Import from excel to Sql Server

asp.net bulkinsert sqlbulkcopy sql-server ssis

Question

I need to import data from Excel into SQL Server. Excel templates provide the data in excel. The columns in Excel are not the same across all of the templates since there are several Excel templates accessible for various clients. The information must be imported into a single shared SQL table.

I searched Google and found a ton of examples, but I can't decide which approach is the most effective.

Please advise me on how to do the aforementioned taking into account the dynamic columns of the templates.

The start row and finish row in the Excel template are not fixed.

1
-3
7/26/2013 3:22:31 PM

Accepted Answer

SSIS

A Data Flow Task The fundamental duty of moving data around inside an SSIS package is closely tied to the source provider's metadata. The metadata won't function in the same data flow if one source has string, int, string, and another contains string, string, string, string. The disparity in column count cannot be overcome when forcing the integer data into a string using the could method.

I would definitely seek to construct N+1 packages in your situation unless you have really complicated business logic to apply to the data arriving from these spreadsheets into the aggregated table. You will get a bundle including every conceivable Excel Format. I assume there is a formula for figuring out which template a file was taken from (ClientAV1.xlsx, ClientBV2.xlsx, etc). These packages would have a Data Flow that was Excel Source to OLE DB Destination. A parent/controller/master package would be the ultimate package. Based on the supplied files, it would manage the coordination of those other programs.

C#

I'm assuming you were okay with a.NET approach as well because you tagged asp.net. There is nothing stopping you from sending bulk instructions to import data into SQL Server when I demonstrate how to read in Excel files in.NET and feed it to an SSIS data flow in this answer.

10
5/23/2017 11:49:12 AM

Popular Answer

The easiest way to import data from an Excel file to a SQL file without having to worry about dynamic column mapping in SSIS is to utilize the Openrowset option. You may add it to the Execute SQL Task and use it as a command:

 SELECT * INTO <TableName> FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\Filename.xls', 'SELECT * FROM [Sheet$]')


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow