Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Extracting Excel files from Multiple subfolders and load them into sql server table basing on city name Expand / Collapse
Author
Message
Posted Monday, June 17, 2013 10:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 19, 2014 9:39 AM
Points: 25, Visits: 335
Hi,
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...
Post #1464266
Posted Monday, June 17, 2013 12:20 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, June 23, 2014 8:04 AM
Points: 83, Visits: 174
You can write a simple .net application which will read excel files, manipulate data as you want and then insert into database.
Post #1464307
Posted Thursday, June 20, 2013 9:18 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 5:10 AM
Points: 434, Visits: 1,346
Can be done quite easily in SSIS.

See attached SSIS solution which will look through a directory and pop a message up when a xls file is found. All you need to do is adapt that to do the import etc.

My directory structure :

D:\Cities
D:\Cities\Dallas
D:\Cities\Another City

d:\Cities\Dallas\cars.xls
d:\Citiies\Another City\cars.xls
d:\Citiies\Another City\trucks.xls

you could add in further checks to make sure the file name contains truck, car etc before importing.





  Post Attachments 
ImportMeFiles.rar (7 views, 31.51 KB)
Post #1465765
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse