[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 125
  • Last Modified:

Import csv files to MS SQL

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
Aleks
Asked:
Aleks
4 Solutions
 
Kyle AbrahamsSenior .Net DeveloperCommented:
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
 
Pawan KumarDatabase ExpertCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
Daniel JonesData Research AnalystCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Tackle projects and never again get stuck behind a technical roadblock.
Join Now