C# with Excel - How to join to excel two sheets data into one datatable

Hi,

I', using excel sheet. In that i have more sheets.

i just want combine or join from two data sheets into one single datatable

below screenshot for example and sample reference.

I want output as datatable (after merging two sheet columns
ScreenShot.png
Ganesh STech Lead cum developerAsked:
Who is Participating?
 
Dustin SaundersDirector of OperationsCommented:
I'm not in a good place to test the code so you'll have to test/fix it, but all you need to do is change it to iterate queries rather than connection strings. It would look something like this.

string connectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=C:\\Path\\file.xlsx;";
            
string query = "SELECT * FROM [{0}]";

DataSet results = new DataSet();
string[] sheets = { "Sheet1", "Sheet2" };


foreach (string sheet in sheets)  //Add the sheets to the data set.
{
	using (OdbcConnection c = new OdbcConnection(connectionString))
	{
		OdbcDataAdapter da = new OdbcDataAdapter(string.Format(query, sheet), c);
		c.Open();
		da.Fill(results);
	}
}

for (int i = 1; i < results.Tables.Count - 1; i++)  //Merge all the collected tables
	results.Tables[i].Merge(results.Tables[0]);

return results.Tables[0];

Open in new window


Just a note, you should give the experts appropriate time between asking for an update-- keep in mind its after midnight in most of the US and while we're more than happy to help it might take a couple of hours or the next morning before we get a chance to respond.
0
 
Pawan KumarDatabase ExpertCommented:
Do you have any database?
We can easily join then using SQL queries...

If not then you can read those excels into data tables using c# and the join them using LINQ.

You can get more details from -

http://www.c-sharpcorner.com/UploadFile/0c1bb2/join-two-datatable-using-linq-in-Asp-Net-C-Sharp/
0
 
Dustin SaundersDirector of OperationsCommented:
I agree, this sort of data is going to be better suited for a database-- you can set up a SQLExpress instance for free and put the information there at the level that generates these spreadsheets.

You should be able to pull data from Excel with C# though, the code looks something like this:

string connectionString = "Driver={{Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}};DBQ={0};";
            string sheetName = "Sheet1";
            string query = "SELECT * FROM [" + sheetName + "]";

            DataSet results = new DataSet();
            List<string> files = new List<string>();

            //Add multiple files to our collection.
            files.Add(string.Format(connectionString, "C:\\Temp\\book1.xlsx"));
            files.Add(string.Format(connectionString, "C:\\Temp\\book2.xlsx"));

            foreach (string file in files)  //Add the sheets to the data set.
            {
                using (OdbcConnection c = new OdbcConnection(file))
                {
                    OdbcDataAdapter da = new OdbcDataAdapter(query, c);
                    c.Open();
                    da.Fill(results);
                }
            }

            for (int i = 1; i < results.Tables.Count - 1; i++)  //Merge all the collected tables
                results.Tables[i].Merge(results.Tables[0]);

            return results.Tables[0];

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Ganesh STech Lead cum developerAuthor Commented:
Thanks for your relpy.

I forgot to mention is below.

1. Excel file is only one. In that we will have two sheet.

2. from that excel we have to consider two sheets (sheet1 and sheet2 in single file)..not in two different excel file

In that case how to proceed your code's...?.
0
 
Ganesh STech Lead cum developerAuthor Commented:
any update...
0
 
Ganesh STech Lead cum developerAuthor Commented:
thank.

but, i am getting error:

ERROR [42S02] [Microsoft][ODBC Excel Driver] The Microsoft Access database engine could not find the object 'Output'. Make sure the object exists and that you spell its name and the path name correctly. If 'sheet1' is not a local object, check your network connection or contact the server administrator.
0
 
Ganesh STech Lead cum developerAuthor Commented:
pls. refer and find attached excel sheet for your reference..

Attached Excel contains SectionA(Sheet),SectionB(sheet2) and Output as Datatable(Sheet3)

So, i need output as datatable as mentioned in Output(sheet3)
Employee.xlsx
0
 
Ganesh STech Lead cum developerAuthor Commented:
Any help at your spare time...
0
 
Ganesh STech Lead cum developerAuthor Commented:
Working..Thanks..
0
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.