importing from text with weird character!!!

Question

importing from text with weird character!!!

Terry-380449

Default port

Points: 1458
More actions
January 9, 2007 at 9:38 am

#368716

i have a text file i am importing, using bulk insert
but there is a strange character at the end of each line, a little square, and the bulk insert is failing!!!
i tried to bulk insert with line delimeter as "\n", but it still wont work!! any clue how to diregard this character???

Viewing 14 posts - 1 through 13 (of 13 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Can you do a replace in the text file before importing??

Can you use a unicode text import (sorry but I don't know the real name of that option, just that it's possible to do so).

Terry-380449 Default port Points: 1458 More actions · Answer 2

i want to replace but i dunno what it is!

i thought it was a carriage return, but no...

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Open the file with notepad, highlight the character, then copy hit to the clipboard.

Go in query analyser and type this :

SELECT ASCII('

paste the character and type

')

Execute and you'll the ascii number. You'll then be able to trace it in an ascii table and know what it is.

I think you could also use that copy paste to move the character as the row delemeter along with "\n".

Terry-380449 Default port Points: 1458 More actions · Answer 4

ok i found out that it is a line feed, not a carriage return

it is a char(10)

can i write smthg like:

BULK INSERT OrdersBulk

FROM 'c:\file.csv'

WITH

(

ROWTERMINATOR = 'char(10)\n'

)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

I would think you'd have to paste the character directly in the code... I can't seem to do something like :

ROWTERMINATOR = CHAR(10) + '\n'

I'll let you play around with it. Let us know what worked for you.

Terry-380449 Default port Points: 1458 More actions · Answer 6

yeah it works only when i paste the character, but it doesnt convince me much!

There must be a way to express the equivalent of the ascii!!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

I guess that if you build the whole string in dynamic sql, it'll work then using something like this.

...

'rowdelimiter = ''' + char(10 + '\n'''

Maybe there's another way but I don't know it.

Terry-380449 Default port Points: 1458 More actions · Answer 8

and maybe someone can help in this:

the person who is passing me the text file is using some php thing,

fwrite($file,$res[0]."\n");

what i see is that he is using \n, but i can't understand how come SQL server is not considering is as \n...

He is passing me the file from a unix to a windows machine, by http://FTP... could that matter?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

Yes, different OS use different row delimiter. IIRC unix use char(10). While windows uses Char(10) + Char(13). Some other might use Char(13) only.

Just paste the char(10) from the clipboard and you'll be golden.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 10

CHAR(10) is the same thing as \n... use one or the other but not both.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jojonl73 SSC Veteran Points: 255 More actions · Answer 11

I have the same problem but do not see any weird / undiscovered sign at all.

my text file that i want to import using bulk insert or using dts, has a fix format 1 column format.

From the 1 column I want to make 3 columns. But the first row decides what the size is for the row?? ( a red line is showing it)

with the row delimiter I choosed every possible thing, but nothing did work.

maybe something has to do that a mainframe text file has another row delimiter than a windows text file???

but which then??

text file is like this:

0100023451234

01000245612oldp

01000145712ol

etc

columns i want to make:

01 000 2345 12 34

01 000 2456 12 oldp

01 000 1457 12 ol

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 12

Fixed format files with "ragged right data" are someones idea of a cruel joke.

One way to import these is to just import into a single column table and split the columns using substring. It's not a slow as you might think.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 13

rowterminator = '\r'

http://msdn2.microsoft.com/en-us/library/ms191485.aspx

N 56°04'39.16"
E 12°55'05.25"