不能批量加载。“c:\数据的文件。txt”并不存在

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?

将文本文件中的数据读入ms sql会有问题。我在我的c:\中创建了一个名为data的文本文件。txt，但是由于某些原因，ms sql server无法找到该文件。我得到的错误“不能批量装载”。“c:\数据的文件。三种“不存在。”什么好主意吗?

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:

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

3 个解决方案

#1

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

这是在服务器上运行的，所以它在寻找C:\数据。服务器C:驱动器上的txt。

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

还要确保您的登录具有C:的读权限。

#2

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.

这个文件在SQL Server的C:\ drive上吗?SQL大容量插入等等总是只在SQL服务器机器上的本地驱动器上工作。您的SQL服务器无法访问您自己的本地驱动器。

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

您需要将文件放到SQL服务器的C:\驱动器上，然后再试一次。

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

更新:@bp_，好的，正确——这个文件也可以在一个共享上，您可以使用UNC路径从SQL服务器机器访问这个共享。但是，必须首先创建该共享，并且SQL Server进程运行的用户必须具有该共享的访问权限。您不能简单地从PC上的本地驱动器获取文件，而不首先设置相当大的基础设施开销

#3

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.

这主要是权限问题。你可能在那个驱动器上没有得到许可。确保您正在使用的登录已经读取，或者如果可能的话是完全控制权限。它在本地机器上对我起作用。

#1