Importing Multiple csv files into one SQL Table

Question

Importing Multiple csv files into one SQL Table

ByronOne

SSCarpal Tunnel

Points: 4656
More actions
May 18, 2012 at 6:19 am

#261167

Hi everyone
I have a number of csv files in one folder location. I would like to import them into a single SQL Table. What would be the method to do this, Bulk Insert or use IS For Each Loop container? If the latter is there a very simplified step by step process for doing this or if the former what would be the correct code?
Thanks in advance
BO

Viewing 15 posts - 1 through 15 (of 24 total)

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

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 1

My preference would be to use bcp. Create a format file first then use the format file and put multiple bcp calls into a batch file, one for each csv file, with the same target table.

I don't know if bcp is the way you want to go with this, if so I'll work on the syntax and post it up.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

ByronOne SSCarpal Tunnel Points: 4656 More actions · Answer 2

Derek

Thanks for your quick response.

That would be great if you could post the syntax, really appreciated.

BO

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 3

Right, something like...

bcp myTable format myFormatFile.fmt -Sservername -T -ddbname -t, -E -k

Should create your format file, do bcp /? to take a look at all the options.

To import, something like:

bcp myTable in db_filename -E -k -Sservername -ddbname -T -t, -f myFormatFile.fmt

Obviously replace 'myTable', 'servername', 'dbname' and filenames with what you have.

You might need to fiddle with the syntax on this a bit but bcp is a great tool, when it works it works REALLY well and I've imported vast amounts of info using it from time to time. Setting up the format file is normally the greatest embuggerance.

You might decide using a script with multiple BULK INSERTs is the right way after all!

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

ByronOne SSCarpal Tunnel Points: 4656 More actions · Answer 4

ByronOne

SSCarpal Tunnel

Points: 4656

May 18, 2012 at 6:47 am

#1489629

Cheers Derek

I'll have a play!

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 5

Quick little tip for finding file names in a known directory without using xp_CmdShell...

EXEC xp_Dirtree 'folderpath',1,1

The first "1" says "for this level only" and the second "1" says to include file names instead of just directory names.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ByronOne SSCarpal Tunnel Points: 4656 More actions · Answer 6

Cheers Jeff

That's really useful to know and makes my life a little easier!

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 7

ByronOne (5/18/2012)
Cheers Jeff
That's really useful to know and makes my life a little easier!

You bet and thanks for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 8

ByronOne (5/18/2012)
Hi everyone
I have a number of csv files in one folder location. I would like to import them into a single SQL Table. What would be the method to do this, Bulk Insert or use IS For Each Loop container? If the latter is there a very simplified step by step process for doing this or if the former what would be the correct code?
Thanks in advance
BO

Is this a regular process? Are the files of identical format?

Personally, I would go for SSIS as you never know if it'll need to be done again.

ByronOne SSCarpal Tunnel Points: 4656 More actions · Answer 9

MJ

Yes the files are identical in format and it's very likely that the process will be needed going forward. I'd like to go down the SSIS route but I don't have a decent step by step guide to refer to...

BO

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 10

Pinal Dave has done a useful one for a single file

http://blog.sqlauthority.com/2011/05/12/sql-server-import-csv-file-into-database-table-using-ssis/[/url]

There are several references for using a for each container. This is the first one from google.

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

So the theory is, create the data flow using Pinal Dave's step-by-step, then apply the logic from the SQLIS (alan mitchell) link to loop over multiple files and assigning the file name to the data flow.

david.tyler SSCrazy Points: 2206 More actions · Answer 11

BO

I agree if the process will be used going forward you should experiment with SSIS. Here are some steps I used recently to setup a similar process:

-----------------------------------------

Add flat file connection -

within Connection Managers pane, add a Flat File Connection. Make sure to detail the Advanced page in the editor to setup your file columns. For now simply browse to a file within your processing share.

-----------------------------------------

Add Variables for your processing share and archive share if applicable. Also add a FileName variable to be used later.

-----------------------------------------

Add a Foreach Loop container to your project and set the Enumerator to Foreach File

Under Expressions, add the following:

* Property: Directory, Expression @[User::varFilePath] (or whatever you named your processing share variable)

* Property: FileSpec, Expression "*.dat" (or whatever your file ext is)

* Set Retrieve file name to Fully Qualified

Under Variable Mappings, add your FileName variable. Index should be 0 (zero).

