Unable to get format file to work

  • ron.abbott

    SSC Veteran

    Points: 234

    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

  • Jeff Moden

    SSC Guru

    Points: 995150

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • ron.abbott

    SSC Veteran

    Points: 234

    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: 234

    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: 995150

    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 CRLF

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • ron.abbott

    SSC Veteran

    Points: 234

    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: 995150

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • ron.abbott

    SSC Veteran

    Points: 234

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

  • Jeff Moden

    SSC Guru

    Points: 995150

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • ron.abbott

    SSC Veteran

    Points: 234

    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: 995150

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Jeff Moden

    SSC Guru

    Points: 995150

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • ron.abbott

    SSC Veteran

    Points: 234

    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: 995150

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • ron.abbott

    SSC Veteran

    Points: 234

    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 \ ?

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

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