By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,705 Members | 2,017 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,705 IT Pros & Developers. It's quick & easy.

writing Excel data to SQL table

P: n/a
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 ***
Nov 17 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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 ***

Nov 17 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.