question

BravehearT1326 avatar image
BravehearT1326 asked

Bulk Insert SQL 2005 - Unix file not working

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\WXBankAcctType.DAT' WITH ( FIELDTERMINATOR ='|', ROWTERMINATOR = '+CHAR(10)+' ) But the error still appears.....Anyone got an idea how to fix this??? Table structure is: CREATE TABLE [dbo].[NA_BANKACCTTYPELOOKUP]( [BANKACCTTYPE] [varchar](3) NOT NULL, [BANKACCTTYPEDESCRIPTION] [varchar](35) NULL, [LASTUPDATE] [varchar](20) NOT NULL ) ON [PRIMARY]
insertfilebulkunix
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.