Solved

C# how to read two excel sheets using join

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Create email hyperlinks from regular text 3 28
Excel to show a dynamic Picklist at level2 2 23
msiexec won't run 4 25
need count all combination 31 29
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

734 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