Loading the 100 files(.csv) from folder along with file name

Question

Post reply

Loading the 100 files(.csv) from folder along with file name

Gangadhara MS

Hall of Fame

Points: 3377
More actions
September 26, 2011 at 10:14 pm

#248770

Dear All,
I have 100 number of .csv files where i am using data transformation task to load the data to database of SQL server 2005 manually/
I was manually running this but it was too much time consuming,could you pls help me in making in the for loop task.
Where i need to capture the file from folder and insert that file name to DB column name.
Any link or reference or guidance will be helpful.
Thanks,
Gangadhara

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Try this tutorial

http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 2

What all approaches have you tried? This seems relatively easy. Help us help you. 🙂

Raunak J

Gangadhara MS Hall of Fame Points: 3377 More actions · Answer 3

Hi

I have used for each loop task and configured to loop through the files and its working very fine now.

I have used variable which is pointing to Folder but the file name which i getting inserted is

E:\temp\PB\SR182510 PBS Item Data for January 1992.csv

But i need to insert file name as only January 1992 to column in DB.

Could u pls help me in this regard.

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 4

You could always use any of the STRING manipulation functions available like substring.

Raunak J

Johan Bijnens SSC Guru Points: 135379 More actions · Answer 5

If it is only loading csv data you might as well choose then new powershell approach:

Loading Data With Powershell[/url]

It comes with all advantages of a scripting language which are nice if you really don't have to perform all transformation stuff SSIS is good in.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me