How to import high format data from Excel to database?

Question

How to import high format data from Excel to database?

What is the best way to import high format data from Excel to SQL Server. Basically I have 250 Excel files exported from the reporting tool in the format our business users prefer. It is a third party tool and cannot export data in any other format. I need to "clean up" these files monthly and import them into the database. I want to use SQL Server 2005

The file formats are as follows:

                                                         Report Name

                                                     Report Description

                                    MTH/DEC/2003 MTH/JAN/2004 MTH/FEB/2004 
                                    Data Type  Data Type    Data Type

Grouping 1                           1900         1700         2800

  Grouping 2                         1500         900          1300

    Detail                           300          500          1000

    Detail                           1100         200          200

    Detail                           100          200          100

+1

sql-server excel ssis

Kalel 05 jan. 09 at 18:56

source to share

7 replies

Berek Bryan · Answer 1 · 2009-01-05T19:00:22+0000

you can write a simple parser. there are many api that will handle reading excel files.

I wrote one in java and it only took a day or two.

here is one api.

Luck

EDIT: Forgetting to mention, we also need sql api like JDBC . Again we are using JDBC for most of our applications and it works great.

HLGEM · Answer 2 · 2009-01-05T19:17:45+0000

Personally, I would do it using SSIS. Perhaps nothing is trivial as the file format looks relatively complex (but I suspect it is true no matter which tool you use), but as long as it stays consistent it will run fast every month and packages SSIS is easy to take control of the source. Since SSIS is part of SQL Server, it is easy to ensure that all servers are available. The key is to have a good understanding of how this format relates to how you store data in the database. This is the hard part no matter what tool you use.

EdgarVerona · Answer 3 · 2009-01-05T19:05:22+0000

Assuming you have Microsoft Excel, you can also use your own ActiveX interface, open to you. More information here:

http://msdn.microsoft.com/en-us/library/wss56bz7(VS.80).aspx

You can use this in anything ActiveX can use (C ++, VB6, VB.NET, etc.) to create a parser, and also follow what Berek said.

ForYourOwnGood · Answer 4 · 2009-01-05T19:06:10+0000

I've done this before with perl and MYSQL. I wrote a simple perl script that parsed a file and output the contents to a .sql file. This can then be done manually or included in a perl script, open MYSQL and use the .sql file.

Mark bostleman · Answer 5 · 2009-01-05T19:24:53+0000

You might want to look at CLR procedures and functions in SQL Server. With a CLR procedure, you can do all of your cleanup in a VB or C # .NET application, but still run jobs from SQL Server just like any other stored procedure or UDF.

Hans · Answer 6 · 2009-01-05T19:53:04+0000

This may sound a little oversimplified, but you can simply dump the data in csv format and parse the output to convert to insert statements for SQL.

score 0 · Answer 7 · 2009-01-05T20:20:36+0000

For Java based application, POI ( http://poi.apache.org/ ) is pretty good for Excel integration applications.

0

05 jan. 09 at 20:20

source to share

How to import high format data from Excel to database?

More articles: