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.OleDb or ODBC will help you. I used OleDb to connect
EXCEL Worksheet.

connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel
5.0;HDR=yes\";Data 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=Microsoft.Jet.OLEDB.4.0; Data
Source=C:\\test.xls; Extended Properties=Excel 8.0;";
//You must use the $ after the object you reference in the
spreadsheet
OleDbDataAdapter myCommand = new OleDbDataAdapter("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.OleDb or ODBC will help you. I used OleDb to connect
EXCEL Worksheet.

connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel
5.0;HDR=yes\";Data 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=Microsoft.Jet.OLEDB.4.0; Data
Source=C:\\test.xls; Extended Properties=Excel 8.0;";
//You must use the $ after the object you reference in the
spreadsheet
OleDbDataAdapter myCommand = new OleDbDataAdapter("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.OleDb or ODBC will help you. I used OleDb to connect
EXCEL Worksheet.

connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel
5.0;HDR=yes\";Data 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=Microsoft.Jet.OLEDB.4.0; Data
Source=C:\\test.xls; Extended Properties=Excel 8.0;";
//You must use the $ after the object you reference in the
spreadsheet
OleDbDataAdapter myCommand = new OleDbDataAdapter("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.OleDb or ODBC will help you. I used OleDb to connect
EXCEL Worksheet.
>
connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel
5.0;HDR=yes\";Data 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=Microsoft.Jet.OLEDB.4.0; Data
Source=C:\\test.xls; Extended Properties=Excel 8.0;";
//You must use the $ after the object you reference in the
spreadsheet
OleDbDataAdapter myCommand = new OleDbDataAdapter("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.OleDb or ODBC will help you. I used OleDb to connect
EXCEL Worksheet.

connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel
5.0;HDR=yes\";Data 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 = ExcelExtendedConn(excelFile);
using (OleDbConnection objConn = new
OleDbConnection(connString))
{
objConn.Open();
dt =
objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables , 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.IsMatch(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(String excelFile, String sheetName)
{
string connString = ExcelExtendedConn(excelFile);
using (OleDbConnection conn = new
OleDbConnection(connString))
{
OleDbCommand odc = new
OleDbCommand(string.Format("Select * FROM [{0}$]", sheetName), conn);
conn.Open();
OleDbDataReader reader = odc.ExecuteReader();
DataTable sheetSchema = reader.GetSchemaTable();
return sheetSchema;
}
}
</code>

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

<code>
public DataTable originalData(String _filename,String _sheet)
{
DataTable dt = new DataTable();
OdbcConnection conn = new OdbcConnection();
OdbcCommand cmd = new OdbcCommand();
conn.ConnectionString = string.Format(@"Driver={{Microsoft
Excel Driver (*.xls)}};DBQ={0};ReadOnly=0;", _filename);
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM [" + _sheet + "$]";
OdbcDataAdapter oda = new OdbcDataAdapter();
oda.SelectCommand = 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=Microsoft.Jet.OLEDB.4.0; Data
Source=C:\\test.xls; Extended Properties=Excel 8.0;";
//You must use the $ after the object you reference in the
spreadsheet
OleDbDataAdapter myCommand = new OleDbDataAdapter("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.OleDb or ODBC will help you. I used OleDb to connect
EXCEL Worksheet.
>
connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel
5.0;HDR=yes\";Data 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(myConnection,
SqlBulkCopyOptions.Default);
bc.DestinationTableName = "yourTableName";
bc.SqlRowsCopied += new
SqlRowsCopiedEventHandler(bc_SqlRowsCopied);
bc.NotifyAfter = 1;
bc.BatchSize = 250;
bc.WriteToServer(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
ExcelExtendedConn 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 ExcelExtendedConn(string excelFile)
{
return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"" +
excelFile + "\";Extended Properties=\"Excel 8.0;HDR=Yes;\"";
}
</code>

as for the Regex namespace: System.Text.RegularExpressions

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
ExcelExtendedConn and Regex.
Could you please specify what namespaces/functions have been missing
here?

Thanks