Click here to Skip to main content
15,940,550 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I am having an urgent issue. I couldn't find any answers for this specific scenario:

Import multiple Excel files, with multiple sheets, and different meta data into one SQL table. The first sheet of each Excel file (6000 files in total) has to be imported into a SQL table. There will only be one table for all the first sheets of these files together.

We found out that we have to use C# (script task in SSIS).

The meta data differs between the files. Below the column names of a few files to get an idea: - Balans (geconsolideerd) '13->'14 31-12-2014 31-12-2013 31-12-2012 31-12-2011 31-12-2010; - Balans (geconsolideerd) '07->'08 31-12-2008 31-12-2007 31-12-2006 - Balans (geconsolideerd) '13->'14 31-12-2014 31-12-2013 31-12-2012 31-12-2011 31-12-2010 31-12-2009 31-12-2008 31-12-2007 31-12-2006 31-12-2005 31-12-2004 31-12-2003

At the end, we are only interested in 'Balans (geconsolideerd)' and data of 5 years (current year - 2 and the 4 years before). So, right now 2014 - 2010. The column with the -> sign should be dropped, even as the year columns which are out of the range, but this will be step 2.

The first step is to get all the data of the first sheet of all the 6000 files into a data table/ temporary table.

Can you help me? If you need more information or if I need to clarify things, please let me know as well.

Thanks in advance.

Chelsea Disseldorp

What I have tried:

A lot of things already, unfortunately.
Posted
Updated 23-Aug-16 2:10am

1 solution

It is quite easy to import Excel information into a datatable as described in Working with MS Excel(xls / xlsx) Using MDAC and Oledb[^]. Once you have the raw data you can then manipulate and write the relevant information to your database via normal SQL.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900