Sql Bulk Insert -- File does not exist

Question

I have the following query to insert into a table

    BULK
     INSERT tblMain
     FROM 'c:\Type.txt'
     WITH
     (
      FIELDTERMINATOR = ',',
      ROWTERMINATOR = '\n'
     )
    GO

It get the message

Msg 4860, Level 16, State 1, Line 1
Cannot bulk load. The file "c:\Type.txt" does not exist.

The file is clearly there. Anything I may be overlooking?

possible duplicate of [sql import into sql server 2008](http://stackoverflow.com/questions/10016427/sql-import-into-sql-server-2008) — , Apr 04 '12 at 18:06
Are you using MS SQL Server? Are you running SSMS to run he query? — Icarus, Apr 04 '12 at 18:07
Is the file **on the SQL Server machine's drive C:** ?? Or on your local PC? The SQL Server machine (assuming its a remote machine - not your own PC) **cannot** read your own local `C:\` drive! (thankfully so!!) — marc_s, Apr 04 '12 at 19:13

score 30 · Accepted Answer · edited May 23 '17 at 12:10

30

Look at that: Cannot bulk load. The file "c:\data.txt" does not exist

Is that file on the SQL Server's C:\ drive??

SQL BULK INSERT etc. always works only with local drive on the SQL Server machine. Your SQL Server cannot reach onto your own local drive.

You need to put the file onto the SQL Server's C:\ drive and try again.

edited May 23 '17 at 12:10

Community

1
1

answered Apr 04 '12 at 18:07

Ezio Auditore da Firenze

1,186
13
18

@ FerhadJabiyev : Thanks for answering. Please include appropriate commands / references when you answer. – Jayan Apr 05 '12 at 06:10
@Jayan Yes you are true. Sorry. I am new in StackOverflow. – Ezio Auditore da Firenze Apr 05 '12 at 06:23
1

@ FerhadJabiyev : No problem. Keep posting answers, upvote suitable answers- ask some good questions as well. – Jayan Apr 05 '12 at 06:25

score 16 · Answer 2 · answered Apr 05 '12 at 06:09

Bulk import utility syntax is described here

http://msdn.microsoft.com/en-us/library/ms188365.aspx

> BULK INSERT     [ database_name . [ schema_name ] . | schema_name . ]
> [ table_name | view_name ] 
>       FROM 'data_file' 
>      [ WITH 
>     (

Note on data_file argument says

' data_file '

Is the full path of the data file that contains data to import into the specified table or view. BULK INSERT can import data from a disk (including network, floppy disk, hard disk, and so on).

data_file must specify a valid path from the server on which SQL Server is running. If data_file is a remote file, specify the Universal Naming Convention (UNC) name. A UNC name has the form \Systemname\ShareName\Path\FileName. For example, \SystemX\DiskZ\Sales\update.txt.

This is a good answer, because it includes the fact that you can use a network path. It doesn't necessarily need to be the SQL Server's local machine. — Nicholas Hill, Feb 25 '13 at 12:17

score 0 · Answer 3 · answered May 17 '16 at 04:14

I've had this problem before. In addition to checking the file path you'll want to make sure you're referencing the correct file name and file type. Make sure this is indeed a text file that you have saved in the source location and not a word file etc. I got tripped up with .doc and .docx. This is a newb mistake of mine to make but hey, it can happen. Changed the file type and it fixed the problem.

Sql Bulk Insert -- File does not exist

3 Answers3

Linked