bcp and white space padding

Last Post 25 May 2004 12:06 AM by satya. 8 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
bryanmurtha
New Member
New Member

--
24 May 2004 03:20 PM
Is there anyway to get a varchar out of bcp and put it into to a new table as a varchar. I get all of this white
space padding. I don't see any varchar datatype specified in bcp. Is this just something you have to live with when using bcp?
satya
New Member
New Member

--
25 May 2004 12:06 AM
Refer to books online for Format Files topic, which covers the issue.

HTH
satya
New Member
New Member

--
25 May 2004 12:06 AM
Refer to books online for Format Files topic, which covers the issue.

HTH
mimadon
New Member
New Member

--
25 May 2004 07:35 AM
If you use a fixed format input file, and the target column was originally created in a database with ANSI_PADDING set ON, SQL Server will not trim trailing blanks from the data going to a varchar() column. I believe this behavior was introduced in SQL 2000.

Microsoft recommends that you leave the ANSI_PADDING set ON. Here's their comment on the subject from BOL: "It is recommended that ANSI_PADDING always be set to ON. SET ANSI_PADDING must be ON when creating or manipulating indexes on computed columns or indexed views."

Assuming you're going to leave ANSI_PADDING set ON, you have at least a couple of options:

1) Create the input file with column delimiters so that the varchar() column data in each row of the input file is only as long as the actual varchar() column data

2) If you're stuck with a fixed format input file, you'll have to update the table after the BCP IN to remove any trailing blanks from the varchar() columns. You can use something like the following to do it:

UPDATE tablename
SET varchar_column_name = RTRIM(varchar_column_name)
WHERE RIGHT(varchar_column_name,1) = ' '

Hope that helps....
bryanmurtha
New Member
New Member

--
25 May 2004 09:27 AM
That did help a lot. So reading between the lines I can just turn ANSI Padding OFF yeah? Or is that a really bad idea.
mimadon
New Member
New Member

--
25 May 2004 10:19 AM
My (limited) understanding is that the behavior of the varchar() column in this case is based on the ANSI_PADDING setting at the time the table was created (or the column added to an existing table).

If that is the case, then you'll have to drop and recreate the table (with the desired setting in place) in order to change the behavior of the varchar() column.

Hopefully if I'm off-base with this explanation, someone will weigh in with better information....
mimadon
New Member
New Member

--
25 May 2004 10:53 AM
I was mistaken...

If the table is created with ANSI_PADDING OFF, then padding never occurs regardless of the ANSI_PADDING setting at the time the data is BULK INSERTED.

If the table is created with ANSI_PADDING ON, then the ANSI_PADDING setting at the time of the BULK INSERT is honored. In other words, if ANSI_PADDING is OFF when the BULK INSERT is executed, no padding occurs, and if the ANSI_PADDING is ON when the BULK INSERT is executed, padding occurs.

I confirmed this behavior using the BULK INSERT statement (rather than BCP.EXE) since it was easy to explicitly set ANSI_PADDING as desired in the T-SQL batch doing the BULK INSERT.

I was unable to find how to change the ANSI_PADDING setting when using BCP.EXE to import the data...
Llewy
New Member
New Member

--
16 Jul 2004 03:12 AM
I have a similiar problem. My input file is fixed width columns with no delimiter. I have tried setting the ANSI_PADDING off before running the BULK INSERT statement but the results are the same - trailing whitespace in my varchar fields. Can anyone point me to a sample on how to do this? My setup is SQL 2000 SP3a / Win 2000 Server.

Thanks in advance
Llewy
New Member
New Member

--
19 Jul 2004 06:23 AM
Yep. I've also tried delimiting the file to see if that helps. To no avail. Have resorted to trimming the fields after insert.
You are not authorized to post a reply.

Acceptable Use Policy