Load .csv file from current directory with Bulk Insert: SQL Server

Question

I am currently trying to do a bulk insert in a .sql file to insert .csv data into one of my SQL Server database's table. However, I need to be able to load the .csv file from its relative directory instead of using the full path. The .csv file is currently in the same directory as the .sql file.

I would like to do something like this

BULK INSERT team
FROM './FinalProject/teams.csv'
WITH
(
    FORMAT = 'CSV', 
    FIELDQUOTE = '"',
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',  --CSV field delimiter
    ROWTERMINATOR = '0x0a',   --Use to shift the control to next row
    TABLOCK
);
GO

but it cannot find the file.

How can I load the file without hardcoding its path?

That's very product specific. Which dbms are you using?

jarlh
– jarlh

2020-11-30 19:13:25 +00:00
Commented Nov 30, 2020 at 19:13 — jarlh
– jarlh, Commented Nov 30, 2020 at 19:13
I'm using SSMS currently.

Aaron
– Aaron

2020-11-30 19:49:06 +00:00
Commented Nov 30, 2020 at 19:49 — Aaron
– Aaron, Commented Nov 30, 2020 at 19:49

CoffeeNeedCoffee · Accepted Answer · 2020-11-30 19:29:14Z

1

If you need a dynamic path, then you need a dynamic query. Build your BULK IMPORT statement before you execute it, as it does not accept a dynamic path.

/* figure out your dynamic path before this point, and store it here */
DECLARE @mypath varchar(50) = './FinalProject/teams.csv' 

/* build your BULK INSERT query */
DECLARE @sql varchar(max) = 'BULK INSERT team 
FROM '''+@mypath+'''
WITH
(
    FORMAT = ''CSV'', 
    FIELDQUOTE = ''"'',
    FIRSTROW = 2,
    FIELDTERMINATOR = '','',  --CSV field delimiter
    ROWTERMINATOR = ''0x0a'',   --Use to shift the control to next row
    TABLOCK
);'

PRINT @sql -- debug

/* run your BULK INSERT query */
EXEC(@sql)

answered Nov 30, 2020 at 19:29

CoffeeNeedCoffee

1,5746 silver badges13 bronze badges

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

Collectives™ on Stack Overflow

Load .csv file from current directory with Bulk Insert: SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related