How to bulk load several CSV files into SQL Server?

Question

I cobbled together the code below. I can write file paths into a table but I can't use the paths in the table to bulk load CSV files in a folder. Can some expert here take a look and let me know what's wrong? TIA.

IF OBJECT_ID('tempdb..#DirectoryTree') IS NOT NULL
DROP TABLE #DirectoryTree;

CREATE TABLE #DirectoryTree (
       id int IDENTITY(1,1)
      ,subdirectory nvarchar(512)
      ,depth int
      ,isfile bit);

INSERT #DirectoryTree (subdirectory,depth,isfile)
EXEC master.sys.xp_dirtree 'C:\my_path\CSV Files\',1,1;


SELECT * FROM #DirectoryTree
WHERE isfile = 1 AND RIGHT(subdirectory,4) = '.csv'
ORDER BY id;
GO
DROP TABLE ALLFILENAMES
--CREATE TABLE ALLFILENAMES(id VARCHAR(999),subdirectory VARCHAR(255),depth VARCHAR(1),isfile VARCHAR(1))

Select * INTO ALLFILENAMES
From #DirectoryTree


--code above is fine; problems start here
--cursor loop
--bulk insert won't take a variable name, so make a sql and execute it instead:
Declare @sql varchar(8000)
set @sql = 'BULK INSERT BULKACT FROM ''' + 'ALLFILENAMES.subdirectory' + ''' '
    + '     WITH ( 
            DATAFILETYPE = ''char'', 
            FIELDTERMINATOR = '','', 
            ROWTERMINATOR = ''\n'', 
            FIRSTROW = 2 
        ) '
print @sql
exec (@sql)

The problem is with the Bulk Insert. Here is the error message that I get: Msg 4860, Level 16, State 1, Line 28 Cannot bulk load. The file "ALLFILENAMES.subdirectory" does not exist.

So, 'ALLFILENAMES' is the name of the table and 'subdirectory' is the name of the field that contains all paths to all CSV files.

What is the problem you are facing actually ? Your bulk insert does not work ? You get an error ? what is the error message ? — Squirrel
– Squirrel, Commented Oct 1, 2018 at 1:36
1. Remove apostrophes (') around ALLFILENAMES.subdirectory. 2. Comment exec and analyze what print produces. — Alex Kudryashev
– Alex Kudryashev, Commented Oct 1, 2018 at 2:45
Ok, I did that and got this: BULK INSERT BULKACT FROM 'ALLFILENAMES.subdirectory' WITH ( DATAFILETYPE = 'char', FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', FIRSTROW = 2 ) So, the 'ALLFILENAMES.subdirectory' should be the path to the file, and it is that field, but this is giving me the table object but not the path inside that object. Also, I just realized that BULKACT should be the name of the new table that the data is being written to. — ASH
– ASH, Commented Oct 1, 2018 at 3:02

Squirrel · Accepted Answer · 2018-10-01 03:33:31Z

1

you need to do a select from that ALLFILENAMES table. You can't just specified the tablename + column name like that and expect it to work

also you need to specify the full path in the FROM file name

and you may use temp table for ALLFILENAMES instead of permanent tble

Declare @sql varchar(max)
select   @sql = isnull(@sql , '')
    + 'BULK INSERT BULKACT FROM ''C:\my_path\CSV Files\' + ALLFILENAMES.subdirectory + ''' '
    + '     WITH ( 
            DATAFILETYPE = ''char'', 
            FIELDTERMINATOR = '','', 
            ROWTERMINATOR = ''\n'', 
            FIRSTROW = 2 
        ); ' + char(13)
from    ALLFILENAMES
print @sql

and there is a WITH (FORMAT = 'CSV'); option for bulk insert from CSV file

answered Oct 1, 2018 at 3:33

Squirrel

24.8k5 gold badges41 silver badges37 bronze badges

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

2 Comments

ASH Over a year ago

Whoops, I wasn't paying attention there. I initially thought 'ALLFILENAMES.subdirectory' had the full path and the file name. I'm ok there. However, the 'BULKACT' is supposed to be the table object name, right. This needs to be updated dynamically, right. It almost looks like 'BULKACT' needs to be replaced with 'ALLFILENAMES.subdirectory' in the BULK INSERT statement. I can't test this now, as I don't have access to SQL Server now.

Squirrel Over a year ago

Yes BULKACT suppose to be a table.

ASH · Accepted Answer · 2018-10-02 02:23:59Z

Thanks for the help, Sqiurrel. I got the code below to work, and add some comments. It's really ugly though. I guess SQL Server really isn't designed for these kinds of things...

----------------------------------------------------------
-- Create 5 tables and all fields in tables
DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=5)
BEGIN

declare @cmd nvarchar(1000), 
@MyTableName nvarchar(100)
print str(@intFlag)

set @MyTableName = 'CSV' + replace(str(@intFlag),' ','')
print @MyTableName
set @cmd = 'CREATE TABLE dbo.' + quotename(@MyTableName, '[') + '(Name varchar(255), Address varchar(255), Age varchar(255), Work varchar(255));';
print @cmd
exec(@cmd)

SET @intFlag = @intFlag + 1

END
GO

----------------------------------------------------------

IF OBJECT_ID('tempdb..#DirectoryTree') IS NOT NULL
DROP TABLE #DirectoryTree;

CREATE TABLE #DirectoryTree (
       id int IDENTITY(1,1)
      ,subdirectory nvarchar(512)
      ,depth int
      ,isfile bit);

INSERT #DirectoryTree (subdirectory,depth,isfile)
EXEC master.sys.xp_dirtree 'C:\your_path_here\',1,1;

SELECT * FROM #DirectoryTree
WHERE isfile = 1 AND RIGHT(subdirectory,4) = '.csv'
ORDER BY id;
GO

DROP TABLE ALLFILENAMES
Select * INTO ALLFILENAMES
From #DirectoryTree

----------------------------------------------------------

--cursor loop
--bulk insert won't take a variable name, so make a sql and execute it instead:
Declare @sql varchar(max)
select   @sql = isnull(@sql , '')
    + 'BULK INSERT ' + ALLFILENAMES.subdirectory + ' FROM ''C:\your_path_here\' + ALLFILENAMES.subdirectory + ''' '
    + '     WITH ( 
            DATAFILETYPE = ''char'', 
            FIELDTERMINATOR = '','', 
            ROWTERMINATOR = ''\n'', 
            FIRSTROW = 2 
        ); ' + char(13)
from   ALLFILENAMES
print @sql
exec (@sql)

----------------------------------------

Collectives™ on Stack Overflow

How to bulk load several CSV files into SQL Server?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related