-----------------------------------------

Add a Data Flow Task inside your Foreach container, provide a name and desc.

Double click Data Flow to enter Data Flow tab.

* Add a Flat File Source, double click and set connection to your previously setup Flat File Connection. Verify columns and error output settings.

* Optional - Add a Derived Column transform if you need to manipulate and column data before sending it to the DB

* Add an OLE DB Destination or whatever is appropriate in your situation, setup the data connection, select the destination table and any options.

* Verify Column Mappings and Error Output settings

Note: If you want rows in error to redirect to another table, set Error Output Error value to Redirect row and add another OLE DB Destination below.

-----------------------------------------

You can also add a File System Task in Control Flow to move files to an archive share to keep your processing share clean.

These steps are simply enough to get you started and may not be complete. You can add a lot more functionality to fit your business needs. Good luck and have fun with it.

Lowell SSC Guru Points: 323506 More actions · Answer 12

I'll go all devils advocate on the SSIS guys and present a solution i put together for other posts that is all TSQL.

Thsi requires xp_cmdshell to get the list of files.

the example assumes grabbing same-format files from multipel folders, sticking the results in a table for everything that needs to be processed.

--BULK INSERT MULTIPLE FILES

--a table to loop thru filenames drop table ALLFILENAMES

CREATE TABLE ALLFILENAMES(WHICHPATH VARCHAR(255),WHICHFILE varchar(255))

--the source table: yours already exists, but needed for this example.

CREATE TABLE BULKACT(RAWDATA VARCHAR (8000))

--some variables

declare @filename varchar(255),

@path varchar(255),

@sql varchar(8000),

@cmd varchar(1000)

--get the list of files to process:

--#########################################

SET @path = 'C:\DB\'

SET @cmd = 'dir ' + @path + '*.txt /b'

INSERT INTO ALLFILENAMES(WHICHFILE)

EXEC Master..xp_cmdShell @cmd

UPDATE ALLFILENAMES SET WHICHPATH = @path where WHICHPATH is null

SET @path = 'C:\DB2\'

SET @cmd = 'dir ' + @path + '*.txt /b'

INSERT INTO ALLFILENAMES(WHICHFILE)

EXEC Master..xp_cmdShell @cmd

UPDATE ALLFILENAMES SET WHICHPATH = @path where WHICHPATH is null

SET @path = 'C:\DB3\'

SET @cmd = 'dir ' + @path + '*.txt /b'

INSERT INTO ALLFILENAMES(WHICHFILE)

EXEC Master..xp_cmdShell @cmd

UPDATE ALLFILENAMES SET WHICHPATH = @path where WHICHPATH is null

SET @path = 'C:\DB4\'

SET @cmd = 'dir ' + @path + '*.txt /b'

INSERT INTO ALLFILENAMES(WHICHFILE)

EXEC Master..xp_cmdShell @cmd

UPDATE ALLFILENAMES SET WHICHPATH = @path where WHICHPATH is null

--#########################################

--cursor loop

declare c1 cursor for SELECT WHICHPATH,WHICHFILE FROM ALLFILENAMES where WHICHFILE like '%.txt%'

open c1

fetch next from c1 into @path,@filename

While @@fetch_status <> -1

begin

--bulk insert won't take a variable name, so make a sql and execute it instead:

set @sql = 'BULK INSERT BULKACT FROM ''' + @path + @filename + ''' '

+ ' WITH (

DATAFILETYPE = ''char'',

FIELDTERMINATOR = '','',

ROWTERMINATOR = ''\n'',

FIRSTROW = 2

) '

print @sql

exec (@sql)

fetch next from c1 into @path,@filename

end

close c1

deallocate c1

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ByronOne SSCarpal Tunnel Points: 4656 More actions · Answer 13

ByronOne

SSCarpal Tunnel

Points: 4656

May 18, 2012 at 2:22 pm

#1489955

You guys are ALL awesome!

I owe you all a beer!

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 14

Lowell (5/18/2012)
I'll go all devils advocate on the SSIS guys and present a solution i put together for other posts that is all TSQL.
Thsi requires xp_cmdshell to get the list of files.
the example assumes grabbing same-format files from multipel folders, sticking the results in a table for everything that needs to be processed.
.....

*cringe*

Sorry. I've seen xp_cmdshell and similar methods like that before. Oooo and a cursor. 🙂