BCP Help

  • Hello All

    I am running a BCP command to create a csv file from a SQL table, but when I am outputting fields which are empty, the text file has a space inserted instead.

    I need my file to look like this:

    "test","","","data"

    instead of:

    "test"," "," ","data"

    Could anyone point me in the right direction

    Thanks

    Bicky1980

  • Are you sure the source table is NULL and not a space?

  • Thanks for the reply, its not actually null its an empty string...

  • Anybody able to help??

  • Please post your BCP command, as well as your form if you are using one.

    Steve Jimmo
    Sr DBA
    “If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

  • BCP Command as follows:

    declare @bcpcommand nvarchar(1000)

    SET @bcpcommand = 'bcp "select CHAR(34)+urn,title,firstname,lastname,company,ad1,ad2,ad3,ad4,ad5,postcode,

    phone1,phone2,mobile,email,dateofbirth,source,sourceapp,cpsagent,cpsreference,cpspartner,reactiv_bdm,

    reactiv_qmq_rdate,job_number_1,job_number_2,job_number_3,job_number_4,job_number_5,job_number_6,

    job_number_7,job_number_8,job_number_9,job_number_10,job_number_11,job_number_12,job_number_13,

    job_number_14,job_number_15,job_number_16,job_number_17,job_number_18,job_number_19,job_number_20+char(34)

    from ASHLEY.DBO.outputtable " queryout "c:\outputs\reactive_med_'+replace(CONVERT(VARCHAR(10), GETDATE(), 104),'.','')+'.csv" -f c:\outputs\emailvision.fmt -T -k'

    EXEC master..xp_cmdshell @bcpCommand

    Also Format file as follows:

    9.0

    43

    1 SQLCHAR 0 10 "\",\"" 1 URN Latin1_General_CI_AS

    2 SQLCHAR 0 50 "\",\"" 2 TITLE Latin1_General_CI_AS

    3 SQLCHAR 0 50 "\",\"" 3 FIRSTNAME Latin1_General_CI_AS

    4 SQLCHAR 0 50 "\",\"" 4 LASTNAME Latin1_General_CI_AS

    5 SQLCHAR 0 1 "\",\"" 5 COMPANY Latin1_General_CI_AS

    6 SQLCHAR 0 50 "\",\"" 6 AD1 Latin1_General_CI_AS

    7 SQLCHAR 0 50 "\",\"" 7 AD2 Latin1_General_CI_AS

    8 SQLCHAR 0 50 "\",\"" 8 AD3 Latin1_General_CI_AS

    9 SQLCHAR 0 50 "\",\"" 9 AD4 Latin1_General_CI_AS

    10 SQLCHAR 0 50 "\",\"" 10 AD5 Latin1_General_CI_AS

    11 SQLCHAR 0 50 "\",\"" 11 POSTCODE Latin1_General_CI_AS

    12 SQLCHAR 0 50 "\",\"" 12 PHONE1 Latin1_General_CI_AS

    13 SQLCHAR 0 50 "\",\"" 13 PHONE2 Latin1_General_CI_AS

    14 SQLCHAR 0 50 "\",\"" 14 MOBILE Latin1_General_CI_AS

    15 SQLCHAR 0 150 "\",\"" 15 EMAIL Latin1_General_CI_AS

    16 SQLCHAR 0 50 "\",\"" 16 DATEOFBIRTH Latin1_General_CI_AS

    17 SQLCHAR 0 3 "\",\"" 17 SOURCE Latin1_General_CI_AS

    18 SQLCHAR 0 1 "\",\"" 18 SOURCEAPP Latin1_General_CI_AS

    19 SQLCHAR 0 50 "\",\"" 19 CPSAGENT Latin1_General_CI_AS

    20 SQLCHAR 0 10 "\",\"" 20 CPSREFERENCE Latin1_General_CI_AS

    21 SQLCHAR 0 50 "\",\"" 21 CPSPARTNER Latin1_General_CI_AS

    22 SQLCHAR 0 1 "\",\"" 22 BDM Latin1_General_CI_AS

    23 SQLCHAR 0 1 "\",\"" 23 RDATE Latin1_General_CI_AS

    24 SQLCHAR 0 1 "\",\"" 24 JOB_NUMBER_1 Latin1_General_CI_AS

    25 SQLCHAR 0 1 "\",\"" 25 JOB_NUMBER_2 Latin1_General_CI_AS

    26 SQLCHAR 0 1 "\",\"" 26 JOB_NUMBER_3 Latin1_General_CI_AS

    27 SQLCHAR 0 1 "\",\"" 27 JOB_NUMBER_4 Latin1_General_CI_AS

    28 SQLCHAR 0 1 "\",\"" 28 JOB_NUMBER_5 Latin1_General_CI_AS

    29 SQLCHAR 0 1 "\",\"" 29 JOB_NUMBER_6 Latin1_General_CI_AS

    30 SQLCHAR 0 1 "\",\"" 30 JOB_NUMBER_7 Latin1_General_CI_AS

    31 SQLCHAR 0 1 "\",\"" 31 JOB_NUMBER_8 Latin1_General_CI_AS

    32 SQLCHAR 0 1 "\",\"" 32 JOB_NUMBER_9 Latin1_General_CI_AS

    33 SQLCHAR 0 1 "\",\"" 33 JOB_NUMBER_10 Latin1_General_CI_AS

    34 SQLCHAR 0 1 "\",\"" 34 JOB_NUMBER_11 Latin1_General_CI_AS

    35 SQLCHAR 0 1 "\",\"" 35 JOB_NUMBER_12 Latin1_General_CI_AS

    36 SQLCHAR 0 1 "\",\"" 36 JOB_NUMBER_13 Latin1_General_CI_AS

    37 SQLCHAR 0 1 "\",\"" 37 JOB_NUMBER_14 Latin1_General_CI_AS

    38 SQLCHAR 0 1 "\",\"" 38 JOB_NUMBER_15 Latin1_General_CI_AS

    39 SQLCHAR 0 1 "\",\"" 39 JOB_NUMBER_16 Latin1_General_CI_AS

    40 SQLCHAR 0 1 "\",\"" 40 JOB_NUMBER_17 Latin1_General_CI_AS

    41 SQLCHAR 0 1 "\",\"" 41 JOB_NUMBER_18 Latin1_General_CI_AS

    42 SQLCHAR 0 1 "\",\"" 42 JOB_NUMBER_19 Latin1_General_CI_AS

    43 SQLCHAR 0 1 "\r" 43 JOB_NUMBER_20 Latin1_General_CI_AS

    \r

    Thanks

    Ash

  • If possible fix the source table with an update statement that replaces the empty strings with NULL.

    UPDATE MyTable

    SET MyField = NULL

    WHERE MyField = ''

    You could use REPLACE in your Select statement

    SELECT REPLACE(MyField,'',NULL)

    Or a CASE statement

    SELECT

    CASE MyField WHEN '' THEN NULL ELSE MyField END

  • Fantasic Chrissy321, works a treat, decided to update the source table prior to outputting via BCP!

    Problem solved! 😀

  • oh dear, 1 new problem now...This works fine, however on my last field which needed to have quote qualifiers not only has the 1st quote e.g. "hello. The Header records (which I have inserted as the first record) has the trailing quote. So I am guessing this is because the value isnt a null vale...

    Any idea how to sort this?

  • bicky1980 (11/12/2010)


    oh dear, 1 new problem now...This works fine, however on my last field which needed to have quote qualifiers not only has the 1st quote e.g. "hello. The Header records (which I have inserted as the first record) has the trailing quote. So I am guessing this is because the value isnt a null vale...

    Any idea how to sort this?

    Literally sort the data or sort it out, that is solve the problem.

    Can you show some sample data illustrating the problem?

    If you are text qualifying the fields which something contained within the fields then you should either change your quailifier or do the UPDATE/REPLACE/CASE thing to clean your data.

  • Literally sort the data or sort it out, that is solve the problem.

    What?

    The data has been output like this:

    "Hello","This","Is","What","I","Mean

    I need this to look like this:

    "Hello","This","Is","What","I","Mean"

    The format file is as above, the bcp is as above.

    After the previous issue, which sorted the space issue, I now have the last value in my SQL table as a null value. This I am guessing is why the last quote (specified in bcp as +char(34)) is not being output.

    I hope this makes my problem clearer...

    Thanks

    Bicky

  • When you concatenate 2 values and one is a null you will always get null so job_number_20+char(34)

    will always be null if job_number_20 is null

    Try this instead of job_number_20+char(34)

    CASE job_number_20 WHEN = NULL THEN char(34) ELSE job_number_20+char(34) END

    or

    COALESCE(job_number_20+char(34),char(34))

  • Fantastic, used COALESCE(job_number_20+char(34),char(34)) and this has rectified the problem thanks for the help 😀

Viewing 13 posts - 1 through 12 (of 12 total)

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