Solved

Import csv files to MS SQL

Posted on 2016-10-12
5
99 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
[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
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 29

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 66

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 66

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

Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Australian government abolished Visa 457 earlier this April and this article describes how this decision might affect Australian IT scene and IT experts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

726 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