Solved

Import csv files to MS SQL

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

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Learn by example how to specify CSS selectors for Selenium WebDriver test automation software.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
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.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

896 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now