Import data from Excel to database in C#

Question

I need to import data from Excel to database with coding instruction.
But it gives me an error. "There is no row at position 0." in line 11.
In the meantime,should I replace my table name instead of "TABLE" in line 8??

Below is my code :

public static DataTable ReadExcelWithoutOffice(string filePath)
{
    var connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;IMEX=1;HDR=YES;TypeGuessRows=0;FirstRowHasNames=true;ImportMixedTypes=Text\""; ;
    using (var conn = new OleDbConnection(connectionString))
    {
        conn.Open();

        var sheets = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
        using (var cmd = conn.CreateCommand())
        {
            cmd.CommandText = "SELECT * FROM [" + sheets.Rows[0]["TABLE_NAME"].ToString() + "] ";

            var adapter = new OleDbDataAdapter(cmd);
            var ds = new DataSet();
            adapter.Fill(ds);
            return ds.Tables[0];
        }
    }
}

http://stackoverflow.com/questions/14796454/import-excel-sheet-using-jet-oledb-4-0-setting-first-row-as-column-name — A3006, Oct 10 '16 at 10:09
Check if your query returning any data and then proceed for next operations — Manoj Naik, Oct 10 '16 at 10:13
How many sheets are present in your Excel file? There is no guarantee that you get these sheets in the order in whìch you see them on the Excel UI — Steve, Oct 10 '16 at 10:15
@Mustafa_K I recommend you consider EPPlus... http://epplus.codeplex.com — Paul Zahra, Oct 10 '16 at 10:23
@Mustafa_K It seems that the exception which you are getting is not from above function. Are you sure about that? What you are doing after returning excel data in data table ? If I am not wrong, you are trying to read or loop through the rows from datatable anywhere else. — Manoj Naik, Oct 10 '16 at 10:38

score 1 · Accepted Answer · answered Oct 10 '16 at 10:56

Thank you all for your answer. I've found some other way to solve my problem Here is my code :

            System.Data.OleDb.OleDbConnection MyConnection;
            System.Data.DataSet DtSet;
            System.Data.OleDb.OleDbDataAdapter MyCommand;
            MyConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='D:\\C# Projects\\ex.xlsx';Extended Properties=Excel 8.0;");
            MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
            MyCommand.TableMappings.Add("tbl_info", "tbl_info");
            DtSet = new System.Data.DataSet();
            MyCommand.Fill(DtSet);
            dgv.DataSource = DtSet.Tables[0];
            MyConnection.Close();

Source : Read and Import Excel File into DataSet

score 0 · Answer 2 · edited May 23 '17 at 11:45

Check out this link.

Reading Excel files from C#

So, it's like this.

Using Microsoft Jet: First create a connection

string Con2 = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + 
filename + @";Extended Properties='Excel 12.0;IMEX=1'";
System.Data.OleDb.OleDbConnection ExcelConnection =
 new System.Data.OleDb.OleDbConnection(Con2);

Then do something like...

DataSet ds = new DataSet();

 // Create OleDbCommand object and select data from worksheet TABNAME
 OleDbCommand cmd_hulpkostenplaatsen = new OleDbCommand("SELECT * FROM [TABNAME$]", ExcelConnection);
        OleDbDataAdapter oleda_hulpkostenplaatsen = new OleDbDataAdapter();
        oleda_hulpkostenplaatsen.SelectCommand = cmd_hulpkostenplaatsen;
        oleda_hulpkostenplaatsen.Fill(ds, "HULPKOSTENPLAATSEN");

        foreach (DataRow row in ds.Tables["HULPKOSTENPLAATSEN"].Rows)
        {
        }

This link is helpful too.

http://www.c-sharpcorner.com/blogs/import-excel-data-to-database-using-c-sharp1

Import data from Excel to database in C#

2 Answers2