Click here to Skip to main content
14,639,661 members

Read all excel in a folder using oledbconnction C#

arunavachowdhury asked:

Open original thread
1-I am having a strange problem.

Requirement: read a list of excel files on a folder and store the content on a dataset.

What I have written:
**Button click:**

protected void btnUploadExcelFiles_Click(object sender, EventArgs e)
{
            string[] strFiles = Directory.GetFiles(strPath);
            foreach (string strFile in strFiles)
            {
                ClsExcelUpload objExcelUpload = new ClsExcelUpload();
                string szConnectionString1 = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + strFile + "';Extended Properties=\"Excel 12.0;HDR=YES;\"";
                objExcelUpload.ReadExcel(szConnectionString1, strFile);

            }
}



**function ReadExcel:**

public string ReadExcel(string szConnectionString, string strFile)
        {
            OleDbConnection objConn = new OleDbConnection();
            objConn.ConnectionString = szConnectionString;
            objConn.Open();

            OleDbDataAdapter DAobjBasicInfo = new OleDbDataAdapter();
            DAobjBasicInfo = new OleDbDataAdapter("select * from [Sheet1$]", objConn);
            DataTable dt1 = new DataTable();
            DAobjBasicInfo.Fill(dt1);
            objConn.Close();
            objConn.Dispose();
            return "";
        }

**Problem:**
The szConnectionString gets updated with the new filename set through the loop but when the OleDbDataAdapter opens the objConn, it looks for the Excel file which is already deleted. and the datatable everytime gets the first file data everytime.

This is the strangest thing I have ever seen as I am not able to identify the area of problem why the oledbconnection is not getting updated.

What I have tried:

The entire functions are written above.
Tags: C#, OleDb, Excel

Preview



When answering a question please:
  1. Read the question carefully.
  2. Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
  3. If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
  4. Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.
Let's work to help developers, not make them feel stupid.
Please note that all posts will be submitted under the The Code Project Open License (CPOL).




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100