Hello - I have an application that was working for awhile and then quit. The problem wasn't very obvious until I viewed the contents of a datatable that was loaded from an excel spreadsheet. The spreadsheet contains multiple records as 1 record per column
and the fields are rows. The datatable has the first field as a header. I need to specify no header. I tried to specify in my connection string but get the error "Could not find installable ISAM" Here is my connection string: Thanks,
if (filepath.EndsWith(".xlsx"))
{
connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + "; Extended Properties=Excel 12.0;HDR=N0;IMEX=1;";
}
else if (filepath.EndsWith(".xls"))
{
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;HDR=N0;IMEX=1;";
}
else
{
return;
}
try
{
string strSql = "Select * from [Sheet1$]";
OleDbConnection excelConnection = new OleDbConnection(connectionString);
excelConnection.Open();
var _PTA = new Dictionary<int, string>();
OleDbCommand dbCommand = new OleDbCommand(strSql, excelConnection);
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(dbCommand);
DataTable dt = new DataTable();
dataAdapter.Fill(dt);
excelConnection.Close();
PTAEntities context = new PTAEntities();
RDowdall
Member
361 Points
216 Posts
Loading Excel into Datatable without header
Feb 24, 2012 04:57 PM|LINK
Hello - I have an application that was working for awhile and then quit. The problem wasn't very obvious until I viewed the contents of a datatable that was loaded from an excel spreadsheet. The spreadsheet contains multiple records as 1 record per column and the fields are rows. The datatable has the first field as a header. I need to specify no header. I tried to specify in my connection string but get the error "Could not find installable ISAM" Here is my connection string: Thanks,
if (filepath.EndsWith(".xlsx")) { connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + "; Extended Properties=Excel 12.0;HDR=N0;IMEX=1;"; } else if (filepath.EndsWith(".xls")) { connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;HDR=N0;IMEX=1;"; } else { return; } try { string strSql = "Select * from [Sheet1$]"; OleDbConnection excelConnection = new OleDbConnection(connectionString); excelConnection.Open(); var _PTA = new Dictionary<int, string>(); OleDbCommand dbCommand = new OleDbCommand(strSql, excelConnection); OleDbDataAdapter dataAdapter = new OleDbDataAdapter(dbCommand); DataTable dt = new DataTable(); dataAdapter.Fill(dt); excelConnection.Close(); PTAEntities context = new PTAEntities();smirnov
All-Star
24614 Points
4192 Posts
Re: Loading Excel into Datatable without header
Feb 24, 2012 05:04 PM|LINK
Instead of HDR=N0; (N-zero) use
HDR=NO;
or
HDR=YES;
http://support.microsoft.com/kb/316934
RDowdall
Member
361 Points
216 Posts
Re: Loading Excel into Datatable without header
Feb 24, 2012 05:14 PM|LINK
Good Catch - I didn't notice that,
Thanks
RDowdall
Member
361 Points
216 Posts
Re: Loading Excel into Datatable without header
Feb 24, 2012 05:19 PM|LINK
I still get "Could not find installable ISAM"
smirnov
All-Star
24614 Points
4192 Posts
Re: Loading Excel into Datatable without header
Feb 24, 2012 06:28 PM|LINK
You need to quote the part with Extended Properties, using ' or " like this
...Extended Properties="Excel 12.0;HDR=NO";
It must be done in both strings, for xls and xlsx.
http://connectionstrings.com/excel#p84
RDowdall
Member
361 Points
216 Posts
Re: Loading Excel into Datatable without header
Feb 24, 2012 06:49 PM|LINK
Thanks for your help. That worked.