Working with Excel files in C#

Question

I have Excel files that are in 2000 & 2003 format. I need to import them via C# code into an access DB. I have written a method to read the file into a data table. No matter which connection string i use (I have checked the other posts on this topic) I continue to get "Table is not in the correct format" error. Can someone please explain to me what I am doing wrong.

        public static DataSet ParseExcel(string excelFile)
        {
            string sheetName = Path.GetFileNameWithoutExtension(excelFile);
            string excelQuery = @"SELECT * FROM [" + sheetName + "]";
            string excelConnctionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + "" + excelFile + "" +
                                    @";Extended Properties=" + "" + @"Excel 8.0;HDR=Yes;" + "";
            if(File.Exists(excelFile))
            {
                var myConnection = new OleDbConnection(excelConnctionString);
                myConnection.Open();
                var myCommand = new OleDbDataAdapter(excelQuery, excelConnctionString);
                myCommand.TableMappings.Add("Table", "TestTable");
                var dtSet = new DataSet();
                myCommand.Fill(dtSet);
                myConnection.Close();
                return dtSet;                
            }
            return null;
        }

connectionstrings.com/excel might of some help.

Martin Liversage
– Martin Liversage

2011-05-25 23:34:14 +00:00
Commented May 25, 2011 at 23:34 — Martin Liversage
– Martin Liversage, Commented May 25, 2011 at 23:34

Ebenezar John Paul · Accepted Answer · 2017-07-03 09:35:38Z

Go through this code example carefully and try to understand the work flow. You will get it real easy to write any kind programs for accessing excel data according to your requirements.

1.Here I just have a Upload Field in order to select the Excel File in .aspx file

<asp:FileUpload ID="Upload_File" runat="server" />
<asp:Button ID="Upload_Button" runat="server"  Text="Upload" onclick="btnUpload_Click"/>
<asp:GridView ID="Gridview_Name" runat="server">
</asp:GridView>

Now lets see what happens in code behind file (.aspx.cs file)

protected void Upload_Button_Click(object sender, EventArgs e) { string connectionString = ""; if (Upload_File.HasFile) // checking whether file is selected to be uploaded

{
       //getting name of the file
 string fileName = Path.GetFileName(Upload_File.PostedFile.FileName);  
//getting extension of the file (for checking purpose - which type .xls or .xlsx)
string fileExtension = Path.GetExtension(Upload_File.PostedFile.FileName);  
string fileLocation = Server.MapPath("" + fileName);    //exact location of the excel files
Upload_File.SaveAs(fileLocation);
 //Check whether file extension is xls or xslx

 if (fileExtension == ".xls")
 {
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
     }
 else if (fileExtension == ".xlsx")
    {
   connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
      }   
     //Create OleDB Connection and OleDb Command

                    OleDbConnection con = new OleDbConnection(connectionString);
                    OleDbCommand cmd = new OleDbCommand();
                    //cmd.CommandType = System.Data.CommandType.Text;
                    cmd.Connection = con;
                    OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);
                    DataTable dtExcelRecords = new DataTable();
                    con.Open();
DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
                    cmd.CommandText = "SELECT * FROM [" + getExcelSheetName + "]";
                    dAdapter.SelectCommand = cmd;
                    dAdapter.Fill(dtExcelRecords);
                    con.Close();
                    Gridview_Name.DataSource = dtExcelRecords;
                    GridView_Name.DataBind();
                }
                else
                {
                    Response.Write("Please Select a File to extract data ");
                }
            }

Step by Step Explanation :

We get the file name, extension, location .
And check it is the type (.xls or .xlsx - particularly for excels of 2003 or other formats).
Set connection strings according to the previous step.
Open oledb connection
Create the necessary data adapter and data table
open the connection
store the current table( where the data from excel is stored) in a datatable instance
Store the name of the sheet in astring by getting it from the current table
Fill the data adapter object(dAdapter) with the our data table (dtExcelRecords)
close the connection
Set datasource for the grid as out data table.
Bind it with our grid.

...And We are done!

Hope it helps.

Amit · Accepted Answer · 2011-05-25 23:25:32Z

0

Try this

OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";

answered May 25, 2011 at 23:25

Amit

22.2k28 gold badges76 silver badges94 bronze badges

1 Comment

SSISPissesMeOff Over a year ago

An OLE DB Provider was not specified in the ConnectionString. An example would be, 'Provider=SQLOLEDB;'. The method now looks like this string excelConnctionString ="OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFile + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";";

Collectives™ on Stack Overflow

Working with Excel files in C#

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related