Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Inserting multiple CSV files into SQL server

Posted on 2016-09-12
6
Medium Priority
?
73 Views
Last Modified: 2016-09-14
I have about 1000 files that I need to import into a table in SQL.  Are there any tools out there to make this easier?
If not, what's the best way?

Currently using SQL Express 2012.
0
Comment
Question by:Brian Sandt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 41794894
SSIS has a ForEach File Loop container where you can point to a specific folder and loop through every file in the folder.  Then inside this container you can add a Data Flow Task that consumes the file in the loop as the source, and have your table as the destination.

This assumes that the 1000 files have exactly the same file specs.
0
 

Author Comment

by:Brian Sandt
ID: 41794904
From what I've read, Express doesn't support SSIS.
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 41794905
Hi,

Sorry for the copy'n'paste of external links, but I thought going straight to the "horse's mouth" may be better for your background reading:

"Import and Export Bulk Data by Using the bcp Utility (SQL Server)" (MS-SQL Server 2012)
[ https://msdn.microsoft.com/en-gb/library/aa337544(v=sql.110).aspx ]

"bcp Utility" (MS-SQL Server 2012)
[ https://msdn.microsoft.com/en-gb/library/ms162802(v=sql.110).aspx ]

Also, unless already set in your version of MS-SQL Server 2012 Express:

"View or Configure Remote Server Connection Options"
[ https://msdn.microsoft.com/en-us/library/ms179383(v=sql.110).aspx ]
0
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 
LVL 27

Expert Comment

by:Zberteoc
ID: 41795006
Do all those files have the same data structure in them? If that is the case then it is easy:

1. Create the table with the corresponding structure where the data from the files will be imported. If you are not sure of the data types for the fields then using a generic NVARCHAR will make sure it will "swallow" anything you might encounter.

2. Create a list with all the files you have in the folder. If they are in different folders it is better to copy them in one.

3. Use the list to create the BCP commands that will take each file in the list and will import it into the table created at step 1. This can be done easily in a batch file that will be executed on any machine on the same network with the target SQL server that has BCP installed on it. Can be the SQL server itself. BCP is a command line utility that comes together with the SQL server installation and it is the fastest tool there is when it comes to importing files.
0
 
LVL 40

Accepted Solution

by:
lcohan earned 2000 total points
ID: 41795057
Here is what I use to restore DB backups and adapted for your CSV import. Just make the few changes to put your actual Path, YourFileNameHere, YourTableNameHere, etc and you can safely run the script below at it will only print the commands until you will uncomment the EXEC line at the bottom.

I would suggest to try it against a copy of the actual table to make sure ALL rows from ALL files go in there safely first.

to enable the xp_cmdshell you can run commands:

--Enable xp_cmdshell

exec sp_configure 'show advanced options', 1;
GO
sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure
GO


---script to import N CSV's into a table

declare @sqlstr nvarchar(max);
declare @restdb nvarchar(max);
declare @i table (col1 varchar(max) null);
declare @fn varchar(255);
set nocount on;

set @sqlstr = N'exec master..xp_cmdshell ''dir C:\FullPathHere\YourFileNameHere.csv /o-d'''

insert @i execute sp_executesql @sqlstr

--you have all files listed in @i table
--select * from @i where col1 like '%.csv'

-- start a loop here to go through all files to restore
WHILE EXISTS (select top 1 * from @i where col1 like '%.csv')
BEGIN -- loop

            --lets get the first one - you need to to a bit of string parsing to get the filename
            --select top 1 * from @i where col1 like '%.csv'
            set @fn = (select top 1 substring(col1, PATINDEX('%YourFileNameHere%',col1), datalength(col1) - PATINDEX('%YourFileNameHere%',col1)+1) from @i where col1 like '%.csv');
            print @fn;

            --build the command string to be executed - insert in your case
            SET @restdb =
            'BULK INSERT YourTableNameHere
            FROM ' + @fn +
            ' WITH
            (
            FIELDTERMINATOR = '','',
            ROWTERMINATOR = ''\n''
            )';

            print @restdb;
            --exec sp_executesql @restdb;      -- *****!!!! uncomment this line and comment all PRINT command lines to perform the actual statemet instead of just print on screen
            if @@ERROR <> 0 BREAK;

            -- delete the file we just procesed so is not done again in the loop
            delete @i where col1 like @fn;

END -- loop
GO
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41797822
@lcohan

It is better if you post any code within the code tag.
0

Featured Post

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question