How to Import multiple .csv files into different tables

Question

How to Import multiple .csv files into different tables

Shodhan

Old Hand

Points: 361
More actions
October 31, 2011 at 6:24 am

#247024

Hi,
Using SSIS I need to import multiple csv files into different table as per their file names. It should dynamically create tables as per the CSV files structure.
for eg: There are 40+ csv files in a folder and each file has a different table structure.
After importing the table name should be as below;
File Names Imported Table name
------------- --------------------------
File1.csv File1_Imported
File2.csv File2_Imported
How should I achieve this using for each loop container?
Note: Each file has different structures.
Can you please suggest any ideas.
Regards,
Shodhan

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

I'd use a dynamic SQL command to do Select Into from each file.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Shodhan Old Hand Points: 361 More actions · Answer 2

Thanks for your opinion, but can you please elaborate more on this.

Regards,

Shodhan

GSquared SSC Guru Points: 260824 More actions · Answer 3

"Select Into" will replicate the columns of the data-source into a new table. You can use dynamic SQL to build the query so that you can name the table whatever you want.

Look in Books Online (on online on MSDN) for the syntax for "Select Into". It has the details there.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

c.ajitkumar SSC Eights! Points: 973 More actions · Answer 4

Hi,

I am having the same situation can anybody help me on this by giving a details example or any helpful link

Thanks

Ajit

Shodhan Old Hand Points: 361 More actions · Answer 5

Hi Ajit,

The table structures in my case was diferent for each files. At that time due to urgency of project, I switched to TSQL and bcp in menthod using xp_cmdshell. I had the table structures ready with me and then created a stored procedure which takes the input file name and destination table name. You can achieve this using WHILE loop or a Cursor. There were no issues in the performance.. you can try this method.

If the table structures in your case are constant then it will be good to use the SSIS- for each loop container.

Below links will help you;

http://sqlage.blogspot.in/2011/02/create-table-for-each-flate-file-and.html

http://microsoft-ssis.blogspot.in/2011/02/how-to-configure-foreach-loop-file.html

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/a3e3273c-2c90-4d7b-91de-00f0569ecfb6/

Regards,

Shodhan

srnsr09 Valued Member Points: 67 More actions · Answer 6

Using SSIS I need to import multiple csv files into different table as per their file names. It should dynamically create tables as per the CSV files structure.

i am also facing the same problem have done this .

Can you help me