Hi,
I'm using this code to read excel, the only difference I see between the
connection strnig is that I especify version 8.0 and you 9.0, try my code
and see what happens , you could use the method that return an array with
all the sheets names.
cheers,
--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation
string srcConnString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+ sourceFile + @";Extended Properties=""Excel 8.0;HDR=YES;""";
string srcQuery = "Select * from [" + GetExcelSheetNames(
sourceFile)[0] + "]";
OleDbConnection srcConn = new OleDbConnection( srcConnString);
srcConn.Open();
OleDbCommand objCmdSelect =new OleDbCommand( srcQuery, srcConn);
OleDbDataReader readerExcel = objCmdSelect.ExecuteReader(
CommandBehavior.CloseConnection);
static String[] GetExcelSheetNames(string excelFile)
{
OleDbConnection objConn = null;
System.Data.DataTable dt = null;
try
{
// Connection String. Change the excel file to the file you
// will search.
String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + excelFile + ";Extended Properties=Excel 8.0;";
// Create connection object by using the preceding connection string.
objConn = new OleDbConnection(connString);
// Open connection with the database.
objConn.Open();
// Get the data table containg the schema guid.
dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables , null);
if(dt == null)
{
return null;
}
String[] excelSheets = new String[dt.Rows.Count];
int i = 0;
// Add the sheet name to the string array.
foreach(DataRow row in dt.Rows)
{
excelSheets[i] = row["TABLE_NAME"].ToString();
i++;
}
return excelSheets;
}
catch(Exception ex)
{
return null;
}
finally
{
// Clean up.
if(objConn != null)
{
objConn.Close();
objConn.Dispose();
}
if(dt != null)
{
dt.Dispose();
}
}
}
"Mike P" <mi*******@gmail.com> wrote in message
news:uP**************@TK2MSFTNGP12.phx.gbl...
I'm trying to take some data from an Excel spreadsheet, put it into a
dataset, and then update a SQL table with the data. I'm not 100% sure
I'm doing this right, can somebody tell me what I'm doing wrong? The
error I'm getting is 'uninstallable ISAM', so I think the error is to do
with the connection string.
OleDbConnection connEx = new
OleDbConnection(ConfigurationSettings.AppSettings["connectExcel"]);
SqlConnection connSql = new
SqlConnection(ConfigurationSettings.AppSettings["connectBrdsql"]);
OleDbCommand commEx = new OleDbCommand("Select orderID, orderDate,
sos, refNo from [Dummy 1$], connEx");
OleDbDataAdapter dapEx = new OleDbDataAdapter(commEx);
DataSet dsSql = new DataSet();
connEx.Open();
connSql.Open();
dapEx.Fill(dsSql, "Orders");
SqlDataAdapter dapSql = new SqlDataAdapter("select * from
O2_B2B_Offline_Microsite_Staging", connSql);
dapSql.Update(dsSql);
Connection string :
<appSettings>
<add key="connectExcel" value="Provider=Microsoft.Jet.OLEDB.4.0; data
source=c:\orders.xls;Extended Properties=Excel 9.0"/>
<add key="connectBrdsql"
value="server=brdsql;uid=datawriter;pwd=rover9;dat abase=amcat_relational
"/>
</appSettings>
Any help would be really appreciated.
Cheers,
Mike
*** Sent via Developersdex http://www.developersdex.com ***
