Post reply

Struggling with bcp on a particular file...

  • May 19, 2017 at 8:32 am

    #336990

    I have a "few" files in text format.
    Fields are separated by 0x01 (Ctrl A?) characters while rows are delimited by a simple 0x0A
    When I try to load the file, I get no row loaded... nor error message
    My bcp command is something like this
    bcp MyDb.dbo.MyTable in 2016-06-01-short.txt -S MyServer -c -T -t 0x01 -r

    If I change my declaration for the carriage return, then I get errors
    bcp MyDb.dbo.MyTable in 2016-06-01-short.txt -S MyServer -c -T -t 0x01 -r 0x0A

    Starting copy...
    SQLState = 22005, NativeError = 0
    Error = [Microsoft][ODBC Driver 11 for SQL Server]Invalid character value for cast specification
    SQLState = 22005, NativeError = 0
    Error = [Microsoft][ODBC Driver 11 for SQL Server]Invalid character value for cast specification
    SQLState = 22005, NativeError = 0
    Error = [Microsoft][ODBC Driver 11 for SQL Server]Invalid character value for cast specification
    SQLState = 22005, NativeError = 0
    Error = [Microsoft][ODBC Driver 11 for SQL Server]Invalid character value for cast specification
    SQLState = 22005, NativeError = 0
    Error = [Microsoft][ODBC Driver 11 for SQL Server]Invalid character value for cast specification
    SQLState = 22005, NativeError = 0
    Error = [Microsoft][ODBC Driver 11 for SQL Server]Invalid character value for cast specification
    SQLState = 22005, NativeError = 0
    Error = [Microsoft][ODBC Driver 11 for SQL Server]Invalid character value for cast specification
    SQLState = 22005, NativeError = 0
    Error = [Microsoft][ODBC Driver 11 for SQL Server]Invalid character value for cast specification
    SQLState = 22005, NativeError = 0
    Error = [Microsoft][ODBC Driver 11 for SQL Server]Invalid character value for cast specification
    SQLState = 22005, NativeError = 0
    Error = [Microsoft][ODBC Driver 11 for SQL Server]Invalid character value for cast specification

    My table definition is
    CREATE TABLE dbo.MyTable(YearMonthDay     INT NOT NULL
                                         ,HourMinute         INT NOT NULL
                                         ,DeviceID          UNIQUEIDENTIFIER NULL
                                         ,Domain              VARCHAR(255) NOT NULL
                                         ,EventName         VARCHAR(255) NOT NULL
                                         ,StrippedURL     VARCHAR(8000) NOT NULL
                                         ,ExtractedValue    VARCHAR(MAX) NULL
                                         ,[Platform]         VARCHAR(255) NOT NULL
                                         ,City              VARCHAR(255) NULL
                                         ,Province          VARCHAR(255) NULL
                                         ,Postcode          VARCHAR(255) NULL
                                         ,Gender              VARCHAR(255) NULL
                                         ,AgeGroup          VARCHAR(255) NULL
                                         ,UnixTimeStamp    BIGINT NULL
                                         ,ISOCountryCode    VARCHAR(255) NULL
                                     );

  • May 19, 2017 at 8:42 am

    #1943326

    I should add that I get error "Unexpected EOF encountered in BCP data-file"

  • May 19, 2017 at 8:57 am

    #1943333

    Have you tried the -C options with RAW?

    -C { ACP | OEM | RAW | code_page }

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

  • May 19, 2017 at 9:01 am

    #1943337

    Had never heard of the RAW option.

    I'll give that a shot (on Monday!)

    Thanks

  • May 19, 2017 at 9:08 am

    #1943340

    Eric Mamet - Friday, May 19, 2017 9:01 AM

    Had never heard of the RAW option.

    I'll give that a shot (on Monday!)

    Thanks

    It might not make a difference, as it specifies the encoding of your content. Your error doesn't really reveal too much at this point. 
    Also read a similar case: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d31db86a-4b11-4b41-b64f-0179e64625c5/unexpected-eof-encountered-in-bcp-data-file?forum=sqltools
    Difficult to say at this point, not sure what your source data formatting is.

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

  • May 22, 2017 at 5:15 am

    #1943519

    Just in case it's useful to anyone...

    I managed to load my file using the following BCP parameters
    bcp MyTable in MyFile -S MyServer -c -T -t 0x01 -r 0x0A 

    The difficulty was to properly specify the Field and Row separators (0x01 is not common...)
    I also had to increase some column sizes but that's a relatively minor detail

    Thanks again for your help

    Eric

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

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