Grabbing a list of CSV files then bulk inserting them into a table

Question

I have a folder of CSV files that I am trying to bulk insert into an already made table. I thought at first the issue was not having the CSV's in the local hard drive, but it looks like I jacked some thing else up too.

Query I am running is below:

DECLARE @ALLFILENAMES TABLE (WHICHPATH VARCHAR(255),WHICHFILE varchar(255))
declare @filename varchar(255),
        @path     varchar(255),
        @sql      varchar(8000),
        @cmd      varchar(1000)


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

SELECT * FROM @ALLFILENAMES 

--cursor loop
declare c1 cursor 
for SELECT WHICHPATH,WHICHFILE 
FROM @ALLFILENAMES
open c1
fetch next from c1 into @path,@filename
While @@fetch_status <> -1
  begin

--make a query and execute
   set @sql = 'BULK INSERT [SVTTASKS].[dbo].[OOS_AWG_X05] FROM ''' + @path + @filename + ''' '
       + '     WITH (firstrow = 2,
                fieldterminator = '','', 
                rowterminator=''\n'')'
print @sql
exec (@sql)

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

Error received is:

(7 rows affected)

BULK INSERT [SVTTASKS].[dbo].[OOS_AWG_X05] FROM 'C:\Invoices\IGG_Data_StoreNum\X05\ Volume in drive C has no label.' WITH (firstrow = 2, fieldterminator = ',', rowterminator='\n') Msg 4860, Level 16, State 1, Line 1 Cannot bulk load. The file "C:\Invoices\IGG_Data_StoreNum\X05\ Volume in drive C has no label." does not exist.

BULK INSERT [SVTTASKS].[dbo].[OOS_AWG_X05] FROM 'C:\Invoices\IGG_Data_StoreNum\X05\ Volume Serial Number is 1653-5277' WITH (firstrow = 2, fieldterminator = ',', rowterminator='\n') Msg 4860, Level 16, State 1, Line 1 Cannot bulk load. The file "C:\Invoices\IGG_Data_StoreNum\X05\ Volume Serial Number is 1653-5277" does not exist.

BULK INSERT [SVTTASKS].[dbo].[OOS_AWG_X05] FROM 'C:\Invoices\IGG_Data_StoreNum\X05\ Directory of C:\Invoices\IGG_Data_StoreNum\X05*.csv ' WITH (firstrow = 2, fieldterminator = ',', rowterminator='\n') Msg 4861, Level 16, State 1, Line 1 Cannot bulk load because the file "C:\Invoices\IGG_Data_StoreNum\X05\ Directory of C:\Invoices\IGG_Data_StoreNum\X05*.csv " could not be opened. Operating system error code 123(The filename, directory name, or volume label syntax is incorrect.).

BULK INSERT [SVTTASKS].[dbo].[OOS_AWG_X05] FROM 'C:\Invoices\IGG_Data_StoreNum\X05\File Not Found' WITH (firstrow = 2, fieldterminator = ',', rowterminator='\n') Msg 4860, Level 16, State 1, Line 1 Cannot bulk load. The file "C:\Invoices\IGG_Data_StoreNum\X05\File Not Found" does not exist.

Clearly your dir command is returning additional output from `dir` that you are not expecting. But this is a mess even without that. Use PowerShell. — Aaron Bertrand, Nov 15 '21 at 20:14
Just grabbed the [first "answer" from searching](https://stackoverflow.com/questions/16076309/import-multiple-csv-files-to-sql-server-from-a-folder) — SMor, Nov 15 '21 at 20:17
Yeah, I did grab most of this from another answer. Then modified to my needs. Found the issue after the dir command comment. The quotations after the file type gave fits. Everything else worked like a champ and the table is loaded. Big thank you to the Stack Overflow community. — Anthony Bondi, Nov 16 '21 at 14:42
`PRINT @cmd;` would have been an invaluable debugging tool. :-) — Aaron Bertrand, Nov 17 '21 at 22:05

score 0 · Answer 1 · answered Nov 16 '21 at 14:47

The quotations after the file type gave fits. Everything else worked like a champ and the table is loaded. Big thank you to the Stack Overflow community.

DECLARE @ALLFILENAMES TABLE (WHICHPATH VARCHAR(255),WHICHFILE varchar(255))
declare @filename varchar(255),
        @path     varchar(255),
        @sql      varchar(8000),
        @cmd      varchar(1000)



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

SELECT * FROM @ALLFILENAMES 


--cursor loop
declare c1 cursor 
for SELECT WHICHPATH,WHICHFILE 
FROM @ALLFILENAMES
open c1
fetch next from c1 into @path,@filename
While @@fetch_status <> -1
  begin


--make a query and execute
   set @sql = 'BULK INSERT [SVTTASKS].[dbo].[OOS_AWG_X05] FROM ''' + @path + @filename + ''' '
       + '     WITH (firstrow = 2,
                fieldterminator = '','', 
                rowterminator=''\n'')'
print @sql
exec (@sql)

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

Grabbing a list of CSV files then bulk inserting them into a table

1 Answers1