ASP.NET - How to get excel fields into sql table.

Asked By The Hunk on 18-Jun-12 01:51 AM
hai friends ,
i have a excel sheet ,it contain a column "MobileNo"
in the column data exists like this
971551231230
971551231231
9715512312ab
971551231232
9715512312[]
971551231233
9715512312mn

now i want to get this column vlaues into sql table .i know that process .

My requirement is insert  all numerics into one table and non-numerics into another table.

How to do this? Please help me
Jitendra Faye replied to The Hunk on 18-Jun-12 01:54 AM
For this first get all the record from excel sheet to any object . after that based on condition you can insert in different table.


To get excel record use this code-

http://www.eggheadcafe.com/community/sql-server/13/10457683/how-to-import-large-data-from-excel-to-sql.aspx

 

Using sqlBulk Class you can export excel file to DataBase.

Use this code-

protected void btnSend_Click(object sender, EventArgs e)
{
String strConnection = "Data Source=MySystem;Initial Catalog=MySamplesDB;Integrated Security=True";

//file upload path
string path = fileuploadExcel.PostedFile.FileName;

//Create connection string to Excel work book
string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=False";

//Create Connection to Excel work book
OleDbConnection excelConnection =new OleDbConnection(excelConnectionString);

//Create OleDbCommand to fetch data from Excel
OleDbCommand cmd = new OleDbCommand("Select [ID],[Name],[Designation] from [Sheet1$]",excelConnection);
excelConnection.Open();
OleDbDataReader dReader;
dReader = cmd.ExecuteReader();
SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
//Give your Destination table name
sqlBulk.DestinationTableName = "Excel_table";
sqlBulk.WriteToServer(dReader);
excelConnection.Close();
}

Try this code and let me know.

 



Chintan Vaghela replied to The Hunk on 18-Jun-12 02:27 AM

Hi Frndz,

 

Functionality: Find numberic and nonnumberic Value

 

Using int.TryParse easily indentify numberic or nonnumberic

 

 

bool isNum = int.TryParse(StrMObileNumber, out MobileNumber);

 

Full Logic     :

 

 

  string StrMObileNumber = "YourValue";

        int MobileNumber;

        bool isNum = int.TryParse(StrMObileNumber, out MobileNumber);

        if (isNum)

        {

          //INT MobileNumber value store in DBtable

          /* Here is Insert only Numberic Value

          971551231230

          971551231231

          971551231232

          971551231233  

         */

 

        }

        else

        {

          //STRING StrMObileNumber value store in DBtable

          /* Here Insert non numberic value

          9715512312ab

            9715512312[]

              9715512312mn

         */

        }

 

Hope this helpful!

Thanks

 

 

 

dipa ahuja replied to The Hunk on 18-Jun-12 02:43 AM
private void button2_Click(object sender, EventArgs e)
{
  string ExcelConstr = @"Provider=Microsoft.ACE.OLEDB.12.0";
  ExcelConstr += "Data Source=d:\book1.xls;Extended Properties=Excel 12.0";
 
  string SqlConstr = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;";
  SqlConstr += "Integrated Security=True;User Instance=True";
 
  using (OleDbConnection con = new OleDbConnection(ExcelConstr))
  {
    con.Open();
    OleDbCommand com = new OleDbCommand("Select * from [Sheet1$]", con);
    OleDbDataReader dr = com.ExecuteReader();
    using (SqlConnection sqlcon = new SqlConnection(SqlConstr))
    {
      sqlcon.Open();
      using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlcon))
      {
        bulkCopy.DestinationTableName = "Table1";
        bulkCopy.ColumnMappings.Add("srno", "srno");
        bulkCopy.ColumnMappings.Add("amount", "amount");
        bulkCopy.WriteToServer(dr);
      }
    }
    dr.Close();
    dr.Dispose();
  }
  MessageBox.Show("successfully imported!");
  //display the imported data in the datagrid
  SqlDataAdapter da = new SqlDataAdapter("select * from Table1", SqlConstr);
  DataTable dt = new DataTable();
 
  da.Fill(dt);
 
  dataGridViewX1.DataSource = dt;
}