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." — LetsKickSomeAss in .net, Aug 28 '12 at 09: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, Aug 28 '12 at 09: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 !! — LetsKickSomeAss in .net, Aug 28 '12 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] — LetsKickSomeAss in .net, Aug 28 '12 at 10:17
How about `SELECT * FROM [Sheet1$A:B,Sheet1$E:F]`? I tried in my Excel, it appears use `,` to separate selection. — fankt, Aug 28 '12 at 10:48

score 3 · Answer 1 · answered Aug 29 '12 at 03:04

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 '12 at 03:04

fankt

1,007
1
7
16

I have updated my question and i have made clear what i need now. – LetsKickSomeAss in .net Aug 29 '12 at 08:00
An alternative solution is process it in C#, like select all but do `fooData1.Columns.Remove("SALARY")`. – fankt Aug 29 '12 at 09:13
IS THE PROBLEM RESOLVED? – pranjal khanduri May 22 '17 at 10:00

score 0 · Answer 2 · answered Jun 07 '17 at 13:22

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 07 '17 at 13:22

Tony Batista

177
2
13

Select Columns with Multiple Ranges from Excel using OleDb

2 Answers2