none
Union two Excel files RRS feed

  • Question

  • When I have one Excel file with two worksheets, each with identical structure, I can use OleDb and treat the worksheets as separate tables, then use UNION to combine them into one table. What if I have two different Excel files? Can I still do a union?

    Can I do something like this:

    OleDbConnection conn1 = new OleDbConnection("...file1.xls...");
    OleDbConnection conn2 = new OleDbConnection("...file2.xls...");
    
    conn1.Open();
    conn2.Open();
    
    OleDbAdapter da1 = new OleDbAdapter("SELECT * FROM [Sheet1$]", conn1);
    OleDbAdapter da2 = new OleDbAdapter("SELECT * FROM [Sheet1$]", conn2);
    
    DataSet ds = new DataSet();
    
    da1.Fill(ds, "MyTable");
    da2.Fill(ds, "MyTable");

    With ds.Tables["MyTable"] have the data from both Excel files? Or will the second fill command overwrite the data from the first fill command?

    • Moved by CoolDadTx Thursday, December 8, 2016 4:39 PM ADO.NET related
    Thursday, December 8, 2016 3:47 PM

Answers

  • My tests were failing, which is why I posted the question. Turns out, yes, that is in fact what happens. The reason my tests were failing was because the second Excel file had data that was being interpreted in the wrong data format. I just added a WHERE clause to make sure that the data is not null or the empty string and that solved the issue.
    Thursday, December 8, 2016 4:43 PM

All replies

  • Since you're not actually using Excel to do the union you could. Of course whether it fills them correctly or not is completely dependent upon the data. Fill will use the existing tables if they already exist. But if the schemas don't match you may get an error or bad results. Assuming they are the same table structures then all the rows from both sources would be combined together.  But you'll want to run a test to confirm that this is the behavior you're seeing.

    Michael Taylor
    http://www.michaeltaylorp3.net

    Thursday, December 8, 2016 4:39 PM
  • My tests were failing, which is why I posted the question. Turns out, yes, that is in fact what happens. The reason my tests were failing was because the second Excel file had data that was being interpreted in the wrong data format. I just added a WHERE clause to make sure that the data is not null or the empty string and that solved the issue.
    Thursday, December 8, 2016 4:43 PM