Post reply

Trimming whitespace during BCP

  • April 26, 2005 at 10:06 pm

    #87305

    Hi,

     

    I am working on a project whereby occassionally the client may provide a CSV file where a field has whitespace before the terminating ,.

    IE: - Example CSV file

       Field1,Field2      ,Field3

    I would like to know how to remove and trim the whitespace using BCP. I have seen this can be done with DTS but how do you it in BCP

    This is the section of BCP code form my stored procedure:

     SET @vcharDbName = 'CTC'

      SET @vcharTbName = 'ctcTempAdditional'

      SET @charSep = ','

      SET @vcharCmd = 'bcp.exe ' + 

          @vcharDbName + '..' + @vcharTbName +  ' in '  +

          @vcharFilename + ' -F1 -c -q -C1252' + ' -t' + @charSep

     Is there a simple option I can set or maybe at a database level

    Kind Regards,

    Andrew

     

     

  • April 27, 2005 at 8:11 am

    #555223

    G'day,

    I usually just follow the BCP statement with an update statement, similar to the following:

    UPDATE ctcTempAdditional SET ColName2 = LTRIM(RTRIM(ColName2))

    There is a time and memory cost that depends on the number of rows and the size of your server.  On a dual CPU box with a couple of gig of memory, this sort of update takes up to a minute for 40 million rows.

    Works for me.  Your results may vary.  Hope this helps.

    Wayne

  • April 27, 2005 at 5:27 pm

    #555412

    Thanks Wayne

    Thats what I will do. I was hopping that SQL Server had an option on the BCP import but this will work fine as I have a low record count in the DB sub 500K records

Viewing 3 posts - 1 through 2 (of 2 total)

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