Help with BCP format error

Question

Help with BCP format error

MountainBig

SSC Veteran

Points: 220
More actions
September 10, 2009 at 2:33 pm

#210632

Hi all,
I am trying to use BCP script to load data from a TXT file(Pipe deliminated) imported through FTP site. when i try to load the file that i recived from the FTP site i get the error "Error = [Microsoft][SQL Native Client]String data, right truncation".
however when i open the same file in Wordpad and then save it as TXT it loads the file without issues so i belive its a format error in my BCP line that needs to be corrected?
my BCP script is as follows.
bcp "[server].[dbo].
" in "d:\folder\folder\file.txt" -F 1 -c -t "|" -r -C RAW -h "TABLOCK" -T
my columns in the txt file is seperated by a "|" and the row is seperated by something that looks like a small square
i am trying to automate this process so that i do not have to open the file everytime to save it in the right format.
any help would be much appreciated.
regards,
J

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

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

Jeff Moden SSC Guru Points: 1004591 More actions · Answer 1

I'm not anywhere near an SQL Server for about the next 4-5 days so I can't provide tested code. However, I can tell you what the problem is... BCP defaults to a 2 character end of line marker (Carriage Return/Line Feed or /r/n in BCP-eese). When you run the file through WordPad, it converts all the single character end of line markers (usually just a Line Feed, also known as the "New Line" character or /n in BCP-eese) to the two character version.

