Unexpected EOF encountered in BCP

Question

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.

https://stackoverflow.com/questions/52295174/unexpected-eof-encountered-in-bcp-data-file/52295180#52295180 — jophab, Sep 12 '18 at 12:50

score 6 · Accepted Answer · answered Jul 30 '15 at 07:00

6

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

answered Jul 30 '15 at 07:00

maxymoo

35,286
11
92
119

should it be `-t"\t"`? Like i did `-S"ServerName" -U"UserName"`? Also, what is `-t,`? – J.S. Orris Aug 05 '15 at 17:59
1

no don't use quotes, `-t` is short for field terminator i think – maxymoo Aug 05 '15 at 22:48
5

Actualy `tab` separated values is the default value for `bcp` so you don't have to specify any `-t` and `-r` at all. That's what I experienced by myself – Daria Mar 23 '16 at 07:40

score 5 · Answer 2 · answered Oct 13 '14 at 12:21

5

"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

Typical causes:

Unix vs Windows line endings
Text data containing your column delimiter (comma in actual data)
Or a mix of the two.

SSMS should have nothing to do with it: it's the format (expected vs actual) that matters

answered Oct 13 '14 at 12:21

gbn

422,506
82
585
676

Well I opened it in Notepad++ and the row terminator is CRLF. No embedded commas that I could find, but was getting the same error when I tab delimited it. – alemus Oct 14 '14 at 22:27
@alemus: Your command line has `-r\n` which is LF. It's possible the CR is confusing things. – gbn Oct 15 '14 at 08:32

score 5 · Answer 3 · answered Feb 01 '17 at 14:28

5

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!

answered Feb 01 '17 at 14:28

Scooter

51
1
1

score 5 · Answer 4 · answered Jul 16 '18 at 21:55

5

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.

answered Jul 16 '18 at 21:55

Clark Vera

183
1
7

I found that I needed to add a placeholder for my ID column value in the import file, even though it's an autogenerated index column. Seems to need that placeholder. – Andrew Schultz Jan 22 '21 at 00:16

score 5 · Answer 5 · answered Sep 19 '18 at 13:25

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.

score 4 · Answer 6 · answered Mar 08 '17 at 16:37

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

score 2 · Answer 7 · answered Aug 22 '19 at 12:39

2

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

answered Aug 22 '19 at 12:39

Hussain

449
4
4

Coffee and Code · Answer 8 · 2021-08-09T04:24:55.643

The answer to this puzzle is insidious. I spent time that I can never get back...

If you're in Windows, use NotePad++ and under "Encoding" on the menu, change it to:

UCS-2 LE BOM

LE = Little Endian...

Such a hateful error! And I just installed SQL Server 2019 and the latest SQLCMD/BCP tools. Seems this error has been around for a while.

This guy saved my life: https://shades-of-orange.com/post/Unexpected-EOF-encountered-in-BCP-data-file

score 1 · Answer 9 · answered Feb 13 '20 at 09:42

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).

Command used: bcp in -T -c -t"|^|"

score 1 · Answer 10 · answered Mar 11 '21 at 19:29

1

In my case the issue was that the record I was trying to import had an invalid foreign key

answered Mar 11 '21 at 19:29

richardprocter

125
1
2

Unexpected EOF encountered in BCP

10 Answers10

Linked