Solved

Import csv files to MS SQL

Posted on 2016-10-12
5
93 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 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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

This article was originally published on Monitis Blog, you can check it here . Today it’s fairly well known that high-performing websites and applications bring in more visitors, higher SEO, and ultimately more sales. By the same token, downtime…
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…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
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.

732 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