excel to sql

[cj]

I've been asked to take thousands of excel files and import them into a
sql database. What's the best way to accomplish this? All the excel
files have the same structure and I can create a sql table with the same
structure. The excel files do have a header record. There are
approximately 30 fields/columns of varying types including dates and
boolean. approximately 50 records/rows per file.

 

[Cor Ligthert[MVP]]

cj,


For the best approach I would look what SQL tools there are available to
import. I surely would do this one time with a Net program.

If it is about one type of excel sheet, that has to be thousand times
imported in one SQL table, then I would look what OleDb could do for me.

As you look at the sample here you have almost everything

www.connectionstrings.com

Cor

 

[Rad [Visual C# MVP]]

I've been asked to take thousands of excel files and import them into a
sql database. What's the best way to accomplish this? All the excel
files have the same structure and I can create a sql table with the same
structure. The excel files do have a header record. There are
approximately 30 fields/columns of varying types including dates and
boolean. approximately 50 records/rows per file.

If the structure is exactly the same you might want to take a look at SQL
Server Integration Services (SQL 2005) or Data Transfer Services (SQL 2000,
SQL 7). They are designed for that sort of thing

 

[Wen Yuan Wang [MSFT]]

Thanks for Rad and Cor's prompt reply.

Hello cj,

There are a lot of choices for you to import Excel data into SQL server,
such as SSIS which suggested by Rad, or using ADO.net and the Jet Provider
which recommended by Cor.

If you need more information, I suggest you may have a look on the
following article. It demonstrates how to import data from Microsoft Excel
worksheets into Microsoft SQL Server databases by using a variety of
methods.
http://support.microsoft.com/kb/321686/en-us
[How to import data from Excel to SQL Server]

Hope this helps. If you have any more concern or there is anything unclear,
please feel free to let me know. We are glad to assist you.

Have a great day,
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.