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
Solved

Import csv files to MS SQL

Posted on 2016-10-12
5
84 Views
Last Modified: 2016-10-13
I am looking for a software that will import about 100 csv spreadsheets into a MS SQL 2008R2 database.
I found DBForge software but it only imports one spreadsheet at the time.
Is there a way to import all at once where each spreadsheet is a separate table ?
0
Comment
Question by:amucinobluedot
5 Comments
 
LVL 40

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 83 total points
ID: 41841143
This is based off of:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/47c8edc1-8cad-4a24-a09a-3fc0c943325c/bulk-insert-multiple-files-tsql?forum=transactsql

You can run this in sql, comments inline.

Note that this assumes the .csv file will be the table name.  (EG:  Employees.csv will have an existing Employees Table already created).

Also note this code is untested and done by hand.  please test this on a DB you don't care about or have a backup for.

-- the path to where the CSV files are located.
declare @path varchar(1000) = 'C:\ImportFiles\' -- needs trailing SLASH

CREATE TABLE #files (name varchar(200) NULL, sqlText varchar(7000) NULL)

-- get the files, put them in the temp table.
INSERT #files(name)
   exec master..xp_cmdshell 'dir /b ' + @path  + '*.csv'


-- should now have an individualized command for every file.
UPDATE #files
SET   sqlText = 'BULK INSERT '  + replace(name,'.csv.','') + ' FROM ''' + name + ''' WITH (' +
             'DATAFILETYPE = ''char'', FIELDTERMINATOR = '','', ' +
             'ROWTERMINATOR = ''\n'')'

  --  . . . if you want to review
  -- select * from #files


DECLARE @sql varchar(8000)

DECLARE cur CURSOR STATIC LOCAL FOR
   SELECT sqlText FROM #files

OPEN cur

WHILE 1 = 1
BEGIN
   FETCH cur INTO @sql
   IF @@fetch_status <> 0
      BREAK
   -- I always like to see what is executing.
    print @sql
  -- fire it off.
   EXEC(@sql)
END

DEALLOCATE cur

Open in new window

0
 
LVL 28

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 83 total points
ID: 41841270
In this case You can use SSIS package where you can create a for each loop and for each speadsheet you can read the data and insert the data in the database.

SSIS also gives you the flexibility to transform the data in between.  

Enjoy!!
0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 83 total points
ID: 41841290
Guessing that Kyle's table-driven solution is the only possible option, as we're talking 100 unique source and destination combinations, so an SSIS package would have to contain 100 unique data flow tasks.  Guessing PowerShell would exhibit the same behavior.
0
 
LVL 4

Accepted Solution

by:
Daniel Jones earned 251 total points
ID: 41841396
You can take the help of this link http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/title-12/ it will show you six ways to import data into SQL Server.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41842474
Thanks for the split, good luck with your project.  -Jim

@Daniel - Do us a favor and avoid posting 'links only' answers to questions, as EE put out a policy two years ago saying they will be removed as it negatively impacts EE's SEO rankings, and if the link goes bad now there's a comment without value.  Thanks in advance.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

When crafting your “Why Us” page, there are a plethora of pitfalls to avoid. Follow these five tips, and you’ll be well on your way to creating an effective page.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
The is a quite short video tutorial. In this video, I'm going to show you how to create self-host WordPress blog with free hosting service.

856 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