Post reply

Text Delimiter in BCP

  • October 13, 2018 at 7:58 am

    #379485

    HI Gurus,

    After lot of struggle finally am able to load data from file to MSSQL table using bcp command. Now am getting one more issue i would need your help on this.

    While loading bulk data file from file to target am getting below issue.

    #@ Row 2, Column 5: String data, right truncation @#

    I have done deep analysis on this error then finally found that it is due to column separator was present in data. For ex : 'John,Mark' our column separator is "," comma.

    In Linux, data is loading only with comma separator data into target , if am trying to use any other separator like $ or ~ or ^ are not working. I mean it was succeed with zero records no error reported in error logs..

    Is there any option to apply text delimiter in BCP , Bcp command should ignore if column sperator was present in Data ? Ex "John,Mark" ?

    I got couple of option from google but it doesn't hlep me , like -q and format file and -f "0xa0" -- I tried all the options.

    Any inputs ?

  • October 15, 2018 at 1:40 am

    #2009543

    Any inputs ?

  • October 15, 2018 at 2:05 am

    #2009545

    Sounds like you need to specify a different delimiter to a comma (,), as it can appear in your data. You can specific the delimiter by use -t. So you could use -t "|" to specify a pipe as the delimiter.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • October 15, 2018 at 4:34 am

    #2009554

    -T is trusted mode but our case it is not applicable because file was sitting in remote location. 

    -t "~" -- I already explained above nonstandard delimiter are not working except "," comma. I tired different delimiter s like "$$" ,~.^ but no luck.

    Can you please let me know how to specify text delimiter in bcp ?  -q is not working. Any other option to apply text delimiter ?

  • October 15, 2018 at 4:44 am

    #2009559

    SATYA R - Monday, October 15, 2018 4:34 AM

    -T is trusted mode but our case it is not applicable because file was sitting in remote location. 

    -t "~" -- I already explained above nonstandard delimiter are not working except "," comma. I tired different delimiter s like "$$" ,~.^ but no luck.

    Can you please let me know how to specify text delimiter in bcp ?  -q is not working. Any other option to apply text delimiter ?

    Must have still have held shift down, my bad.

    What do you mean "no luck". What happens instead then? As i said, -t is to specify the field delimiter. This is stated in the documentation: https://docs.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-2017.

    -t field_term
    Specifies the field terminator. The default is \t (tab character). Use this parameter to override the default field terminator. For more information, see Specify Field and Row Terminators (SQL Server).

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • October 15, 2018 at 7:58 am

    #2009612

    SATYA R - Monday, October 15, 2018 4:34 AM

    -T is trusted mode but our case it is not applicable because file was sitting in remote location. 

    -t "~" -- I already explained above nonstandard delimiter are not working except "," comma. I tired different delimiter s like "$$" ,~.^ but no luck.

    Can you please let me know how to specify text delimiter in bcp ?  -q is not working. Any other option to apply text delimiter ?

    Post your BCP statement because "~" is a valid delimiter as are a whole bunch of other characters.  It's what's in the data file that counts here.

    As for delimiters embedded in the data, when a field has such an embedded comma, is the field embedded in quotes?  Also, are you able to use PowerShell?

    And, keep in mind what it says in the article at the following link...
    https://docs.microsoft.com/en-us/sql/relational-databases/import-export/prepare-data-for-bulk-export-or-import-sql-server?view=sql-server-2017

    And, I quote...

    Comma-separated value (CSV) files are not supported by SQL Server bulk-import operations. However, in some cases, a CSV file can be used as the data file for a bulk import of data into SQL Server. Note that the field terminator of a CSV file does not have to be a comma. To be usable as a data file for bulk import, a CSV file must comply with the following restrictions:

    Data fields never contain the field terminator.

    Either none or all of the values in a data field are enclosed in quotation marks ("").

    That last part is the key and there's a wicked easy way to make that happen using Powershell and that's why I need to know if you can use it or not.  And, considering the mistakes you've made within the BCP command on previous posts, we really need to see your current attempt at this command.

    I also believe this would be a bit easier if you'd let SQL Server PULL the data from the source system instead of trying to PUSH the data.

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

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

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