Post reply

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

  • 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:

  • August 26, 2010 at 4:46 am

    #1212204

    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!

  • August 26, 2010 at 4:53 am

    #1212210

    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.

  • August 26, 2010 at 9:45 am

    #1212389

    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 = '|'

    )

  • August 26, 2010 at 9:53 am

    #1212393

    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?

  • August 26, 2010 at 11:01 am

    #1212438

    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

  • August 26, 2010 at 11:07 am

    #1212442

    Oops, posted twice.

  • August 26, 2010 at 11:32 am

    #1212458

    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!

  • August 26, 2010 at 11:36 am

    #1212462

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

  • August 26, 2010 at 3:38 pm

    #1212585

    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

  • September 1, 2010 at 5:07 am

    #1214858

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

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

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