C#: Import Excel and CSV file into DataTable using C#

It's one of the main hurdles any developer will face during development life cycle. We have Excel sheet or CSV sheet and client want to save data of Excel/CSV file to be saved in database with the help of C# coding.

Here I'll explain how we can import Excel and CSV file and then create data-table of file records. We can then easily use this data-table to insert record in DB (here I'm not explaining how to insert record to DB).

Here is function to import excel file and return DataTable:

Namespace required are: System.Data and System.Data.OleDb;

private DataTable GetExcelSheetdata() {     DataTable dt = new DataTable();     try     {         // Url of file to be imported.         var url = @"c:\test.xls";         // Create OleDB connection string.         string connectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0;""", url);         var query = String.Format("Select * from [{0}$]", "Sheet1");         //Creates and fill Oledb data adapter.         OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, connectionString);         //create and fill dataset from adapter               DataSet dataSet = new DataSet();         dataAdapter.Fill(dataSet);         dt = dataSet.Tables[0];     }     catch     {     }     return dt; }

And if we want to import .csv file, then here is the code to import file and return DataTable:

Namespace required are: System.Data and System.Data.Odbc;

private DataTable GetCsvSpreadSheetdata() {     DataTable dt = new DataTable();     try     {         // Directory and file to be imported.         var serverDirPath = @"c:\";         var fileName = "test.csv";         var filePath = string.Concat(serverDirPath, "\\", fileName);         var strConnString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq="                 + serverDirPath + ";Extensions=asc,csv,tab,txt;Persist Security Info=False";         // Creates and opens an ODBC connection         OdbcConnection conn = new OdbcConnection(strConnString.Trim());         conn.Open();         var query = string.Format("Select * from [{0}]", fileName);         //Creates the data adapter         OdbcDataAdapter obj_da = new OdbcDataAdapter(query, conn);         DataSet ds = new DataSet();         //Fill dataset with the records from CSV file         obj_da.Fill(ds, "members");         //closes the connection         conn.Close();         dt = ds.Tables[0];     }     catch     {     }     return dt; }

User can easily iterate over returned DataTable to save the record in DB.

Happy Coding!!!