To keep from having to run it through WordPad, you need to tell BCP to use just the single character version. One way to do that is to copy the little square you see and paste it into the BCP command line for the -r paramter (encapsulated in quotes, of course). Another way is to assign -r the value of /n without quotes (which sometimes doesn't work).

Of course, if neither of those methods work, it may not be a Line Feed character and you'll need to find out what that little square character actually is. You can to that as follows in Query Analyzer...

SELECT ASCII('put the little square here')

Then, we'll need to warp the BCP command into a little bit of dynamic sql. Of course, we can avoid a trip through xp_CmdShell by using BULK INSERT instead. It has the same switches for delimiter and End of Line markers and, under the right conditions, can actually beat BCP for performance the way you're using BCP right now.

The advantage of BCP over BULK INSERT in SQL Server 2000 is that BCP can be set to be error tolerant as well as saving any bad lines of data to an "errata" file for possible rework and reimport.

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

MountainBig SSC Veteran Points: 220 More actions · Answer 2

Thank you for taking the time to answer my question. i tried both the approach for the BCP line command modifications as you mentioned by adding the square symbol in double quotes and adding the /n in the line however there was no effect however when i tried the Bulk insert it worked like a charm.

BULK INSERT Epny_Downloadactivity

FROM 'D:\folder\folder\file.txt'

WITH

(

MAXERRORS = 0,

FIELDTERMINATOR = '|',

ROWTERMINATOR = '

'

)

so my question is there a way to add this SQL statement to my BCP file or do i need to create a SQL Stored Procedure for this?

if i can use this in the BCP executable file could you assist in the format for the addition because when i add the above in the BCP file it does not do the trick unless i execute in sql server?

apprreciate all your help.

regards,

J

Jeff Moden SSC Guru Points: 1004591 More actions · Answer 3

Sorry for the delay.

You really need to find out what the character number of the little square box is because, at this point, we don't know if it's a Cr or Lf character. See the note about the ASCII function I used in my previous post.

If it's a Cr, that will be ASCII character 13 which is Hex 0x0D.

If it's a Lf, that will be ASCII character 10 which is Hex 0x0A.

If it's something else, we'll make the translation when we find out what it is.

The -r parameter can use the Hex value. For example, if we think the square character is ASCII 10, then the -r parameter can look like the following...

-r0x0A

... or ...

-r"0x0A"

... in the BCP command line.

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

MountainBig SSC Veteran Points: 220 More actions · Answer 4

Its number 10 (lf) character as i used the statement you provided and got the character number.

MountainBig SSC Veteran Points: 220 More actions · Answer 5

I tried using all the variations of -r "/n" -r/n or -r"square" -r"0x0D" -r0x0D with spaces without spaces but still 0 rows copied. maybe the bulk insert might be better option at this point than BCP command line? can Bulk Insert be included in the BCP executable file? could you help with how i would format the actual lines as shown above in my 2 previous post.

again appricate all your help.

regards,

J

Jeff Moden SSC Guru Points: 1004591 More actions · Answer 6

MountainBig (9/11/2009)
I tried using all the variations of -r "/n" -r/n or -r"square" -r"0x0D" -r0x0D with spaces without spaces but still 0 rows copied. maybe the bulk insert might be better option at this point than BCP command line? can Bulk Insert be included in the BCP executable file? could you help with how i would format the actual lines as shown above in my 2 previous post.
again appricate all your help.
regards,
J

If it's character 10 (Lf), as you say, then 0X0D is incorrect because that's Hex for "13" or Cr. You need to use 0X0A, instead.

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

Jeff Moden SSC Guru Points: 1004591 More actions · Answer 7

Ummm.... are you all set now?

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

bernardash SSC Rookie Points: 27 More actions · Answer 8

Hi Jeff,

I was having the same problem as MountainBig. I was sweating bullets that this was going to be another one of those late night needle in a haystack situations until I ran into your post. You saved me from a major headache. Most importantly, you allowed me to get my work done so that I can spend some time with my family during the holiday. Your answers are accurate and concise. You are a real diamond in the rough. I'm extremely thankful for your assistance.

Thank you sir !

Jeff Moden SSC Guru Points: 1004591 More actions · Answer 9

THAT, good Sir, is some of the best feedback I've ever gotten... glad to help especially since it means more time with family for you. And, welcome aboard!

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

bernardash SSC Rookie Points: 27 More actions · Answer 10

I was forced to use a format file b/c I need to skip one of the table columns. Here is the format file :

9.0

17

1 SQLCHAR 0 100 "|" 1 Year ""

2 SQLCHAR 0 100 "|" 2 Scenario ""

3 SQLCHAR 0 100 "|" 3 Version ""

4 SQLCHAR 0 100 "|" 4 Entity ""

5 SQLCHAR 0 100 "|" 5 Account ""

6 SQLCHAR 0 100 "|" 6 Nov ""

7 SQLCHAR 0 100 "|" 7 Dec ""

8 SQLCHAR 0 100 "|" 8 Jan ""

9 SQLCHAR 0 100 "|" 9 Feb ""

10 SQLCHAR 0 100 "|" 10 Mar ""

11 SQLCHAR 0 100 "|" 11 Apr ""

12 SQLCHAR 0 100 "|" 12 May ""

13 SQLCHAR 0 100 "|" 13 Jun ""

14 SQLCHAR 0 100 "|" 14 Jul ""

15 SQLCHAR 0 100 "|" 15 Aug ""

16 SQLCHAR 0 100 "|" 16 Sep ""

17 SQLCHAR 0 100 "0x0A" 17 Oct ""

Now, I'm getting the same error I was earlier :

#@ Row 1, Column 17: String data, right truncation @#

It's seeing my whole data file as one long record b/c it's failing to pick up the character that indicates the end of the record.

When I pass this command in my batch file, everything works :

bcp.exe JDE_Export.dbo.JDE_Export in PRK_ALL_BudgetExport.txt -c -t "|" -r"0x0A" -S ****** -e errors.err -U ****** -P ****** -F "2"

The table now has 18 columns but the data file only 17 columns. All I need to do is skip the last column.

a) Is there an easier way to skip a column

or

b) I have to get this darn format file to work and understand the character that indicates the end of each record.

Thanks.

bernardash SSC Rookie Points: 27 More actions · Answer 11

Sometimes the hardest questions to answer have the simplest solutions....

I copied the line terminator from Essbase's output file and pasted it into the format file in place of 0x0A... Voila !

I didn't think to try this approach this time b/c when I tried it a few days ago to get it to work in the middle of the parameters of my command in a batch file (bcp.exe JDE_Export.dbo.JDE_Export in PRK_ALL_BudgetExport.txt -c -t "|" -r"0x0A" -S ****** -e errors.err -U ****** -P ****** -F "2"), pasting the little rectangular into the batch file didn't work.

anyway, it looks like I'm back in business again.

Jeff Moden SSC Guru Points: 1004591 More actions · Answer 12

bernardash (12/3/2009)
I was forced to use a format file b/c I need to skip one of the table columns. Here is the format file :
...
The table now has 18 columns but the data file only 17 columns. All I need to do is skip the last column.
a) Is there an easier way to skip a column

Heh... like you said...

"Sometimes the hardest questions to answer have the simplest solutions...."

Create a view of the table in the same column order as the data file and leave out any columns that you need to skip. Then, just use BULK INSERT using the view as the target.

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

Help with BCP format error

Cookies on SQLServerCentral