BULK INSERT a file produced from a UNIX machine - SQL 2005

Question

BULK INSERT a file produced from a UNIX machine - SQL 2005

BravehearT1326

SSChasing Mays

Points: 616
More actions
August 26, 2010 at 4:01 am

#225315

Hi there
I am attempting to import a UNIX generated file using SQL Server Bulk Insert command and am getting nowhere fast. The issue appears to be related to the end-of-line terminator that has been used.
Having viewed the file in a HEX editor it shows the end-of-line character as being HEX 0a, or in Decimal as 10.
The actual SQL error received is: Msg 4863, Level 16, State 1, Line 1 Bulk load data conversion error (truncation) for row 1, column 3 (LASTUPDATE).
Viewing the file in Windows Notepad shows that there is a character (shown as a rectangle) at the end of the last character in the 3rd field with and the next line continuing straight after.
However when viewing in WORDPAD it shows as if there is a carriage return present (Not shown in posting here): CHK|CHECKING|08-24-2010 SAV|SAVINGS|08-24-2010 UNK|UNKNOWN|08-24-2010
I have tried several different ROWTERMINATOR values to try and get this working including:
BULK INSERT TEST.dbo.NA_BANKACCTTYPELOOKUP
FROM 'C:\TEST\test.txt'
WITH
(
FIELDTERMINATOR ='|',
ROWTERMINATOR = '+CHAR(10)+'
)
but still the import fails.
Anyone got any ideas how to get this file loaded - its driving me NUTS!:hehe:

Viewing 11 posts - 1 through 10 (of 10 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

for files originating from unix, i've always used ROWTERMINATOR = '\r' ({slash r} if the forum doesn't like to print it),

otherwise regualr PC files get {slash n}

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

BravehearT1326 SSChasing Mays Points: 616 More actions · Answer 2

Thanks for the reply.

Tried running

BULK INSERT TEST.dbo.NA_BANKACCTTYPELOOKUP

FROM 'C:\TEST\WXBankAcctType.txt'

WITH

(

FIELDTERMINATOR ='|',

ROWTERMINATOR = '\r'

)

still getting message:

Msg 4863, Level 16, State 1, Line 1

Bulk load data conversion error (truncation) for row 1, column 3 (LASTUPDATE).

I have uploaded the sample file I am using for reference. Had to change the extension to .txt to upload and amended code above to show this.

steve-893342 SSCarpal Tunnel Points: 4439 More actions · Answer 3

Providing your NA_BANKACCTTYPELOOKUP table has 7 columns, all wide enough to accommodate the data, then this should work

BULK INSERT TEST.dbo.NA_BANKACCTTYPELOOKUP

FROM 'C:\TEST\WXBankAcctType.txt'

WITH

(

FIELDTERMINATOR = '|'

)

Nevyn SSChampion Points: 14210 More actions · Answer 4

Try running ux2dos on the file (on unix) before trying to load it ...

But based on the error it might not be a unix file issue at all. I assume lastupdate is a date. What format is it in? Are you copying it into a date column?

matzer SSCrazy Eights Points: 9727 More actions · Answer 5

BravehearT1326 (8/26/2010)
BULK INSERT TEST.dbo.NA_BANKACCTTYPELOOKUP
FROM 'C:\TEST\test.txt'
WITH
(
FIELDTERMINATOR ='|',
ROWTERMINATOR = '+CHAR(10)+'
)

You were very close with that '+CHAR(10)+'. However you need to use dynamic sql to use a linefeed ( or 0x0A) as the row terminator. According to BOL, a carriage return (\r or 0x0D) is prepended if a linefeed is used as the terminator. I used an example from BOL to get around this and it seems to work:

DECLARE @bulk_cmd varchar(1000)

SET @bulk_cmd = 'BULK INSERT TEST.dbo.NA_BANKACCTTYPELOOKUP

FROM ''C:\TEST\WXBankAcctType.txt''

WITH (FIELDTERMINATOR = ''|'',

ROWTERMINATOR = '''+CHAR(10)+''')'

EXEC(@bulk_cmd)

Peter

matzer SSCrazy Eights Points: 9727 More actions · Answer 6

matzer

SSCrazy Eights

Points: 9727

August 26, 2010 at 11:07 am

#1212442

Oops, posted twice.

Lowell SSC Guru Points: 323518 More actions · Answer 7

ok, I just realized that the shortcut command you needed was for {slash L}, not {slash R]

ROWTERMINATOR =

vbCrLf = CHAR(13) + CHAR(10) = \n

vbCr = CHAR(13) = \r

vbLf = CHAR(10) = \l

--note that i am in the elite group that can now print \n

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

steve-893342 SSCarpal Tunnel Points: 4439 More actions · Answer 8

Lowell (8/26/2010)
ok, I just realized that the shortcut command you needed was for {slash L}, not {slash R]
ROWTERMINATOR =
vbCrLf = CHAR(13) + CHAR(10) = \n
vbCr = CHAR(13) = \r
vbLf = CHAR(10) = \l
--note that i am in the elite group that can now print \n

You are indeed! Nicely done:-)

matzer SSCrazy Eights Points: 9727 More actions · Answer 9

Lowell (8/26/2010)
ok, I just realized that the shortcut command you needed was for {slash L}, not {slash R]
ROWTERMINATOR =
vbCrLf = CHAR(13) + CHAR(10) = \n
vbCr = CHAR(13) = \r
vbLf = CHAR(10) = \l

I can't find anything about \l. It's also not working. In any C-like language I know \n = CHAR(10) and \r = CHAR(13). The sample code I posted comes straight from the description of BULK INSERT in BOL (example C):

C. Using line feed as a row terminator
The following example imports a file that uses the line feed as a row terminator such as a UNIX output:
Copy Code
DECLARE @bulk_cmd varchar(1000)
SET @bulk_cmd = 'BULK INSERT AdventureWorks.Sales.SalesOrderDetail
FROM ''<drive>:\<path>\<filename>''
WITH (ROWTERMINATOR = '''+CHAR(10)+''')'
EXEC(@bulk_cmd)

I suppose using \n is not working because (again according to BOL)

Note:
When you use bcp interactively and specify (newline) as the row terminator, bcp automatically prefixes it with a \r (carriage return) character, which results in a row terminator of \r \n.

I'm not sure what interactively means in this case but it seems to work the same way for BULK INSERT.

--note that i am in the elite group that can now print \n

Wow, am i a member of an elite group too now? 🙂

Peter

BravehearT1326 SSChasing Mays Points: 616 More actions · Answer 10

Folks - Thanks for this.

Same old "So near and yet so far"....

Again - Thanks for taking the time to work on this as it was driving me mad!!!;-)