Unable to get format file to work

Question

Unable to get format file to work

ron.abbott

SSC Veteran

Points: 264
More actions
April 22, 2018 at 7:19 pm

#403289

I am importing a text file with tab delimiters into an SQL2005 table.
The BCP command works fine without specifying the -f option
bcp Rest01.dbo.RestCreditors IN P:\RestCreditorsNoH.txt -c -T -S ".\SQLDEVELOPMENT"
but as soon as I add the format switch and remove the -c option
bcp Rest01.dbo.RestCreditors IN P:\RestCreditorsNoH.txt -T -S ".\SQLDEVELOPMENT" -f P:\BCPCrs.fmt
I get
Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unexpected EOF encountered in BCP data-file
0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1
BCP format file attached as well as the data file

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 1

ron.abbott - Sunday, April 22, 2018 7:19 PM
I am importing a text file with tab delimiters into an SQL2005 table.
The BCP command works fine without specifying the -f option
bcp Rest01.dbo.RestCreditors IN P:\RestCreditorsNoH.txt -c -T -S ".\SQLDEVELOPMENT"
but as soon as I add the format switch and remove the -c option
bcp Rest01.dbo.RestCreditors IN P:\RestCreditorsNoH.txt -T -S ".\SQLDEVELOPMENT" -f P:\BCPCrs.fmt
I get
Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unexpected EOF encountered in BCP data-file
0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1
BCP format file attached as well as the data file

I'm not seeing the BCP format file attached.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ron.abbott SSC Veteran Points: 264 More actions · Answer 2

Sorry - it looks like it only allowed one attachement.
Here it is
9.0
29
1   SQLCHAR   0   400 "/t" 1 CreditorName            SQL_Latin1_General_CP1_CI_AS
2   SQLCHAR   0   400 "/t" 2 AddressLine1            SQL_Latin1_General_CP1_CI_AS
3   SQLCHAR   0   400 "/t" 3 AddressLine2            SQL_Latin1_General_CP1_CI_AS
4   SQLCHAR   0   400 "/t" 4 AddressLine3            SQL_Latin1_General_CP1_CI_AS
5   SQLCHAR   0   400 "/t" 5 AlphaIndex             SQL_Latin1_General_CP1_CI_AS
6   SQLCHAR   0   400 "/t" 6 HomePhone             SQL_Latin1_General_CP1_CI_AS
7   SQLCHAR   0   400 "/t" 7 WorkPhone             SQL_Latin1_General_CP1_CI_AS
8   SQLCHAR   0   400 "/t" 8 Fax               SQL_Latin1_General_CP1_CI_AS
9   SQLCHAR   0   400 "/t" 9 Contact              SQL_Latin1_General_CP1_CI_AS
10  SQLCHAR   0   400 "/t" 10 BroughtForward           SQL_Latin1_General_CP1_CI_AS
11  SQLCHAR   0   400 "/t" 11 CurrentDue             SQL_Latin1_General_CP1_CI_AS
12  SQLCHAR   0   400 "/t" 12 Paid               SQL_Latin1_General_CP1_CI_AS
13  SQLCHAR   0   400 "/t" 13 Balance              SQL_Latin1_General_CP1_CI_AS
14  SQLCHAR   0   400 "/t" 14 LastDatePaid            SQL_Latin1_General_CP1_CI_AS
15  SQLCHAR   0   400 "/t" 15 Mobile              SQL_Latin1_General_CP1_CI_AS
16  SQLCHAR   0   400 "/t" 16 Email              SQL_Latin1_General_CP1_CI_AS
17  SQLCHAR   0   400 "/t" 17 PaymentMethod            SQL_Latin1_General_CP1_CI_AS
18  SQLCHAR   0   400 "/t" 18 PayName              SQL_Latin1_General_CP1_CI_AS
19  SQLCHAR   0   400 "/t" 19 PayDetail1             SQL_Latin1_General_CP1_CI_AS
20  SQLCHAR   0   400 "/t" 20 PayDetail2             SQL_Latin1_General_CP1_CI_AS
21  SQLCHAR   0   400 "/t" 21 PayDetail3             SQL_Latin1_General_CP1_CI_AS
22  SQLCHAR   0   400 "/t" 22 ABN               SQL_Latin1_General_CP1_CI_AS
23  SQLCHAR   0   400 "/t" 23 ABNConfirmed            SQL_Latin1_General_CP1_CI_AS
24  SQLCHAR   0   400 "/t" 24 Salutation             SQL_Latin1_General_CP1_CI_AS
25  SQLCHAR   0   200 "/t" 25 InsuranceDueDate           SQL_Latin1_General_CP1_CI_AS
26  SQLCHAR   0   400 "/t" 26 DocManagementFilename         SQL_Latin1_General_CP1_CI_AS
27  SQLCHAR   0   400 "/t" 27 SMSEmailAddr            SQL_Latin1_General_CP1_CI_AS
28  SQLCHAR   0   400 "/t" 28 ActiveStatus            SQL_Latin1_General_CP1_CI_AS
29  SQLCHAR   0   400 "/r" 29 ChangeDate             SQL_Latin1_General_CP1_CI_AS

