28

I'm having a problem reading data from a text file into ms sql. I created a text file in my c:\ called data.txt, but for some reason ms sql server cannot find the file. I get the error "Cannot bulk load. The file "c:\data.txt" does not exist." Any ideas?

The data file (yes I know the data looks crappy, but in the real world thats how it comes from clients):

01-04 10.338,18 0,00 597.877,06- 5 0,7500 62,278-
06-04 91.773,00 9.949,83 679.700,23- 1 0,7500 14,160-
07-04 60.648,40 149.239,36 591.109,27- 1 0,7500 12,314-
08-04 220.173,70 213.804,37 597.478,60- 1 0,7500 12,447-
09-04 986.071,39 0,00 1.583.549,99- 3 0,7500 98,971-
12-04 836.049,00 1.325.234,79 1.094.364,20- 1 0,7500 22,799-
13-04 38.000,00 503.010,49 629.353,71- 1 0,7500 13,111-
14-04 286.400,00 840.126,50 75.627,21- 1 0,7500 1,575-

The Sql:

CREATE TABLE #temp
(
    vchCol1 VARCHAR (50),
    vchCol2 VARCHAR (50),
    vchCol3 VARCHAR (50),
    vchCol4 VARCHAR (50),
    vchCol5 VARCHAR (50),
    vchCol6 VARCHAR (50),
    vchCol7 VARCHAR (50)
)

BULK insert #temp
FROM 'c:\data.txt'
WITH
(
FIELDTERMINATOR = ' ',
ROWTERMINATOR = '\n'
)

select * from #temp
drop table #temp
Daniel Brink
  • 2,434
  • 4
  • 24
  • 26

3 Answers3

57

That's run on the server, so its looking for C:\data.txt on the server's C: drive.

Also ensure the logon your using has read permissions on C:.

Alex K.
  • 171,639
  • 30
  • 264
  • 288
  • 1
    So for some we already got this, but so.. what is changed to make it work? To pull the file from your localdrive thru the network? Or is there a missing statment that it's impossible to have the server pull the file vai some ip or something? Cheers -Jeremy – Quantum Dec 07 '11 at 21:14
  • 1
    You would need to pull it over a unc share – Alex K. Dec 08 '11 at 10:42
  • Semma!! (In Tamil) - Now only, I know this point. Thanks. – Pugal Apr 01 '21 at 05:15
14

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.

Update: @bp_, ok, correct - the file can also be on a share that you can access from the SQL Server machine using an UNC path. But again: that share must be created first, and the user the SQL Server process is running under must have access permissions to that share. You cannot just simply grab a file from a local drive on your PC without setting up quite a bit of infrastructure overhead first

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
1

This is mostly permission issue. you may not have permission on that drive. Make sure the logon you are using has read or if possible full control permission. It worked for me on local machine.

Abde
  • 11
  • 2