CODEWITHSUNDEEP
t-sqlsql-server-2017
Nani
Nani

Reputation: 89

How to import multiple CSV files into SQL Server tables?

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.

Upvotes: 0

Views: 3906

Answers (2)

John Meek
John Meek

Reputation: 181

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

Upvotes: 1

ASH
ASH

Reputation: 20302

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.

Upvotes: 1

Related Questions