writing Excel data to SQL table

[Mike P]

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;database=amcat_relational
"/>
</appSettings>


Any help would be really appreciated.


Cheers,

Mike

 

[Ignacio Machin \( .NET/ C# MVP \)]

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 = 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();
}
}
}

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;database=amcat_relational
"/>
</appSettings>


Any help would be really appreciated.


Cheers,

Mike