英文:
SQL Server BCP load data by using master.dbo.xp_cmdshell - Errors and server with multiple instances
问题
我在将数据从txt/csv文件加载到SQL Server表时遇到了一些错误：
错误 = [Microsoft][ODBC Driver 11 for SQL Server]无法打开BCP主机数据文件
错误 = [Microsoft][ODBC Driver 11 for SQL Server]在BCP数据文件中遇到意外的EOF
英文:
I get some errors when loading data from txt/csv file into SQL Server table:
> Error = [Microsoft][ODBC Driver 11 for SQL Server]Unable to open BCP host data-file
> Error = [Microsoft][ODBC Driver 11 for SQL Server]Unexpected EOF encountered in BCP data-file
答案1
得分: 1
前提条件：使用BCP命令需要您的用户帐户具有足够的权限访问该文件夹，例如创建文件、编辑文件或删除文件。并且在使用时文件未被打开。例如，下面是示例代码，d:_temp将是要检查的文件夹。
注意：当服务器有多个实例，如dev、test、preprod或prod时，您将需要添加@@Servername来区分用于加载数据的哪个实例。
例如：ServerName将采用此格式：DTC001\Dev、DTC001\Test
以下是查询数据或将数据加载到表中的正确格式：
-- 从txt文件加载数据到表中：
Declare @sql varchar(8000)
Declare @FileName varchar(100) = '123out.txt'
Declare @Path varchar(1000) = 'd:\_temp\';
if object_id('tempdb..##tt_test') > 0 drop table ##tt_test;
CREATE TABLE ##tt_test (FileLine VARCHAR(max));
select @sql = 'bcp ##tt_test in "' + @Path + @FileName + '" /c /t \n /k /S' + @@SERVERNAME + ' /T'
--print @sql
EXEC master.dbo.xp_cmdshell @sql;
select * from ##tt_test
-- 将数据查询到txt/csv文件中。成功。
declare @str varchar(8000)
Declare @FileName varchar(100) = '1234588.txt'
Declare @Path varchar(1000) = 'd:\_temp\';
Declare @SelectCMD varchar(200) = 'select top 10 col1 from testdb.dbo.table1';
Set @str = 'EXEC master..xp_Cmdshell ''bcp "' + @SelectCMD + '" queryout "' + @Path + @FileName + '" -c -t"" -S' + @@servername + ' -T'''
Print @str
EXEC(@str);
以下是我在将数据从txt/csv文件加载到SQL Server表时遇到的一些常见错误：
错误1：错误 = [Microsoft][ODBC Driver 11 for SQL Server]无法打开BCP主机数据文件
可能原因：找不到路径或文件名。我在使用时犯了一个语法错误，使用了两个单引号''而不是双引号"，导致了这个错误。请确保您在文件路径中使用双引号。
错误命令：EXEC master.dbo.xp_cmdshell 'bcp ##tt_test in ''d:_temp\123.txt'' -c -T';
错误2：错误 = [Microsoft][ODBC Driver 11 for SQL Server]在BCP数据文件中遇到意外的EOF
可能原因：文本文件可能没有以正确的符号结尾。
123out.txt是我使用的文件
另一个常见原因是：表列与txt/csv文件不匹配也会导致此错误。
英文:
Pre-condition: using BCP command need your user account has enough permission to that file folder, like create a file, edit a file or delete a file. And also the file is not opened when using it. For example, the sample code below, d:_temp will the the folder to check.
Note: When the server has multiple instances, like dev, test, preprod or prod, you will have to add @@Servername which will distinguish which instance is used to load data into.
eg: ServerName will be this format: DTC001\Dev, DTC001\Test
Here are the correct format to query out data or load data into table:
--Load data from txt file into table:
Declare @sql varchar(8000)
Declare @FileName varchar(100) ='123out.txt'
Declare @Path varchar(1000) ='d:\_temp\';
if object_id('tempdb..##tt_test') > 0 drop table ##tt_test;
CREATE TABLE ##tt_test (FileLine VARCHAR(max));
select @sql = 'bcp ##tt_test in "' + @Path + @FileName + '" /c /t \n /k /S' + @@SERVERNAME + ' /T'
--print @sql
EXEC master.dbo.xp_cmdshell @sql;
select * from ##tt_test
--queryout data into txt/csv file. successfully.
declare @str varchar(8000)
Declare @FileName varchar(100) ='1234588.txt'
Declare @Path varchar(1000) ='d:\_temp\';
Declare @SelectCMD varchar(200) ='select top 10 col1 from testdb.dbo.table1';
Set @str ='EXEC master..xp_Cmdshell ''bcp "' + @SelectCMD +'" queryout "' + @Path + @FileName + '" -c -t"" -S' + @@servername + ' -T'''
Print @str
EXEC(@str);
here are some popular errors I got when loading data from txt/csv file into SQL server table:
Error 1: Error = [Microsoft][ODBC Driver 11 for SQL Server]Unable to open BCP host data-file
Possible reasons: could not find the path or filename. I made a syntax error to use '' instead of double quote " which cause this error. see the sample code below. Please make sure you are using double quote for the file path.
Error command: EXEC master.dbo.xp_cmdshell 'bcp ##tt_test in ''d:\_temp3.txt'' -c -T';
Error 2: Error = [Microsoft][ODBC Driver 11 for SQL Server]Unexpected EOF encountered in BCP data-file
Possible reasons: the text file may not ending with correct symbol.
the 123out.txt is the one I use
Another popular reason is: The table columns do not match the txt/csv file will create this error also.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论