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];
        }
    }
}

Check if your query returning any data and then proceed for next operations — Manoj Naik
– Manoj Naik, Commented Oct 10, 2016 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
– Steve, Commented Oct 10, 2016 at 10:15

Mustafa_K · Accepted Answer · 2016-10-10 10:56:25Z

1

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

answered Oct 10, 2016 at 10:56

Mustafa_K

951 silver badge11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 11:45:49Z

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

Collectives™ on Stack Overflow

Import data from Excel to database in C#

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related