How to import multiple CSV files into SQL Server tables?

Question

I am using SQL Server 2017 version, and I want to import multiple .csv files into multiple tables in SQL server.

I found the following script in the net,

--BULK INSERT MULTIPLE FILES From a Folder 

--a table to loop thru filenames drop table ALLFILENAMES
CREATE TABLE ALLFILENAMES(WHICHPATH VARCHAR(255),WHICHFILE varchar(255))

--some variables
declare @filename varchar(255),
        @path     varchar(255),
        @sql      varchar(8000),
        @cmd      varchar(1000)


--get the list of files to process:
SET @path = 'C:\Dump\'
SET @cmd = 'dir ' + @path + '*.csv /b'
INSERT INTO  ALLFILENAMES(WHICHFILE)
EXEC Master..xp_cmdShell @cmd
UPDATE ALLFILENAMES SET WHICHPATH = @path where WHICHPATH is null


--cursor loop
declare c1 cursor for SELECT WHICHPATH,WHICHFILE FROM ALLFILENAMES where WHICHFILE like '%.csv%'
open c1
fetch next from c1 into @path,@filename
While @@fetch_status <> -1
  begin
  --bulk insert won't take a variable name, so make a sql and execute it instead:
   set @sql = 'BULK INSERT Temp FROM ''' + @path + @filename + ''' '
       + '     WITH ( 
               FIELDTERMINATOR = '','', 
               ROWTERMINATOR = ''\n'', 
               FIRSTROW = 2 
            ) '
print @sql
exec (@sql)

  fetch next from c1 into @path,@filename
  end
close c1
deallocate c1

But the problem is I cannot use the command 'EXEC Master..xp_cmdShell' cause it was disabled by DBA's due to some security reasons, and they are not permitting me to use it. Is there any alternative command that I can use instead of 'xp_cmdShell' in the same script.

In this script near bulk insert command (set @sql = 'BULK INSERT Temp FROM ''' + @path + @filename + ''' ' + ') I see only one table name 'Test', and how can I mention multiple table names in the Bulk insert command?

Any help please.

Are you restricted to doing this via command? The sql server management studio has a build in function for this. But I am not sure if you can import multiple files for this or if you have to do it for each file. — Olli
– Olli, Commented May 21, 2020 at 15:09
Do all 15 files have exactly the same structure and order of column names? If so, lemme know because this is easily possible without the use of xp_CmdShell. As a bit of a side bar, DBAs that don't allow the use of xp_CmdShell simply don't know how to allow the use of it safely, which can also be easily done. Contrary to what most DBAs and Auditors (which may be the real source of disinformation here) think, xp_CmdShell is NOT a security risk when it's setup and used properly. — Jeff Moden
– Jeff Moden, Commented May 23, 2020 at 22:21
No, they have different structures and different column names. — Nani
– Nani, Commented May 25, 2020 at 2:28

ASH · Accepted Answer · 2020-05-21 15:01:11Z

1

It's been a long time since I have had to do this, but this is how I used to do these kinds of things.

DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=100)
BEGIN

PRINT @intFlag

declare @fullpath1 varchar(1000)
select @fullpath1 = '''\\FTP\' + convert(varchar, getdate()- @intFlag , 112) + '_your_file.csv'''
declare @cmd1 nvarchar(1000)
select @cmd1 = 'bulk insert [dbo].[your_table] from ' + @fullpath1 + ' with (FIELDTERMINATOR = ''\t'', FIRSTROW = 5, ROWTERMINATOR=''0x0a'')'
exec (@cmd1)

SET @intFlag = @intFlag + 1

END
GO

As you can tell, this is looping through a bunch of files with dates as file names. The first part of each file name was in this date format: convert(varchar, getdate()- @intFlag , 112)

I'm guessing your files have names that match some specific pattern.

answered May 21, 2020 at 15:01

ASH

20.5k28 gold badges117 silver badges247 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

John Meek · Accepted Answer · 2020-05-21 15:50:36Z

1

SQl Server has a tool that does this for you. Goto to your SQL Server folder Open SQL Server Import and Export Wizard. Choose a Data Source Microsoft Excel Select the Excel File. And following the steps

answered May 21, 2020 at 15:50

John Meek

1711 silver badge9 bronze badges

3 Comments

Nani Over a year ago

I need to do this on a daily basis, and I have to create a autosys job.

John Meek Over a year ago

learn.microsoft.com/en-us/sql/integration-services/…

Jeff Moden Over a year ago

Heh... lordy... no need to use 4 letter words in SQL Server. :D Once the OP replies to my question in the original post, I'll be happy to demonstrate provided that all 15 files are of the same structure.

Collectives™ on Stack Overflow

How to import multiple CSV files into SQL Server tables?

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related