?
Solved

C# how to read two excel sheets using join

Posted on 2013-06-21
4
Medium Priority
?
1,316 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 1500 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

Command Line Tips and Tricks

The command line is a powerful tool at the disposal of every Linux user. Although Linux distros come with beautiful user interfaces, it's worthwhile to learn the command line because it allows you to do a number of things that you otherwise cannot do from the GUI.  

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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.

765 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