BCP via batch file - Error = [Microsoft][SQL Native Client]Unexpected EOF encountered in BCP data-file

  • rob_nye

    SSC-Addicted

    Points: 494

    Hi,

    I have a csv file - output from a sybase database which is column delimited with a ','

    whne I try to import this into a sqlserver 2005 database using BCP via a batch file using a .fmt file I get the above error

    for %%i in (D:\AppSupp\files_2b_sqlloaded.txt) do (
        bcp Ideal_Risk.dbo.Sybase_Disk_space in %%i -t , -f D:\AppSupp\Sybase_stats.fmt -L 1 -e d:\AppSupp\Error.log -m 2 -T >D:\AppSupp\import.log
        )

    initially I thought it was due to CRLF as the last line, I have now removed this (via another script)
    still it errored - so I manually removed white space from the csv file - still fails

    9.0
    8
    1   SQLCHAR   0   50  ","  1  Database_Name       Latin1_General_CI_AS
    2   SQLCHAR   0   18  ","  2  Data_Size_MB       ""
    3   SQLCHAR   0   18  ","  3  Used_Data_MB       ""
    4   SQLCHAR   0   18  ","  4  Data_Full_Pcnt       ""
    5   SQLCHAR   0   18  ","  5  Log_Size_MB        ""
    6   SQLCHAR   0   18  ","  6  Free_Log_MB        ""
    7   SQLCHAR   0   18  ","  7  Log_Full_Pcnt       ""
    8   SQLCHAR   0   19  "\r\n" 8  Snap_shot         ""

    data example rows

    web_tempdb    , 512.00   , 8.79   , 1.72   , 512.00  , 508.25  , 0.73   ,   Jan 31 2017 11:41AM
    sep_tempdb    , 1024.00  , 10.01   , 0.98   , 512.00  , 510.00  , 0.39   ,   Jan 31 2017 11:41AM
    sybsecurity    , 1536.00  , 1027.71  , 66.91  , 128.00  , 127.50  , 0.39   ,   Jan 31 2017 11:41AM
    sa_tempdb    , 4096.00  , 21.55   , 0.53   , 512.00  , 510.00  , 0.39   ,   Jan 31 2017 11:41AM
    master     , 128.00   , 15.90   , 12.42  , NULL   , 112.10  , NULL   ,   Jan 31 2017 11:41AM
    sybsystemprocs   , 256.00   , 127.85   , 49.94  , NULL   , 128.15  , NULL   ,   Jan 31 2017 11:41AM
    IDEAL      , 118784.00  , 95438.59  , 80.35  , 1024.00  , 996.25  , 2.71   ,   Jan 31 2017 11:41AM
    dbccdb     , 2048.00  , 1813.31  , 88.54  , 128.00  , 127.50  , 0.39   ,   Jan 31 2017 11:41AM
    user_tempdb    , 512.00   , 7.55   , 1.48   , 512.00  , 510.00  , 0.39   ,   Jan 31 2017 11:41AM
    sybsystemdb    , 13.00   , 1.84   , 14.15  , NULL   , 11.16   , NULL   ,   Jan 31 2017 11:41AM
    sybmgmtdb    , 160.00   , 23.72   , 14.83  , 10.00   , 9.96   , 0.41   ,   Jan 31 2017 11:41AM
    if_tempdb    , 512.00   , 9.62   , 1.88   , 512.00  , 503.98  , 1.57   ,   Jan 31 2017 11:41AM
    tempdb     , 2052.00  , 40.00   , 1.95   , 1152.00  , 1145.22  , 0.59   ,   Jan 31 2017 11:41AM
    model      , 153.00   , 6.15   , 4.02   , NULL   , 146.85  , NULL   ,   Jan 31 2017 11:41AM

    Im sure it is the data but I am stumped as to what is wrong

  • Ian Scarlett

    SSC-Insane

    Points: 23197

    In your format file, you are telling it to delimit column 2 onwards with a Tab character, but the sample data you posted is comma delimited.

  • rob_nye

    SSC-Addicted

    Points: 494

    doh... I had posted the wrong .fmt file.... now corrected

    I tried changing to tab to see if it made any difference..... but it didnt

  • rob_nye

    SSC-Addicted

    Points: 494

    turns out the problem was the date field on each row.. wasnt being recognised as datetime therefore would not import

    changed process to import into a temp table with the date field as varchar
    then did an insert from temp to base table converting the date field into a bona fide datetime field 🙂

  • Jeff Moden

    SSC Guru

    Points: 994682

    rob_nye - Thursday, February 23, 2017 2:11 AM

    turns out the problem was the date field on each row.. wasnt being recognised as datetime therefore would not import

    changed process to import into a temp table with the date field as varchar
    then did an insert from temp to base table converting the date field into a bona fide datetime field 🙂

    Importing into a staging table, like you did, is usually the best way to go because it allows you to pre-validate all data long before it hits your production table.  It also makes doing "upserts" a whole lot easier, if that's something that you need to do.

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

Viewing 5 posts - 1 through 5 (of 5 total)

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