locked
bcp utility for IN command : file with string delimiter " RRS feed

  • Question

  • Hi ,

    I am facing issue with bcp IN command when file is having string delimiter as " .

    file data  example : "-961000","USD","-1000000"

    when I used -q below error is given 

    SQLState = 22005, NativeError = 0
    Error = [Microsoft][SQL Server Native Client 11.0]Invalid character value for cast specification.

    please help

    Wednesday, April 4, 2018 1:23 PM

Answers

  • A format file for this data would look like:

    9.0
    4
    1 SQLCHAR 0 0 "\""     0  ""  ""
    2 SQLCHAR 0 0 "\"^\""  1 id   Latin1_General_CI_AS
    3 SQLCHAR 0 0 "\"^\""  2 name Latin1_General_CI_AS
    4 SQLCHAR 0 0 "\"\r\n" 3 abc  Latin1_General_CI_AS

    There is one extra field in the description to skip the initial quote.

    Note that you need the option -F 2 to skip the header.

    • Marked as answer by Fabcoder Monday, April 16, 2018 8:24 AM
    Monday, April 16, 2018 7:19 AM

  • "ID"^"NAME"^"DATE"
    "87889892"^"ABC"^"2017-07-06"

    that may be because you're not excluding the " in the beginning

    you should start with

    11.0
    105
    1       SQLCHAR             0       0      "\""        0     x                                             ""
    2       SQLCHAR             0       12      "^\""        1     T_ID                                             ""


    to escape the first "

    for example for the data you posted

    it will look like below

    bcp statement used is

    bcp DBName.dbo.TableName IN "<file path>\testbcp.txt" -F2 -f "<format file path>\bcpfmt.fmt" -T


    table created is like

    create table tablename
    
    (
    id int,
    val varchar(100),
    date datetime
    )

    and format file used is this

    12.0  
    4 
    1	SQLCHAR	      0        0       "\""             0     x 	       SQL_Latin1_General_CP1_CI_AS	
    2       SQLCHAR       0        0       "\"^\""     	1     ID 		""  
    3       SQLCHAR       0        0       "\"^\""     	2     Val              SQL_Latin1_General_CP1_CI_AS  
    4       SQLCHAR       0        0      "\""     		3     date		""  


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page



    • Edited by Visakh16MVP Monday, April 16, 2018 8:14 AM
    • Marked as answer by Fabcoder Monday, April 16, 2018 8:23 AM
    Monday, April 16, 2018 7:32 AM

