Extracting Excel files from Multiple subfolders and load them into sql server table basing on city name

Question

I have a folder which contain's Sub folders(City Names) and in those sub folders I have excel files. I have to take all the excel files from the sub folder and load them into a SQL Server table basing on the city name. The files are structured as below.

Dallas Folder contain's these files

DALLAS_Cars_2011.xls
DALLAS_Trucks_2011.xls
DALLAS_Bikes_2011.xls

My requirement is I have to load the cars data into cars table and Trucks data into Trucks tables, basing on their names and all the city file's should go into same table like cars's, trucks. And I have to create an ID column basing on the city name it can be based on the alphabetical order can any of the guys help me in this.

all the cars data should go into the same table. all files have same column names, similarly with the trucks and bikes. An ID column should be created basing on the city name. — New to stackoverflow

Edper Edper · Accepted Answer · 2013-06-17T17:34:52

First of all I am using SQL Server Express 2008 (R2) and with this I was able to export MS Excel File into my Database. I know at least three ways to do this:

 1) Using Import and Export Data (whether 32 bit or 64 bit).
 2) Using SQL per se
 3) Programmatically like using C# or VB.net

In Import and Export Data just go to your MS SQL Server under All Programs and from there follow the direction. You specify the Data Source as MS Excel and then you Identify your Destination and make sure you Identity to which Database you want to put it.

enter image description here

In Using SQL you could simply use the following command just change accordingly to your own need.

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=YES;Database=c:\CityName\DALLAS_Cars_2011.xls','select * from [sheet1$]')

However, if you encounter problem like :

 OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

Try to add this lines:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=YES;Database=c:\CityName\DALLAS_Cars_2011.xls','select * from [sheet1$]')

Otherwise if you still encounter problem that probably means that your MS Excel is 32 bit and you are running on 64 bit SQL Server. So, you are better off doing the 1st method.

Finally if you know programming in VB.Net or C# then you could probably code it like this:

Dim cn As ADODB.Connection
Dim strSQL As String
Dim lngRecsAff As Long
Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB;Data Source=<server>;" & _
    "Initial Catalog=<database>;User ID=<user>;Password=<password>"

'Import by using OPENDATASOURCE.
strSQL = "SELECT * INTO XLImport6 FROM " & _
    "OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', " & _
    "'Data Source=C:\test\xltest.xls;" & _
    "Extended Properties=Excel 8.0')...[Customers$]"
cn.Execute strSQL, lngRecsAff, adExecuteNoRecords

See source here

Or you could also use a library from EPPlus like what I did in one of my program.

See its link here.

Extracting Excel files from Multiple subfolders and load them into sql server table basing on city name

2 Answers