Import multiple csv files into one table

Question

I am using Micorsoft SQL Server Managment Studio. I have a folder names "CSVfeeds" on the sql server. It has multiple CSV files from each day. Each csv file is the same format. How can I import all of the csv files into one table? It would also help if the table could have an extra field to store the title of the csv file that row came from.

Example: csv1: Name, ID John, 1

csv2: Name, ID Steve, 2

Table: Name, ID, File John, 1, csv1 Steve, 2, csv2

I researched I few options like bulk insert. It just isn't coming together for me. I am familiar with SSIS packages, but I can think of a way to insert an undefined amount of csv files into the table with SSIS.

Any Help?

Brian Knight · Accepted Answer · 2013-09-11 18:22:44Z

4

You can use an SSIS For Each Container to iterate over the files in a directory. The ForEach File iterator within lets you specify a folder and the files to enumerate, the format of the file name of the retrieved files, and whether to traverse subfolders.

Within the For Each container, you can set a variable to contain the path of the file being iterated over, and use that variable value as the path of the CSV input in your Data Flow Task.

Here's a link to an example.

Hope this helps!

answered Sep 11, 2013 at 18:22

Brian Knight

5,04130 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Import multiple csv files into one table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related