Solved

C# how to read two excel sheets using join

Posted on 2013-06-21
4
1,155 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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

831 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