ron.abbott SSC Veteran Points: 264 More actions · Answer 3

It was generated using the format option of the bcp command

BCP Rest01.dbo.RestCreditors Format null -T -c -S ".\SQLDEVELOPMENT" -r/r -t/t -f "bcpCrs.fmt" -e P:\BCPErrors.txt

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 4

ron.abbott - Sunday, April 22, 2018 7:53 PM
It was generated using the format option of the bcp command
BCP Rest01.dbo.RestCreditors Format null -T -c -S ".\SQLDEVELOPMENT" -r/r -t/t -f "bcpCrs.fmt" -e P:\BCPErrors.txt

I can see one problem immediately in your format file. You have the line terminator as / r and it needs to be / r / n (without the spaces) in this case. I checked the text file you attached and the lines definitely end with ^C_R^L_F

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ron.abbott SSC Veteran Points: 264 More actions · Answer 5

OK, changed last line to :

29 SQLCHAR 0 400 "/r/n" 29 ChangeDate SQL_Latin1_General_CP1_CI_AS

still same EOF error

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 6

Also, since this is a really clean and consistent text file, why are you bothering with a BCP format file to begin with?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ron.abbott SSC Veteran Points: 264 More actions · Answer 7

Because I have 6 more text files, and would like to exclude certain fields going to the table.

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 8

Ah. Ok. Speaking of tables, can you post the CREATE TABLE statement for the table this file is supposed to go into? That way, I can do some testing.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ron.abbott SSC Veteran Points: 264 More actions · Answer 9

I used Script Table as Create to Clipboard, then pasted this :

USE [Rest01]
GO

/****** Object: Table [dbo].[RestCreditors] Script Date: 23/04/2018 2:12:38 p.m. ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[RestCreditors](
    [CreditorName] [nvarchar](200) NULL,
    [AddressLine1] [nvarchar](200) NULL,
    [AddressLine2] [nvarchar](200) NULL,
    [AddressLine3] [nvarchar](200) NULL,
    [AlphaIndex] [nvarchar](200) NOT NULL,
    [HomePhone] [nvarchar](200) NULL,
    [WorkPhone] [nvarchar](200) NULL,
    [Fax] [nvarchar](200) NULL,
    [Contact] [nvarchar](200) NULL,
    [BroughtForward] [nvarchar](200) NULL,
    [CurrentDue] [nvarchar](200) NULL,
    [Paid] [nvarchar](200) NULL,
    [Balance] [nvarchar](200) NULL,
    [LastDatePaid] [nvarchar](200) NULL,
    [Mobile] [nvarchar](200) NULL,
    [Email] [nvarchar](200) NULL,
    [PaymentMethod] [nvarchar](200) NULL,
    [PayName] [nvarchar](200) NULL,
    [PayDetail1] [nvarchar](200) NULL,
    [PayDetail2] [nvarchar](200) NULL,
    [PayDetail3] [nvarchar](200) NULL,
    [ABN] [nvarchar](200) NULL,
    [ABNConfirmed] [nvarchar](200) NULL,
    [Salutation] [nvarchar](200) NULL,
    [InsuranceDueDate] [nvarchar](100) NULL,
    [DocManagementFilename] [nvarchar](200) NULL,
    [SMSEmailAddr] [nvarchar](200) NULL,
    [ActiveStatus] [nvarchar](200) NULL,
    [ChangeDate] [nvarchar](200) NULL,
CONSTRAINT [PK_RestCreditors] PRIMARY KEY CLUSTERED
(
    [AlphaIndex] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 10

That should do... let me see what gives.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 11

I was able to duplicate the error even after adding the / n. Looking...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ron.abbott SSC Veteran Points: 264 More actions · Answer 12

The txt file was generated by a software package that I am unfamiliar with.
Not sure why the format file suggests lengths of 400, and the create table says 200 for all bu one field.

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 13

ron.abbott - Sunday, April 22, 2018 8:25 PM
The txt file was generated by a software package that I am unfamiliar with.
Not sure why the format file suggests lengths of 400, and the create table says 200 for all bu one field.

Good grief. If the problem was a snake, it would have bitten me. All the slashes need to be changed to backslashes in the format file.

Also, the reason they all suggest 400 is so that the allowable size of the field in the file is bigger than the allowable size in the table so that if the file field data is larger than what the table will handle, it'll pop up an error instead of artificially truncating it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ron.abbott SSC Veteran Points: 264 More actions · Answer 14

Jeff Moden - Sunday, April 22, 2018 9:08 PM
ron.abbott - Sunday, April 22, 2018 8:25 PM
The txt file was generated by a software package that I am unfamiliar with.
Not sure why the format file suggests lengths of 400, and the create table says 200 for all bu one field.
Good grief. If the problem was a snake, it would have bitten me. All the slashes need to be changed to backslashes in the format file.
Also, the reason they all suggest 400 is so that the allowable size of the field in the file is bigger than the allowable size in the table so that if the file field data is larger than what the table will handle, it'll pop up an error instead of artificially truncating it.

It worked ! - Thanks so much.
Any idea why would the format file be generated with / instead of \ ?