Importing Excel data to SQL

Terry

Hi,

I am trying to import data from Excel sheet to the database referring to http://www.c-sharpcorner.com/UploadFile/0c1bb2/inserting-excel-file-records-into-sql-server-database-using/ & other sites. This is the Code :

// Connection String to connect to EXCEL

private void ExcelConn(string FilePath)

{

constr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR=YES;'", FilePath);

Econ = new OleDbConnection(constr);

}

// Read & Insert Excel File records to DB

private void InsertExcelRecords(string filePath)

{

// connect to Excel

ExcelConn(filePath);

string query = string.Format("Select * from Sheet1$");

OleDbCommand Ecom = new OleDbCommand(query, Econ);

Econ.Open();

//string sheet1 = Econ.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();

DataTable sheets = Econ.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

DataSet ds = new DataSet();

OleDbDataAdapter oda = new OleDbDataAdapter(query, Econ);

oda.Fill(ds); // ERROR ON THIS LINE

Econ.Close();

DataTable dt = ds.Tables[0];

ImportedGrid.DataSource = dt;

ImportedGrid.DataBind();

oda = null;

ds = null;*/

Ecom = null;

Econ = null;

}

The Excel file has 1 sheet named "Sheet1", but yet I cannot get the sheet here. I get error Syntax error in FROM clause.

With this statement - string query = string.Format("Select * FROM [{0}]", "Sheet1$"); I get he following error :

The Microsoft Office Access database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.

In both the cases the DataTable sheets has 9 cols & 0 Rows.

Can you help me find this error cause ?

Also the article saves the records directly to DB. I wish to update the records to my existing DbSet & then save changes to the DB. How can that be achieved ? Which method is preferred ?

Kindly help me solve the problem.

Thanks

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Terry11y ago
Manoj,
Can you please have a look at this post http://www.c-sharpcorner.com/Forums/Thread/296713/suggestion-for-such-a-view.aspx

and give me your suggestions & guidance.

Thanks
0
Terry11y ago
Thanks Manoj.
Yes "Sheet1" does exists in the file and it's the only sheet in the workbook. With the above code, the file was being saved temporarily to \\IISExpress folder. I saved the uploaded file to a Content folder of the application and it could find the Sheet1 and also the name of the Sheet.

I had a look at both the articles. Actually, I have several fields, few mandatory, few of type Enum & DateTime. With the mentioned approaches, fields were parsed as it should be and wasn't working as expected.

Hence I am reading each row and saving values in Model, validating it, looking for any errors and finally adding the Model to the DBContext DbSet and saving to DB. In Excel, many times the fields may not be formatted with data type, in such case while importing that value will be taken as string & that cannot be type casted automatically to int or enum or date types.

Is their any easy or better way to import taking care of the above that I have implemented right now !! As that will only make sure that the imported record is fully valid.

Thanks
0
Manoj Bhoir11y ago
Hi,

With string query = string.Format("Select * FROM [{0}]", "Sheet1$");

Check that Sheet1 exist in your Excel File.

Fore more information See : http://www.codeproject.com/Articles/32581/Import-Data-from-Excel-to-SQL-Server

You can also use SQLBulkCopy.

See this Article : http://www.codeproject.com/Tips/636719/Import-MS-Excel-data-to-SQL-Server-table-using-Csh
+1