Data loaded from text file appears jumbled up

[#1]

I am attempting to load data from a text file where the delimiter is '|'

My load statement is

(txt, codepage is 1252, embedded labels, delimiter is '|', msq, no eof )

When the data is loaded though records are jumbled up so for instance you will see sales regions appearing under sales manager.

I have tried both standard and no quotes to no avail. Also checked specific lines using rowNo and a text editor but can't spot anything visible.

I believe this topic has been raised but no resolution was offered.

Thanks

[sasiparupudi1]

Try copying all the text and paste it into a new text file and try loading it again.

[#3]

Yes unfortunately already tried that to no avail

[vlad_komarov]

It's probably not the best (and fastest) solution, but since you are stuck anyway:

Try to split this file into few and try to reload them separately.

If one of the file will be loaded with the same problem, try to split it into even smaller pieces....

At some point you should be able to locate a problem record(s).

[Ralf-Narfeldt]

One thing to look for is a double-quote character (") as the first character of a field, as that would mean the start of multi-line content in MSQ.

[#6]

Guys - if it helps attached are the import steps provided by the person who extracted the data.

You can see they have imported it as a csv, however the same issue exists whether in this format or text file.

[Ralf-Narfeldt]

I see that it uses a double quote " as text qualifier, so every column is surrounded by double quotes if you look in a text editor?

Any stray " inside the field contents? They would probably offset the columns.

[#8]

Thanks Ralf. Yes there are plenty of stray " inside the field contents. What's the best way to get round this ?

[sasiparupudi1]

repace all "" with nothing in a text editor

[Ralf-Narfeldt]

They need to be escaped with another "

So if there's " inside the field content, it needs to be replaced with ""

[#11]

I can change that but basically I have some description fields with an example something like this :

Injector; Grease; Sl1;3/8x1/8in NPTF F; 4 Outlet

Does this mean I have to go through the whole document and replace every special character ? And even having done so if I miss one it will throw all the columns off ? Surely there has to be a better way to get round this (?)