Select Columns with Multiple Ranges from Excel using OleDb

Question

enter image description here My current requirement is I need to select multiple ranges from an excel sheet(DUMMY.xls) in a single query and put it into a DataTable(fooData1) : As you can see from the image above I have columns from A to F. Therefore I am filtering out the columns from A to B and from D to F into a single dataTable(fooData1). The content of the resulting datatable should be like:

NAME    ID     Date     Hobby     COLOR
DEEPAK  1               Coding    Black
ASTHA   2               Singing   Red
GAURAV  3               Dancing   Blue
BHAVESH 4     6/29/2007 Painting  Green

My Code goes like this:

and while executing the Query the following error fires up

"Characters found after end of SQL statement."

     DataTable fooData1 = new DataTable();
                       System.Data.OleDb.OleDbConnection dbConnection =
              new System.Data.OleDb.OleDbConnection
                (@"Provider=Microsoft.Jet.OLEDB.4.0;"
                 + @"Data Source=C:\Dummy.xls;"
                 + @"Extended Properties=""Excel 8.0;HDR=Yes;""");
            dbConnection.Open();
            try
            {
                string strSql = "SELECT * FROM [Sheet1$A:B] ; select * from[Sheet1$D:F]";
                OleDbCommand cmd = new OleDbCommand(strSql);
                cmd.Connection = dbConnection;
                System.Data.OleDb.OleDbDataAdapter dbAdapter1 = new System.Data.OleDb.OleDbDataAdapter(cmd);                       
                dbAdapter1.Fill(fooData1);
                          }
            finally
            {
                dbConnection.Close();
            }

@evanc3: while executing the Query the following error fires up "Characters found after end of SQL statement." — Deepak Sahu
– Deepak Sahu, Commented Aug 28, 2012 at 9:05
Have you try it with one query? Just SELECT * FROM [Sheet1$A:B], because it looks like it won't accept querys which after semicolon. — fankt
– fankt, Commented Aug 28, 2012 at 9:40
@evanc3: As i have said I need to find data from multiple ranges.. and yes the query SELECT * FROM [Sheet1$A:B] would work just fine !! — Deepak Sahu
– Deepak Sahu, Commented Aug 28, 2012 at 10:01
@evanc3: I have already tried that.. but it takes duplicates i.e. for every [Sheet1$A:B] it joins with every rows of [Sheet1$E:F] — Deepak Sahu
– Deepak Sahu, Commented Aug 28, 2012 at 10:17

fankt · Accepted Answer · 2012-08-29 03:04:49Z

3

It can be solve by SQL, use UNION ALL.

SELECT * FROM [Sheet1$A:B] UNION ALL select * from[Sheet1$E:F]

I made test data like:

1   2   3   4   5   6
1   2   3   4   5   6
1   2   3   4   5   6

and the DataTable got:

(Since the option specific with HDR=Yes, the first row is header)

Hope that can solve your problem. :)

answered Aug 29, 2012 at 3:04

fankt

1,0471 gold badge7 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Deepak Sahu Over a year ago

I have updated my question and i have made clear what i need now.

fankt Over a year ago

An alternative solution is process it in C#, like select all but do fooData1.Columns.Remove("SALARY").

pranjal khanduri Over a year ago

IS THE PROBLEM RESOLVED?

Tony Batista · Accepted Answer · 2017-06-07 13:22:04Z

0

Hey as for your question, you select individual columns by using the F and the column number. For example, "SELECT * FROM [Sheet1$A:B] ; select * from[Sheet1$D:F]"; could be re-write as:

"SELECT F1,F2,F4,F5 FROM [Sheet1$];"

answered Jun 7, 2017 at 13:22

Tony Batista

1772 silver badges13 bronze badges

Collectives™ on Stack Overflow

Select Columns with Multiple Ranges from Excel using OleDb

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related