C# how to read two excel sheets using join

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
LVL 1
pclarke7Asked:
Who is Participating?
 
andrewssd3Connect With a Mentor Commented:
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
 
rspahitzCommented:
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
 
pclarke7Author Commented:
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
 
andrewssd3Commented:
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
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.