Import a textfile..?

[kenjoswe]

Hi all,

When I try to import a fixed field Unix textfile into a SQL-server table i get some strange result. I'm using LF as a row delmiter.

This is what I get when I look at the table after the import:
Field1 Field2
30180000014 500
3018
30180081222 015

As you can see the second record is sort of 'cut off'.

If I do the same import to a Access table i get the correct answer:
Field1 Field2
30180000014 500
30180000015 500
30180081222 015

If I study the textfile in a Hex-editor I can see that (simpified):
30180000014 500{LF}30180000014 500{LF}3018
0000015 500{LF}30180081222 015{LF}

Is there a bug in SQL 'DTS import textfile'

/Kent J.

 

[nigelrivett]

3018
0000015

What is the character that is causing this to display on two lines?
How are you looking at the table?
Try in query analyser using text output.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[kenjoswe]

Well, if I look directly into the table it's a square (•) before 3018 and that's a linefeed.

If I use the query analyzer I'll get:
Field1 Field2
30180000014 500
3018
30180081222 015

/Kent J.

 

[nigelrivett]

>> Well, if I look directly into the table
How are you doing that? Looking at the data page?
Try it in query analyser using text output.


What's after the
3018
and before the
0000015

which is on two lines in

30180000014 500{LF}30180000014 500{LF}3018
0000015 500{LF}30180081222 015{LF}

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[kenjoswe]

nigelrivett,

This is how the actual result look like in Query Analyzer, text output:
Field1 Field2
301800807503 0
3018
301810157580 101004

After 3018, on the second row, there is nothing more on that row.

This is how it looks in grid layout in Query Analyzer:
Field1 Field2
301800807503 129200
301800807503 0 3018
301810157580 101004

If I open the table from Enterprise Manager then I can see that there is a strange square symbol (Line Feed?) after the last character in Field2 except on the second row.

301800807503 129200?
301800807503 0
301810157580 101004?


If I open the textfile in a Hex editor then I can see that in the case of the second row the strange symbol is before '3018'.

I don't understand why I have this problem in SQL 'DTS import texfile' and not with the import routine in Access.

I you want to I can send you the real textfile which is 434 kb.

/Kent J.