Import Excel Specific Cell contents to SQL Server

Question

User-105759420 posted

Hi Everyone,

I have Build an asp.net with c# backend and SQL Server 2008 R2,

I want to copy the contents of particular cells in each sheet to my SQL database;

for exmaple:  

in my database table i have 6 columns : Title --> Zip  {A4-->A9} ok

how can i insert : content of B4 in First column,B5 in 2nd column (FirstName) .....

 OK, 

Thank you ;)

Answer 1

User61956409 posted

Hi Tiger19,

Thanks for your post.

You could refer to the following code to read data from Excel File then insert the data into the sql database.

String strExcelConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=YourExcel.xlsx;Extended Properties='Excel 8.0;HDR=Yes'";

OleDbConnection connExcel = new OleDbConnection(strExcelConn);

OleDbCommand cmdExcel = new OleDbCommand();

cmdExcel.Connection = connExcel;

connExcel.Open();

DataTable dtExcelSchema;

dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

connExcel.Close();

DataSet ds = new DataSet();

string SheetName = dtExcelSchema.Rows[0][2].ToString();

cmdExcel.CommandText = "SELECT * From [" + SheetName + "B4:B9]";

OleDbDataAdapter da = new OleDbDataAdapter();

da.SelectCommand = cmdExcel;

da.Fill(ds);

string Title = ds.Tables[0].Columns[0].ColumnName.ToString();
string FirstName = ds.Tables[0].Rows[0][0].ToString();
string LastName = ds.Tables[0].Rows[1][0].ToString();
string Street = ds.Tables[0].Rows[2][0].ToString();
string City = ds.Tables[0].Rows[3][0].ToString();
string Zip = ds.Tables[0].Rows[4][0].ToString();

//insert this data into sql database 

For information about how to read Excel File, please refer to this article.

• http://www.aspsnippets.com/Articles/Read-Excel-file-using-OLEDB-Data-Provider-in-C-Net.aspx

Best Regards,

Fei Han

Answer 2

User-105759420 posted

Hi Fei Han,

thank you for your replay, i will try it ;)

thank you for the help.

Best Regards ;