How to select Columns from excel sheet

b b

15y17.6k

Hi All,

I want to select few columns from Excel Sheet to datatable(not whole Excel sheet) using C# .net. Can anyone help me.

OleDbConnection connection = null;
String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + @"F:\Book.xls" + ";" + "Extended Properties=Excel 8.0;";
DataTable dtCH = new DataTable();
connection = new OleDbConnection(sConnectionString);
OleDbCommand command = new OleDbCommand("Select * FROM [Sheet1$]", connection);
connection.Open();
dtCH = new DataTable("Table");
OleDbDataReader db_reader = command.ExecuteReader();

dtCH.Load(db_reader);
DataTable table = dtCH;

connection.Close();

I am using the above codes and its shoeing all values from Excel file to Datatable. But i want to show only few columns from Excel to Datatable.

Regards,
B

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Karthikeyan Anbarasan15y ago
Select the column names in the query itself...
0
vijay15y ago
hi..
try this link to get a excel file into datatable...
http://vbdotnetaddict.blogspot.com/2011/01/import-excel-table-into-sql-table.html

After getting the excel file into datatable and you can select a particular column from the data table..Its very easy steps to select a any column from datatable...
0
Krishna Garad15y ago
Try this
string sql="Select Col1,Col2 From[Sheet1$]";

For Integrated query use this

strSQL = "SELECT S.StudentId, S.StudentName, M.Marks, G.Marks, (M.Marks+G.Marks) AS Total " & _
   "FROM [Students$] S, [Mathematics$] M, [Geography$] G " & _
   "WHERE(S.StudentId = M.StudentId And S.StudentId = G.StudentId) " & _
   "ORDER BY (M.Marks+G.Marks) DESC"
0