Reading Excel sheet into DB Table

[dba123]

I need some help and direction on what classes and an example or two
(article) on how to read an Excel Worksheet and insert one column into a
database table column. I am using .NET 2.0 only. What namespaces and
classes should I use and how?
--
dba123

[simida]

Maybe System.Data.Ole Db or ODBC will help you. I used OleDb to connect
EXCEL Worksheet.

connStr = "Provider=Micro soft.Jet.OLEDB. 4.0;Extended Properties=\"Ex cel
5.0;HDR=yes\";D ata Source=test.xls ;";

You can also use MSSQL Tools about Import/Export data Wizard to import
your worksheet data into DB.

Sincerely,
simida

dba123 写�：

I need some help and direction on what classes and an example or two
(article) on how to read an Excel Worksheet and insert one column into a
database table column. I am using .NET 2.0 only. What namespaces and
classes should I use and how?
--
dba123

[dba123]

Thanks. What about reading the worksheet, how to pick out a certain column,
and pass that as a stream or something to a s tored procedure which inserts
the data from the worksheet?

Here's my code so far:

// Insert Root Categories
string strConn;
strConn = "Provider=Micro soft.Jet.OLEDB. 4.0; Data
Source=C:\\test .xls; Extended Properties=Exce l 8.0;";
//You must use the $ after the object you reference in the
spreadsheet
OleDbDataAdapte r myCommand = new OleDbDataAdapte r("SELECT * FROM
[Root Levels$]", strConn);

DataSet myDataSet = new DataSet();
myCommand.Fill( myDataSet, "ExcelInfo" );

//Now, using stored proc, insert the data (Specific column in the dataset)
into database table.

Also, is there a better way or new way in .NET 2.0 to read an excel worksheet?

OR can I somehow create a stream then stream each row from the excel sheet
directly to my SQL Table?
--
dba123
"simida" wrote:

Maybe System.Data.Ole Db or ODBC will help you. I used OleDb to connect
EXCEL Worksheet.

connStr = "Provider=Micro soft.Jet.OLEDB. 4.0;Extended Properties=\"Ex cel
5.0;HDR=yes\";D ata Source=test.xls ;";

You can also use MSSQL Tools about Import/Export data Wizard to import
your worksheet data into DB.

Sincerely,
simida

dba123 写�：

I need some help and direction on what classes and an example or two
(article) on how to read an Excel Worksheet and insert one column into a
database table column. I am using .NET 2.0 only. What namespaces and
classes should I use and how?
--
dba123

[simida]

Another way, you can use EXCEL2003 open XLS file, and save as .XML
file. You will process XML file more easy in .NET 2.0.

You can also anaylse EXCEL file format to read data firstly. More
information about EXCEL file format, plz refer to this.

http://chicago.sourceforge.net/devel/docs/excel/

Sincerely,
simida

dba123 写�：

Thanks. What about reading the worksheet, how to pick out a certain column,
and pass that as a stream or something to a s tored procedure which inserts
the data from the worksheet?

Here's my code so far:

// Insert Root Categories
string strConn;
strConn = "Provider=Micro soft.Jet.OLEDB. 4.0; Data
Source=C:\\test .xls; Extended Properties=Exce l 8.0;";
//You must use the $ after the object you reference in the
spreadsheet
OleDbDataAdapte r myCommand = new OleDbDataAdapte r("SELECT *FROM
[Root Levels$]", strConn);

DataSet myDataSet = new DataSet();
myCommand.Fill( myDataSet, "ExcelInfo" );

//Now, using stored proc, insert the data (Specific column in the dataset)
into database table.

Also, is there a better way or new way in .NET 2.0 to read an excel worksheet?

OR can I somehow create a stream then stream each row from the excel sheet
directly to my SQL Table?
--
dba123
"simida" wrote:

Maybe System.Data.Ole Db or ODBC will help you. I used OleDb to connect
EXCEL Worksheet.

connStr = "Provider=Micro soft.Jet.OLEDB. 4.0;Extended Properties=\"Ex cel
5.0;HDR=yes\";D ata Source=test.xls ;";

You can also use MSSQL Tools about Import/Export data Wizard to import
your worksheet data into DB.

Sincerely,
simida

dba123 写�：

I need some help and direction on what classes and an example or two
(article) on how to read an Excel Worksheet and insert one column into a
database table column. I am using .NET 2.0 only. What namespaces and
classes should I use and how?


--
dba123

[dba123]

I looked at the link, seems more complex than needed to simply read an excel
file and figure out which sheets and columns to work with. what is BIFF?
There has to be an easy way to pinpoint specific worksheets in a workbook
and certain columns through .NET 2.0 in conjunction with reading and writing
to a DB table.

Yea, I could use XML and the .NET XML reader class but right now, time is
of the essence.

--
dba123
"simida" wrote:

Another way, you can use EXCEL2003 open XLS file, and save as .XML
file. You will process XML file more easy in .NET 2.0.

You can also anaylse EXCEL file format to read data firstly. More
information about EXCEL file format, plz refer to this.

http://chicago.sourceforge.net/devel/docs/excel/

Sincerely,
simida

dba123 写�：

Thanks. What about reading the worksheet, how to pick out a certain column,
and pass that as a stream or something to a s tored procedure which inserts
the data from the worksheet?

Here's my code so far:

// Insert Root Categories
string strConn;
strConn = "Provider=Micro soft.Jet.OLEDB. 4.0; Data
Source=C:\\test .xls; Extended Properties=Exce l 8.0;";
//You must use the $ after the object you reference in the
spreadsheet
OleDbDataAdapte r myCommand = new OleDbDataAdapte r("SELECT * FROM
[Root Levels$]", strConn);

DataSet myDataSet = new DataSet();
myCommand.Fill( myDataSet, "ExcelInfo" );

//Now, using stored proc, insert the data (Specific column in the dataset)
into database table.

Also, is there a better way or new way in .NET 2.0 to read an excel worksheet?

OR can I somehow create a stream then stream each row from the excel sheet
directly to my SQL Table?
--
dba123
"simida" wrote:

Maybe System.Data.Ole Db or ODBC will help you. I used OleDb to connect
EXCEL Worksheet.
>
connStr = "Provider=Micro soft.Jet.OLEDB. 4.0;Extended Properties=\"Ex cel
5.0;HDR=yes\";D ata Source=test.xls ;";
>
You can also use MSSQL Tools about Import/Export data Wizard to import
your worksheet data into DB.
>
Sincerely,
simida
>
dba123 写�：
>
I need some help and direction on what classes and an example or two
(article) on how to read an Excel Worksheet and insert one column into a
database table column. I am using .NET 2.0 only. What namespaces and
classes should I use and how?


--
dba123
>
>

[simida]

If you want to read certain columns, you can use SELECT statements in
SQL language.
Such as,
"SELECT [Column1], [Column2] FROM [Root Levels$]"

OR, you operate it on the table which is contained in the DataSet
object.
Table.Select() method.

As far as I know, it is a easy way to read EXCEL file. :(

Hope a more elegant way to achieve it.

Sincerely,
simida
dba123 写�：

I looked at the link, seems more complex than needed to simply read an excel
file and figure out which sheets and columns to work with. what is BIFF?
There has to be an easy way to pinpoint specific worksheets in a workbook
and certain columns through .NET 2.0 in conjunction with reading and writing
to a DB table.

Yea, I could use XML and the .NET XML reader class but right now, time is
of the essence.

--
dba123
"simida" wrote:

Another way, you can use EXCEL2003 open XLS file, and save as .XML
file. You will process XML file more easy in .NET 2.0.

You can also anaylse EXCEL file format to read data firstly. More
information about EXCEL file format, plz refer to this.

http://chicago.sourceforge.net/devel/docs/excel/

Sincerely,
simida

dba123 写�：

Thanks. What about reading the worksheet, how to pick out a certain column,
and pass that as a stream or something to a s tored procedure which inserts
the data from the worksheet?
>
Here's my code so far:
>
// Insert Root Categories
string strConn;
strConn = "Provider=Micro soft.Jet.OLEDB. 4.0; Data
Source=C:\\test .xls; Extended Properties=Exce l 8.0;";
//You must use the $ after the object you reference in the
spreadsheet
OleDbDataAdapte r myCommand = new OleDbDataAdapte r("SELECT * FROM
[Root Levels$]", strConn);
>
DataSet myDataSet = new DataSet();
myCommand.Fill( myDataSet, "ExcelInfo" );
>
//Now, using stored proc, insert the data (Specific column in the dataset)
into database table.
>
Also, is there a better way or new way in .NET 2.0 to read an excel worksheet?
>
OR can I somehow create a stream then stream each row from the excelsheet
directly to my SQL Table?
>
>
--
dba123
>
>
"simida" wrote:
>
Maybe System.Data.Ole Db or ODBC will help you. I used OleDb to connect
EXCEL Worksheet.

connStr = "Provider=Micro soft.Jet.OLEDB. 4.0;Extended Properties=\"Ex cel
5.0;HDR=yes\";D ata Source=test.xls ;";

You can also use MSSQL Tools about Import/Export data Wizard to import
your worksheet data into DB.

Sincerely,
simida

dba123 写�：

I need some help and direction on what classes and an example or two
(article) on how to read an Excel Worksheet and insert one columninto a
database table column. I am using .NET 2.0 only. What namespaces and
classes should I use and how?
>

--
dba123

[Code Monkey]

To get excel sheet NAMES, use the following - just pass in the filepath
of the excel spreadsheet:

<code>
public string[] ExcelSheetNames (String excelFile)
{
DataTable dt;
string connString = ExcelExtendedCo nn(excelFile);
using (OleDbConnectio n objConn = new
OleDbConnection (connString))
{
objConn.Open();
dt =
objConn.GetOleD bSchemaTable(Ol eDbSchemaGuid.T ables, null);
if(dt == null)
{
return null;
}
string[] res = new string[dt.Rows.Count];
for (int i = 0; i < res.Length; i++)
{
string name = dt.Rows[i]["TABLE_NAME "].ToString();
if(name[0] == '\'')
{
//numeric sheetnames get single quotes around
them in the schema.
//remove them here
if(Regex.IsMatc h(name, @"^'\d\w+\$'$") )
{
name = name.Substring( 1, name.Length - 2);
}
}
res[i] = name;
}
return res;
}
}
</code>

To get the column names of the worksheet in the excel spreadsheet:

<code>
public DataTable columnNames(Str ing excelFile, String sheetName)
{
string connString = ExcelExtendedCo nn(excelFile);
using (OleDbConnectio n conn = new
OleDbConnection (connString))
{
OleDbCommand odc = new
OleDbCommand(st ring.Format("Se lect * FROM [{0}$]", sheetName), conn);
conn.Open();
OleDbDataReader reader = odc.ExecuteRead er();
DataTable sheetSchema = reader.GetSchem aTable();
return sheetSchema;
}
}
</code>

Using the methods above, you can then pull the data of the excel
spreadsheet:

<code>
public DataTable originalData(St ring _filename,Strin g _sheet)
{
DataTable dt = new DataTable();
OdbcConnection conn = new OdbcConnection( );
OdbcCommand cmd = new OdbcCommand();
conn.Connection String = string.Format(@ "Driver={{Micro soft
Excel Driver (*.xls)}};DBQ={ 0};ReadOnly=0;" , _filename);
cmd.Connection = conn;
cmd.CommandType = CommandType.Tex t;
cmd.CommandText = "SELECT * FROM [" + _sheet + "$]";
OdbcDataAdapter oda = new OdbcDataAdapter ();
oda.SelectComma nd = cmd;
oda.Fill(dt);
return dt;
}

</code>

I know this works because it's pulled directly from an application that
I've just written.


simida wrote:

If you want to read certain columns, you can use SELECT statements in
SQL language.
Such as,
"SELECT [Column1], [Column2] FROM [Root Levels$]"

OR, you operate it on the table which is contained in the DataSet
object.
Table.Select() method.

As far as I know, it is a easy way to read EXCEL file. :(

Hope a more elegant way to achieve it.

Sincerely,
simida
dba123 写�：

I looked at the link, seems more complex than needed to simply read an excel
file and figure out which sheets and columns to work with. what is BIFF?
There has to be an easy way to pinpoint specific worksheets in a workbook
and certain columns through .NET 2.0 in conjunction with reading and writing
to a DB table.

Yea, I could use XML and the .NET XML reader class but right now, timeis
of the essence.

--
dba123
"simida" wrote:

Another way, you can use EXCEL2003 open XLS file, and save as .XML
file. You will process XML file more easy in .NET 2.0.
>
You can also anaylse EXCEL file format to read data firstly. More
information about EXCEL file format, plz refer to this.
>
http://chicago.sourceforge.net/devel/docs/excel/
>
Sincerely,
simida
>
dba123 写�：
>
Thanks. What about reading the worksheet, how to pick out a certain column,
and pass that as a stream or something to a s tored procedure whichinserts
the data from the worksheet?

Here's my code so far:

// Insert Root Categories
string strConn;
strConn = "Provider=Micro soft.Jet.OLEDB. 4.0; Data
Source=C:\\test .xls; Extended Properties=Exce l 8.0;";
//You must use the $ after the object you reference in the
spreadsheet
OleDbDataAdapte r myCommand = new OleDbDataAdapte r("SELECT * FROM
[Root Levels$]", strConn);

DataSet myDataSet = new DataSet();
myCommand.Fill( myDataSet, "ExcelInfo" );

//Now, using stored proc, insert the data (Specific column in the dataset)
into database table.

Also, is there a better way or new way in .NET 2.0 to read an excelworksheet?

OR can I somehow create a stream then stream each row from the excel sheet
directly to my SQL Table?


--
dba123


"simida" wrote:

Maybe System.Data.Ole Db or ODBC will help you. I used OleDb to connect
EXCEL Worksheet.
>
connStr = "Provider=Micro soft.Jet.OLEDB. 4.0;Extended Properties=\"Ex cel
5.0;HDR=yes\";D ata Source=test.xls ;";
>
You can also use MSSQL Tools about Import/Export data Wizard to import
your worksheet data into DB.
>
Sincerely,
simida
>
dba123 写�：
>
I need some help and direction on what classes and an example or two
(article) on how to read an Excel Worksheet and insert one column into a
database table column. I am using .NET 2.0 only. What namespaces and
classes should I use and how?


--
dba123

>

>

[Code Monkey]

Forgot to say - once you've got the datatable with all the data, you're
better off using the SqlBulkCopy command if you're using SQL server,
ie;

private void BulkCopyIntoSQL (DataTable dt)
{
SqlBulkCopy bc = new SqlBulkCopy(myC onnection,
SqlBulkCopyOpti ons.Default);
bc.DestinationT ableName = "yourTableName" ;
bc.SqlRowsCopie d += new
SqlRowsCopiedEv entHandler(bc_S qlRowsCopied);
bc.NotifyAfter = 1;
bc.BatchSize = 250;
bc.WriteToServe r(dt);
bc.Close();
}

[Raj11]

Hi Code Monkey ,

I need to read the sheetnames of Excel files in my program using
ASP.Net 1.1.
I tried ur way of doing it, but .Net is unable to recognise functions
ExcelExtendedCo nn and Regex.
Could you please specify what namespaces/functions have been missing
here?

Thanks

[Code Monkey]

Sorry - my code was written in ASP.Net v2, but it should still work.

Guess I forgot to include other code!

OK here goes:

just pass the FULL file path of the excel spreadsheet into:

<code>
private static string ExcelExtendedCo nn(string excelFile)
{
return "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=\"" +
excelFile + "\";Extende d Properties=\"Ex cel 8.0;HDR=Yes;\"" ;
}
</code>

as for the Regex namespace: System.Text.Reg ularExpressions

Raj11 wrote:

Hi Code Monkey ,

I need to read the sheetnames of Excel files in my program using
ASP.Net 1.1.
I tried ur way of doing it, but .Net is unable to recognise functions
ExcelExtendedCo nn and Regex.
Could you please specify what namespaces/functions have been missing
here?

Thanks