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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.