"HDR=Yes;" indicates that the first row contains columnnames, not data.
"IMEX=1;" tells the driver to always read "intermixed" data columns as text
Hi Vishal,
In my excel file the actual data starts from row number 11. Row numbers 1-9 do not contain data which i need. Row number 10 has column names and Row number 11 and above contains the data.
Is there any way through which i can specify from which row number the code should start reading the excel file.
You could create a named region, by going into menu Insert--> Name -->Defiine, and create a named region with required range. If example range name is myDataRange, then you could say
SpreadSheetName = "[sheet1$]"; //normal case
OR
SpreadSheetName = "[myDataRange]"; //special case
ExcelCommand.CommandText = @"SELECT * FROM " + SpreadSheetName;
Hope it helps. Please mark as answer if this helps you.
Glad it helped. On your right hand side, you would find buttons like "reply" etc, you should find a button similar to "mark as answer". Just click on that and make sure you do that for my post/reply where I mentioned about using Range.
JKC
Member
238 Points
266 Posts
Read data from Excel to a datatable C#
Apr 30, 2008 09:30 PM|LINK
This code is incorrect, there seems to be an attribute missing, any ideas?
public static DataSet exceldata(string filelocation)
{
DataSet ds = new DataSet();
OleDbCommand excelCommand = new OleDbCommand();OleDbDataAdapter excelDataAdapter = new OleDbDataAdapter();
string excelConnStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + filelocation + "; Extended Properties =Excel 8.0;";
OleDbConnection excelConn = new OleDbConnection(excelConnStr);
excelConn.Open();
DataTable dtPatterns = new DataTable();excelCommand = new OleDbCommand("SELECT `PATTERN` as PATTERN, `PLAN` as PLAN FROM [PATTERNS$]", excelConn);
excelDataAdapter.SelectCommand = excelCommand;
excelDataAdapter.Fill(dtPatterns);
"dtPatterns.TableName = Patterns";
ds.Tables.Add(dtPatterns);
return ds;
}
vishalbade
Participant
1126 Points
187 Posts
Re: Read data from Excel to a datatable C#
Apr 30, 2008 10:52 PM|LINK
Hi You need to use IMEX attribute, If your excel is 2003, then excel library version is 11.0, but 8.0 should also be fine. Try the below things
Extended Properties='Excel 8.0;IMEX=1'; The single quotes should start after = and end after 1. If this does not work try the below
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
"HDR=Yes;" indicates that the first row contains columnnames, not data.
"IMEX=1;" tells the driver to always read "intermixed" data columns as text
JKC
Member
238 Points
266 Posts
Re: Read data from Excel to a datatable C#
May 01, 2008 01:03 AM|LINK
Thanks!
vishalbade
Participant
1126 Points
187 Posts
Re: Read data from Excel to a datatable C#
May 01, 2008 09:53 AM|LINK
Please mark my post as answer if it helped you.
Vince Xu - M...
All-Star
80367 Points
6801 Posts
Re: Read data from Excel to a datatable C#
May 02, 2008 03:38 AM|LINK
Hi,
There are two approach for retrieving data from Excel.
One is using OLEDB which you used.
string connstr ="Provider=Microsoft.Jet.Oledb.4.0;Data Source=C:\aaa.xls;Extended Properties=Excel 8.0"/> OleDbConnection conn = new OleDbConnection(connstr); string strSQL = "SELECT * FROM [Sheet$]"; OleDbCommand cmd = new OleDbCommand(strSQL, conn); DataSet ds = new DataSet(); OleDbDataAdapter da = new OleDbDataAdapter(cmd); da.Fill(ds); GridView1.DataSource = ds; GridView1.DataBind();The other is using Excel Object(Microsoft.Office.Interop.Excel) to retrieve it into DataSet.
The following post is retrieving excel file and import into GridView by using Microsoft.Office.Interop.Excel: http://forums.asp.net/p/1192930/2057005.aspx#2057005
Hope it helps.
sarangpitale
Member
6 Points
4 Posts
Re: Read data from Excel to a datatable C#
May 26, 2008 12:27 PM|LINK
Hi Vishal,
In my excel file the actual data starts from row number 11. Row numbers 1-9 do not contain data which i need. Row number 10 has column names and Row number 11 and above contains the data.
Is there any way through which i can specify from which row number the code should start reading the excel file.
vishalbade
Participant
1126 Points
187 Posts
Re: Read data from Excel to a datatable C#
May 26, 2008 01:09 PM|LINK
You could create a named region, by going into menu Insert--> Name -->Defiine, and create a named region with required range. If example range name is myDataRange, then you could say
SpreadSheetName = "[sheet1$]"; //normal case
OR
SpreadSheetName = "[myDataRange]"; //special case
ExcelCommand.CommandText = @"SELECT * FROM " + SpreadSheetName;
Hope it helps. Please mark as answer if this helps you.
sarangpitale
Member
6 Points
4 Posts
Re: Read data from Excel to a datatable C#
May 28, 2008 06:26 AM|LINK
Ya it helped Vishal [:D]
Thank you for the immediate reply.
BTW how do i mark your reply as answer?
sorry i am new to this forum.
vishalbade
Participant
1126 Points
187 Posts
Re: Read data from Excel to a datatable C#
May 28, 2008 01:17 PM|LINK
Glad it helped. On your right hand side, you would find buttons like "reply" etc, you should find a button similar to "mark as answer". Just click on that and make sure you do that for my post/reply where I mentioned about using Range.
sarangpitale
Member
6 Points
4 Posts
Re: Read data from Excel to a datatable C#
May 30, 2008 01:56 PM|LINK
Cannot find answer button/link anywhere.
All i can find is Reply Favorites Contact buttons.
Please note that i am logged in.