Unexpected EOF encountered in BCP

2020-02-26 10:18发布

站内问答 / 后端开发
927 8 5

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.

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

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.

|improve this question

标签：
举报
8条回答
可以哭但决不认输i
2楼-- · 2020-02-26 10:36

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

My data was an extract from a Unix-based Oracle DB which was tab delimited and had an LF end of line character.

Because my data was tab delimited I did not specify a -t parameter, the bcp default is tab.

Because my row terminator was a LineFeed (LF) character, then I used -r /r

Because my data was all being loaded into char fields I used the -c parameter

查看更多
0人赞 添加讨论(0) 举报
▲ chillily
3楼-- · 2020-02-26 10:40

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.

Reverting back to UTF-8 fixed my problem.

This link helped me resolving my issue.

enter image description here

查看更多
0人赞 添加讨论(0) 举报
老娘就宠你
4楼-- · 2020-02-26 10:48

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.

查看更多
0人赞 添加讨论(0) 举报
劫难
5楼-- · 2020-02-26 10:52

Open the CSV file in EXCEL and "save as" new CSV file

enter image description here

查看更多
0人赞 添加讨论(0) 举报
再贱就再见
6楼-- · 2020-02-26 10:54

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!

查看更多
0人赞 添加讨论(0) 举报
The star\"
7楼-- · 2020-02-26 10:56

If the file is tab-delimited then the command line flag for the column separator should be -t\t -t,

查看更多
0人赞 添加讨论(0) 举报
1 2 下一页
登录 后发表回答
相关问题
查看全部
相关文章
查看全部
收藏的人(5)