SQL 在 BCP 中遇到意外的 EOF
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26339092/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Unexpected EOF encountered in BCP
提问by alemus
Trying to import data into Azure. Created a text file in Management Studio 2005. I have tried both a comma and tab delimited text file.
尝试将数据导入 Azure。在 Management Studio 2005 中创建了一个文本文件。我尝试了逗号和制表符分隔的文本文件。
BCP IN -c -t, -r\n -U -S -P I get the error {SQL Server Native Client 11.0]Unexpected EOF encountered in BCP data file
BCP IN -c -t, -r\n -U -S -PI 得到错误 {SQL Server Native Client 11.0] 在 BCP 数据文件中遇到意外的 EOF
Here is the script I used to create the file:
这是我用来创建文件的脚本：
SELECT top 10 [Id]
,[RecordId]
,[PracticeId]
,[MonthEndId]
,ISNULL(CAST(InvoiceItemId AS VARCHAR(50)),'') AS InvoiceItemId
,[Date]
,[Number]
,[RecordTypeId]
,[LedgerTypeId]
,[TargetLedgerTypeId]
,ISNULL(CAST(Tax1Id as varchar(50)),'')AS Tax1Id
,[Tax1Exempt]
,[Tax1Total]
,[Tax1Exemption]
,ISNULL(CAST([Tax2Id] AS VARCHAR(50)),'') AS Tax2Id
,[Tax2Exempt]
,[Tax2Total]
,[Tax2Exemption]
,[TotalTaxable]
,[TotalTax]
,[TotalWithTax]
,[Unassigned]
,ISNULL(CAST([ReversingTypeId] AS VARCHAR(50)),'') AS ReversingTypeId
,[IncludeAccrualDoctor]
,12 AS InstanceId
FROM <table>
Here is the table it is inserted into
这是它插入的表
CREATE TABLE [WS].[ARFinancialRecord](
[Id] [uniqueidentifier] NOT NULL,
[RecordId] [uniqueidentifier] NOT NULL,
[PracticeId] [uniqueidentifier] NOT NULL,
[MonthEndId] [uniqueidentifier] NOT NULL,
[InvoiceItemId] [uniqueidentifier] NULL,
[Date] [smalldatetime] NOT NULL,
[Number] [varchar](17) NOT NULL,
[RecordTypeId] [tinyint] NOT NULL,
[LedgerTypeId] [tinyint] NOT NULL,
[TargetLedgerTypeId] [tinyint] NOT NULL,
[Tax1Id] [uniqueidentifier] NULL,
[Tax1Exempt] [bit] NOT NULL,
[Tax1Total] [decimal](30, 8) NOT NULL,
[Tax1Exemption] [decimal](30, 8) NOT NULL,
[Tax2Id] [uniqueidentifier] NULL,
[Tax2Exempt] [bit] NOT NULL,
[Tax2Total] [decimal](30, 8) NOT NULL,
[Tax2Exemption] [decimal](30, 8) NOT NULL,
[TotalTaxable] [decimal](30, 8) NOT NULL,
[TotalTax] [decimal](30, 8) NOT NULL,
[TotalWithTax] [decimal](30, 8) NOT NULL,
[Unassigned] [decimal](30, 8) NOT NULL,
[ReversingTypeId] [tinyint] NULL,
[IncludeAccrualDoctor] [bit] NOT NULL,
[InstanceId] [tinyint] NOT NULL,
CONSTRAINT [PK_ARFinancialRecord] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
There are actually several hundred thousand actual records and I have done this from a different server, the only difference being the version of management studio.
实际上有几十万条实际记录，我是从不同的服务器完成的，唯一的区别是管理工作室的版本。
采纳答案by maxymoo
If the file is tab-delimited then the command line flag for the column separator should be -t\t-t,
如果文件是制表符分隔的，那么列分隔符的命令行标志应该是 -t\t-t,
回答by gbn
"Unexpected EOF" normally means means the column or row terminator is not what you expect That is, your command line arguments for these do match the file
“意外的 EOF”通常意味着列或行终止符不是您所期望的也就是说，您的命令行参数与文件匹配
Typical causes:
典型原因：
- Unix vs Windows line endings
- Text data containing your column delimiter (comma in actual data)
- Or a mix of the two.
- Unix 与 Windows 行尾
- 包含列分隔符的文本数据（实际数据中的逗号）
- 或者两者的混合。
SSMS should have nothing to do with it: it's the format (expected vs actual) that matters
SSMS 应该与它无关：重要的是格式（预期与实际）
回答by Scooter
Just an FYI that I encountered this same exact error and it turned out that my destination table contained one extra column than the DAT file!
仅供参考，我遇到了同样的确切错误，结果我的目标表包含比 DAT 文件多一列！
回答by Dave Fish
I think most of us prefer real-world examples than syntax hints, so here's what I did:
我认为我们中的大多数人更喜欢现实世界的例子而不是语法提示，所以我是这样做的：
bcp LoadDB.dbo.test in C:\temp\test.txt -S 123.66.108.207 -U testuser -P testpass -c -r /r
bcp LoadDB.dbo.test in C:\temp\test.txt -S 123.66.108.207 -U testuser -P testpass -c -r /r
My data was an extract from a Unix-based Oracle DB which was tab delimited and had an LF end of line character.
我的数据是从基于 Unix 的 Oracle DB 中提取的，该数据库以制表符分隔并具有 LF 行尾字符。
Because my data was tab delimited I did not specify a -t parameter, the bcp default is tab.
因为我的数据是制表符分隔的，所以我没有指定 -t 参数，bcp 默认是制表符。
Because my row terminator was a LineFeed (LF) character, then I used -r /r
因为我的行终止符是 LineFeed (LF) 字符，所以我使用了 -r /r
Because my data was all being loaded into char fields I used the -c parameter
因为我的数据都被加载到字符字段中，所以我使用了 -c 参数
回答by Clark Vera
I every case that I have encountered this error, it ends up being an issue where the number of columns in the table do not the match the number of columns delimited in the text file. The easy way to confirm this is to load the text file into excel and compare the column count to that of the table.
我在遇到此错误的每种情况下，最终都会成为一个问题，即表中的列数与文本文件中分隔的列数不匹配。确认这一点的简单方法是将文本文件加载到 excel 并将列数与表的列数进行比较。
回答by Hussain
Open the CSV file in EXCEL and "save as" new CSV file
在 EXCEL 中打开 CSV 文件并“另存为”新的 CSV 文件
回答by user12891176
I was facing the same error while trying to bcp in the records from datafile to table. A workaround which works is just open the file in Notepad++ or similar editor and add extra line at the end of the file.This worked for my case - field separator - |^|, row separator - new line (LRCF).
尝试在从数据文件到表的记录中进行 bcp 时，我遇到了同样的错误。一种有效的解决方法是在 Notepad++ 或类似编辑器中打开文件并在文件末尾添加额外的行。这适用于我的情况 - 字段分隔符 - |^|，行分隔符 - 新行（LRCF）。
Command used: bcp in -T -c -t"|^|"
使用的命令：bcp in -T -c -t"|^|"
回答by ProgSky
I will share my experience with this issue. My users were sending me UTF-8 encoding and everything was working fine. My load started to fail when they updated the encoding to Encode in UCS-2 LE BOM. Use notepad++ to check these setting.
我将分享我在这个问题上的经验。我的用户向我发送 UTF-8 编码，一切正常。当他们将编码更新为 UCS-2 LE BOM 中的编码时，我的加载开始失败。使用记事本++检查这些设置。
Reverting back to UTF-8 fixed my problem.
恢复到 UTF-8 解决了我的问题。
This linkhelped me resolving my issue.
此链接帮助我解决了我的问题。
