Solved

C# how to read two excel sheets using join

Posted on 2013-06-21
4
1,178 Views
Last Modified: 2013-08-25
Hello,
I have figured out how to read in a spreadsheet in c# using odbcDataReader. See below:

                con1.Open();
                string SQL = "Select * from [TransactionHeader$]";
                "select * from [TransactionMaster$]";
                using (OdbcCommand com = new OdbcCommand(SQL, con1, null))
                {
                    using (OdbcDataReader reader = com.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            int Fields = reader.FieldCount;
                            string value = reader[0] + " " + reader[1] + " ";

                            MessageBox.Show(value.ToString() + Fields);
                        }
                    }
                }

Now I would like to be able to do a similiar type of read, but joining two Sheets (TransascionMaster & TransactionHeader). The SQL statement would be something like this:

select * from [TransactionMaster$], [TransactionHeader$] where TransactionMaster.CELL1 = TransactionHeader.CELL5"

Can I do this and if so what is the format of the SQL statement?

regards
Pat
0
Comment
Question by:pclarke7
  • 2
4 Comments
 
LVL 22

Expert Comment

by:rspahitz
ID: 39267131
I don't think this is possible without spending more time rebuilding all the parts.  I've never tried this with an ODBC connection, but since each sheet is a separate object, the only way I can see to do this is to either pull each object separately then merge them in C# or to get Excel to merge the parts (which would be rather slow) then use C# to to what you wanted.

FYI
Since they are two separate objects, imagine that you are working with two separate forms and you want to merge the contents.  Ultimately you need to either go to each form to get the contents or you need to get the forms to merge themselves into a new object then get the contents.
0
 
LVL 17

Accepted Solution

by:
andrewssd3 earned 500 total points
ID: 39267133
You should be able to do this using a sql statement like
select * from 
	[TransactionMaster$] AS m
	INNER JOIN [TransactionHeader$] AS h ON m.CELL1 = h.CELL5

Open in new window

Assuming that CELL1 and CELL5 are column headers on the 2 sheets
0
 

Author Comment

by:pclarke7
ID: 39267857
Thanks for both comments.
Andrew I went with your suggestion and below is the SQL statement:

string SQL = "Select * from [TransactionMaster$] AS m LEFT JOIN [TransactionHeader$] AS h ON m.T2_TransactionID=h.T1_TransID and m.T2_CompanyID= " + "'" + CompanyAlpha + "'" + " and m.T2_CompanyID=" + "'" + globals.MyTrans.Transaction + "'"  + " Order by m.T2_SeqNo";

However I get the following error

ERROR [HY000¿] [Microsoft][ODBC Excel Driver] JOIN expression not supported.

any ideas ?

regards
Pat
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 39267977
I'm pretty sure I have used joins across sheets with the Excel ODBC driver in the past from VBA, but it's obviously not implemented in yours. I'm not very experienced with .NET data objects, but isn't it possible to created Datasets separately and join them with a DataRelation? This is an example of doing this, but you'd need to ask more of a .Net expert whether this is feasible and/or sensible in your situation.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question