How should I import highly formatted data from Excel to a database?

Question

What is the best way to import highly formatted data from Excel to SQL server. Basically I have 250+ Excel files that have been exported from a reporting tool in a format that our business users would prefer. This is a 3rd party tool that can not export data in any other format. I need to "scrub" these files on a monthly basis and import them into a database. I want to use SQL Server 2005

File formats look like this:

                                                         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

Answer 1

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

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

here is one api.

Good Luck

EDIT: Forgot to mention we will also need a sql api such as JDBC. Again we use JDBC for the majority of our applications and works great.

Answer 2

Assuming that you have Microsoft Excel, you can also use Excel's own exposed ActiveX interface. More information here:

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

You could use that in anything that can use ActiveX (C++, VB6, VB.NET etc...) to create a parser as well, to follow-up on what Berek said.

Answer 3

I have done this before with perl and MYSQL. I wrote a simple perl script which parsed the file and output the contents to a .sql file. Then, this can eather be done manually or included in the perl script, open MYSQL and use the .sql file.

Answer 4

Personally I would do it using SSIS. It might not be trivial to set up as the file format looks relatively complex (but that I suspect might be true no matter what tool you use), but as long as it stays consistent, it will run quickly each month and SSIS packages are easy to put under source control. Since SSIS is part of SQL Server it is easy to make sure allthe servers have it available. The key is do have a good understanding of how that format relates to how you store data in the database. THat's the hard part no matter what tool you use.

Answer 5

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

Answer 6

This may seem a bit simplistic, but you could simply dump the data in csv format and do some parsing of the output to convert to insert statements for SQL.

Answer 7

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