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 '10 at 02:41 AM in Default

BravehearT1326 gravatar image

BravehearT1326
11 1 1 1

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

0 answers: sort oldest
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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x103
x17
x10
x2

asked: Aug 26 '10 at 02:41 AM

Seen: 1007 times

Last Updated: Aug 26 '10 at 02:41 AM