C#: Loading two Excel into one DataTable


I am writing a C# windows program and I need to load an Excel sheet into a DataTable. This is not a problem, and I do it like this:

OleDbDataAdapter MyDA = new OleDbDataAdapter("select * from [" + "ark1" + "$]", @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txtExcelDatabaseFile.Text + ";Extended Properties=\"Excel 8.0;Persist Security Info=False;HDR=NoIMEX=1;\"");
        catch (OleDbException oe)

This works fine. My problem is that the data that I have now is split into two execl sheets.

Is there a way to append data from the second Excel sheet to the dt_Database DataTable after filling it with data from the first Excel sheet??

Thanks in advance
Søren Augustesen
Who is Participating?
Step 1:
You can read the Excel file using the OLEDB adapter to get the Excel sheet names using the code below:

public static string[] getExcelSheets(string mFile)
        string strXlsConnString;
        strXlsConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
                           mFile + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
        OleDbConnection xlsConn = new OleDbConnection(strXlsConnString);
        DataTable xlTable = new DataTable();
        xlTable = xlsConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

        System.String strExcelSheetNames = "";
        string sheetName;
        //Loop through the excel database table names and take only
        //the tables that ends with a $ characters. Other tables are not worksheets...
        for (int lngStart = 0; lngStart < xlTable.Rows.Count; lngStart++)
            //Remove the single-quote surrounding the table name...
            sheetName = xlTable.Rows[lngStart][2].ToString().Replace("'", "");
            if (sheetName.EndsWith("$")) //Yes, this is a worksheet
                //concatenate with a single-quote delimeter... to be returned
                //as a string array later using the split function
                strExcelSheetNames += sheetName.Substring(0, sheetName.Length - 1) + "~";

        if (strExcelSheetNames.EndsWith("~"))
        //the last single quote needs to be removed
        //so that the array index ends with the last sheetname
            strExcelSheetNames = strExcelSheetNames.Substring(0,
                                    strExcelSheetNames.Length - 1);

        char[] chrDelimter = { '~' };
        return strExcelSheetNames.Split(chrDelimter);
    catch (Exception exp)
        throw new Exception("Error while listing the excel" +
                            " sheets from upload file " + exp.Message, exp);

Step 2:
Once sheet names are stored in a string array variable, you can read the contents from the Excel sheet using your code above and merge the content of each data tables.

poultarpAuthor Commented:

I can now see that I didn't explain my problem correctly in the first post - sorry about that.

My data is not in to different Excel sheets, but in two different Excel files, so your very detailed explanation is not what I was looking for.

However the "merge" command was just what I was looking for - and the way to read data from multiple sheets were a nice bonus :o)

Søren Augustesen
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.