x

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]

more ▼

asked Aug 26, 2010 at 02:41 AM in Default

avatar image

BravehearT1326
11 1 1 1

(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x146
x25
x9
x2

asked: Aug 26, 2010 at 02:41 AM

Seen: 1333 times

Last Updated: Aug 26, 2010 at 02:41 AM

Copyright 2017 Redgate Software. Privacy Policy