-3

I have a task to import excel data to Sql server. The excel data comes from excel templates. There are different excel templates available for different cleints so the columns in excel are not same accross all the templates. The data has to be imported to one common table of SQl.

I did googled and there is lot many samples available on the net, but i cannot identify which is the best method.

May i request you to please guide me on how to achieve the above considering dynamic columns of the templates.

In the excel template start row and end row are not fixed.

Santosh
  • 12,175
  • 4
  • 41
  • 72
  • Do you have 32bit or 64bit OS? Are you planning to use built in importing capabilities or build your own? Could other software packages apart from MS SQL Server be used? If no, could other software that is part of MSSQL apart from SQL Server Management Studio be used (like SSIS)? When your question covers these points, you are probably going to get the answer that meets your requirements. – OzrenTkalcecKrznaric Jul 31 '13 at 04:54
  • 1
    I'd say in the real world what you actually do is have a single import method and then you manually clean the Excel data to match each time. Somewhat depends on volumes of course, but when you have hoomans writing spreadsheets you just can't account for everything. – Paul Jul 31 '13 at 08:41

4 Answers4

10

SSIS

A Data Flow Task, the primary task for moving data about within an SSIS package, is tightly bound to the metadata of the source provider. If one source has string, int, string and the next has string, string, string, string, the metadata is going to be too different for that work in a the same data flow. While you could coerce the integer data into a string, the difference in column count cannot be overcome.

In your case, unless you have terribly complex business logic to apply to the data coming from these spreadsheets into the consolidated table, I would probably look to design N+1 packages. A package for each possible Excel Format you will receive. I'm assuming there is some logic for determining which template a file was sourced from (ClientAV1.xlsx, ClientBV2.xlsx, etc). These packages would have a Data Flow that was Excel Source to OLE DB Destination. The final package would be a parent/controller/master package. It would handle the coordination of those other packages based on available files.

C#

Since you've tagged asp.net, I'm assuming you were fine with a .NET approach as well. In this answer, I cover how you can read in Excel files in .NET and while I feed it to an SSIS data flow, there's nothing preventing you from issuing bulk commands to load data into SQL Server.

Community
  • 1
  • 1
billinkc
  • 59,250
  • 9
  • 102
  • 159
1

Dynamic column mapping in SSIS is quite tricky, the simplest method where you dont have to worry about column mapping is to use Openrowset option to import data from Excel file to sql file. You can use it as a command and add it in Execute SQL Task:

 SELECT * INTO <TableName> FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\Filename.xls', 'SELECT * FROM [Sheet$]')
Sonam
  • 3,406
  • 1
  • 12
  • 24
0

If you don't mind using a paid third-party SSIS component licensed on a subscription model, have a close look at DataDefractor SSIS. Once you get the hang of it, you can develop a package to import all but the most complex and variably-formatted Excel workbooks in under a day, including testing.

The alternative is writing code that takes days - or weeks - to develop and test. It all depends on what your time is worth to your employer.

The problem with this component is that when the license expires, it stops working, so you need to keep the license up-to-date. They have a perpetual license which they don't advertise, but IIRC, it costs something like 20 or 30 years worth of subscriptions.

Monty Wild
  • 3,981
  • 1
  • 21
  • 36
0

Use OpenRowSet or OpenDataSource

http://cleancode4devs.blogspot.in/2012/01/import-data-from-excel-to-sqlserver.html