BULK INSERT fails for Unix file

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

BULK INSERT fails for Unix file

Author

Topic

BravehearT1326
Starting Member

24 Posts

Posted - 2010-08-26 : 11:16:10

Hi

I have a UNIX generated file that I am looking to import into SQL 2005 using the BULK INSERT command. I seem to have issues with the end-of-line character not being recognised.

If I view the file in windows notepad it shows the lines of data one after another and the end of line character looking like a little rectangle.

If I open it in Windows Wordpad it shows as below.

CHK|CHECKING|08-24-2010
SAV|SAVINGS|08-24-2010
UNK|UNKNOWN|08-24-2010

You would then assume that the end of line character was a carriage return....but oh no!!!

BULK INSERT TEST.dbo.NA_BANKACCTTYPELOOKUP

   FROM 'C:\TEST\WXBankAcctType.DAT'

   WITH

     (  
       FIELDTERMINATOR ='|',
       ROWTERMINATOR = '\n\r'
      )

fails with the error
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 1, column 3 (LASTUPDATE).

The table structure I am attempting to insert into is:


CREATE TABLE [dbo].[NA_BANKACCTTYPELOOKUP](
	[BANKACCTTYPE] [varchar](3) NOT NULL,
	[BANKACCTTYPEDESCRIPTION] [varchar](35) NULL,
	[LASTUPDATE] [varchar](20) NOT NULL
) ON [PRIMARY]

Anyone got any ideas what is going on.....its driving me mad.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-08-26 : 12:16:02

Open it with a program like vi or vi for windows, this way you can see what the character is.

BravehearT1326
Starting Member

24 Posts

Posted - 2010-08-27 : 06:05:49

Had a look at the file in WinVi as you mentioned and it shows the ANSI/HEX character as being a 0a which is LineFeed. The decimal equivilent of this is 10

The default for BULK INSERT is \n which is NewLine which does not work and produces the same error message as before.

Tried running the BULKINSERT again this time using CHAR(10)as the ROWTERMINATOR and this still errors.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-08-27 : 06:29:10

Your unix file most probably just has line feed as the row delimter. When you are loading to sql you are using, both linefeed and CR as delimiter. One way you can get this working is by changing the file format to PC from UNIX using an editor like textpad. Or, You could just specify that linefeed is the row delimiter and try loading.

BravehearT1326
Starting Member

24 Posts

Posted - 2010-08-27 : 07:08:27

I have tried just using the LINEFEED as the row delimiter and this still fails. What I have found is that if I run the file thru SED for windows it generates a file that has a CR/LF as the row delimeter and this is read successfully.

Looks like SQL 2005 just cant have LF as the row delimeter.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-08-27 : 07:39:26

I have spent a good portion of my life on this exact problem and my solution was to run the file through a program that converted the LF's to CR/LF's. Here it is: http://www.thefreecountry.com/tofrodos/index.shtml

There, I just saved you a major headache!

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-08-27 : 07:46:44

textpad does this conversion too.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-08-27 : 07:56:21

This problem also comes in case you have files ftp'ed from a unix to windows server without turning on ASCII mode.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-08-27 : 08:03:27

I needed something that was fool proof and command-line based and tofrodos did it for me. Converted roughly 50GB of text-files on a daily basis without a hitch.

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-08-30 : 02:45:21

The files I converted were first compressed on the unix server, transferred via binary ftp and then uncompressed and it was simply impossible for me to find a way to import these files other than to do the lf/crlf replace.

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-08-30 : 03:04:54

quote:
Originally posted by Lumbago

The files I converted were first compressed on the unix server, transferred via binary ftp and then uncompressed and it was simply impossible for me to find a way to import these files other than to do the lf/crlf replace.

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Did you try ftp'ing in ascii mode instead?

BTW, Your blog page has a nice background to it.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-08-30 : 03:21:31

The files I transferred were compressed as .tar.gz-files so it didn't really matter which mode I used.

Thanx for the comment about my blog, unfortunately I can't take credit for it though...it's simply a theme in wordpress

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources