Importing csv in sql server on Mac os with docker gives permission error

Question

I am trying to fill in via Bulk insert statement from a local csv file like this:

BULK INSERT Simpson_Paradox 
FROM '/Users/amreshpandey/SimpsonParadoxCase/SimpsonParadox.csv'
 with (fieldterminator = ',', rowterminator = '\n')

But I get the following error:

[02:25:43] Started executing query at Line 1 Msg 4860, Level 16, State 1, Line 1 Cannot bulk load. The file "/Users/amreshpandey/SimpsonParadoxCase/SimpsonParadox.csv" does not exist or you don't have file access rights. Total execution time: 00:00:00.007

What am I doing wrong? am I specifying the path to file incorrectly? Many thnx.

OS is Mac High Sierra

Answer 1

I had the same issue. This worked for me:

First, I copied file from my local file system to that of docker container through:

docker cp /Users/ainura/Desktop/file.txt sql_container:/

(sql_container is the container name in this case).

Then, I run the bulk insert sql query but still had issue with rowterminator. Instead of writing '\n' for row terminator I tried hex value and it worked perfectly, seems like this is happening because of how file was generated:

BULK INSERT Table_name from '/file.txt' 
with (fieldterminator = ',', rowterminator = '0x0a');

I hope this helps!