All replies

  • Wednesday, April 4, 2018 1:35 PM
  • What is the datatype of your table column? 

    If you've text qualifier specify it in bcp format file

    see

    https://social.technet.microsoft.com/wiki/contents/articles/30178.parsing-data-from-a-flat-file-with-inconsistent-delimiters-in-sql-server.aspx#Files_With_Text_Qualifiers

    http://www.nigelrivett.net/SQLTsql/BCP_quoted_CSV_Format_file.html


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, April 4, 2018 1:47 PM
  • The -q switch refers to the setting QUOTED_IDENTIFIER, and would matter if you use the queryout option. It has no relation to the data.

    I have an article on my web site about BCP. It includes examples on how to use format files to handles files like this:
    http://www.sommarskog.se/bulkload.html

    Wednesday, April 4, 2018 10:02 PM
  • Thank you .

    I tried using format file now the error as below 

    System.Exception: Bcp run  exited with code 1, Details: Starting copy...
    SQLState = 22005, NativeError = 0
    Error = [Microsoft][SQL Server Native Client 11.0]Invalid character value for cast specification

    Format file looks like 

    11.0
    105
    1       SQLCHAR             0       12      "^\""        1     T_ID                                             ""


    • Edited by Fabcoder Monday, April 16, 2018 6:43 AM
    Monday, April 16, 2018 6:40 AM
  • Thank you .

    I tried using format file now the error as below 

    System.Exception: Bcp run  exited with code 1, Details: Starting copy...
    SQLState = 22005, NativeError = 0
    Error = [Microsoft][SQL Server Native Client 11.0]Invalid character value for cast specification

    Format file looks like 

    11.0
    105
    1       SQLCHAR             0       12      "^\""        1     T_ID                                             ""


    Can you show some sample data you're trying to import?

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, April 16, 2018 7:03 AM

  • "ID"^"NAME"^"DATE"
    "87889892"^"ABC"^"2017-07-06"
    Monday, April 16, 2018 7:11 AM
  • A format file for this data would look like:

    9.0
    4
    1 SQLCHAR 0 0 "\""     0  ""  ""
    2 SQLCHAR 0 0 "\"^\""  1 id   Latin1_General_CI_AS
    3 SQLCHAR 0 0 "\"^\""  2 name Latin1_General_CI_AS
    4 SQLCHAR 0 0 "\"\r\n" 3 abc  Latin1_General_CI_AS

    There is one extra field in the description to skip the initial quote.

    Note that you need the option -F 2 to skip the header.

    • Marked as answer by Fabcoder Monday, April 16, 2018 8:24 AM
    Monday, April 16, 2018 7:19 AM

  • "ID"^"NAME"^"DATE"
    "87889892"^"ABC"^"2017-07-06"

    that may be because you're not excluding the " in the beginning

    you should start with

    11.0
    105
    1       SQLCHAR             0       0      "\""        0     x                                             ""
    2       SQLCHAR             0       12      "^\""        1     T_ID                                             ""


    to escape the first "

    for example for the data you posted

    it will look like below

    bcp statement used is

    bcp DBName.dbo.TableName IN "<file path>\testbcp.txt" -F2 -f "<format file path>\bcpfmt.fmt" -T


    table created is like

    create table tablename
    
    (
    id int,
    val varchar(100),
    date datetime
    )

    and format file used is this

    12.0  
    4 
    1	SQLCHAR	      0        0       "\""             0     x 	       SQL_Latin1_General_CP1_CI_AS	
    2       SQLCHAR       0        0       "\"^\""     	1     ID 		""  
    3       SQLCHAR       0        0       "\"^\""     	2     Val              SQL_Latin1_General_CP1_CI_AS  
    4       SQLCHAR       0        0      "\""     		3     date		""  


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page



    • Edited by Visakh16MVP Monday, April 16, 2018 8:14 AM
    • Marked as answer by Fabcoder Monday, April 16, 2018 8:23 AM
    Monday, April 16, 2018 7:32 AM
  • Cool Thanks . Working successfully.

    Is there any way I can generate the format file automatically without manual addition ?

    9.0
    4
    1 SQLCHAR 0 0 "\""     0  ""  ""
    2 SQLCHAR 0 0 "\"^\""  1 id   Latin1_General_CI_AS
    3 SQLCHAR 0 0 "\"^\""  2 name Latin1_General_CI_AS
    4 SQLCHAR 0 0 "\"\r\n" 3 abc  Latin1_General_CI_AS

    Monday, April 16, 2018 8:22 AM
  • Is there any way I can generate the format file automatically without manual addition ?

    Not really. You can say things like:

    bcp somedb.dbo.mytbl format nul -f mytbl.fmt -c -t "^" -T -S server

    This will generate a format file for from the table definition, but you would need to modify it manually afterwards t get the delimiters correct, and also add that extra dummy column in the beginning. (A further complication is that the ^ may have a special meaning to the command-line shell, which also may do interesting things with the double quotes.)

    As I said previously, there is an article on my web site that discusses the BCP and BULK INSERT in depth. Reading this article, you can learn what you can do with a format file - and what you cannot do.
    http://www.sommarskog.se/bulkload.html

    Monday, April 16, 2018 10:50 AM
  • Cool Thanks . Working successfully.

    Is there any way I can generate the format file automatically without manual addition ?

    9.0
    4
    1 SQLCHAR 0 0 "\""     0  ""  ""
    2 SQLCHAR 0 0 "\"^\""  1 id   Latin1_General_CI_AS
    3 SQLCHAR 0 0 "\"^\""  2 name Latin1_General_CI_AS
    4 SQLCHAR 0 0 "\"\r\n" 3 abc  Latin1_General_CI_AS

    You can generate the base version based on your table structure

    see

    https://visakhm.blogspot.ae/2013/10/generate-format-files-based-on-table.html

    But would need tweaking it for matching your files delimiter etc format if its not a standard file


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, April 16, 2018 10:55 AM