topic Re: Data loaded from text file appears jumbled up in QlikView

Data loaded from text file appears jumbled up

Thu, 23 Jul 2015 17:13:42 GMT

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

Re: Data loaded from text file appears jumbled up

sasiparupudi1 — Thu, 23 Jul 2015 17:21:54 GMT

Please post your data and script

Re: Data loaded from text file appears jumbled up

Fri, 24 Jul 2015 10:57:28 GMT

I think I can see the issue which is basically a load of special characters in the offending records. In this case how would I isolate certain columns from being loaded in ( given this is a text file ) ?

Re: Data loaded from text file appears jumbled up

Ralf-Narfeldt — Fri, 24 Jul 2015 11:02:39 GMT

It would help if you gave a sample of the offending lines + line before in the text file.

Re: Data loaded from text file appears jumbled up

sasiparupudi1 — Fri, 24 Jul 2015 11:05:53 GMT

You can try loading few columns at a time by commenting some..

Load

TransactionDate,

CompanyName

/*,

Comments,

CustomerName,

from myfile.csv

You may also want to look at purgechar function ex:purgechar(Comments,'@&,^$') as Comments

Hope this helps

Sasi

Re: Data loaded from text file appears jumbled up

sgrice — Fri, 24 Jul 2015 11:08:39 GMT

I have had this issue when some records do not have all the fields and the output is missing delimitors

I had to load the data has a big string for each record and parse the data manually within qlikview script

Re: Data loaded from text file appears jumbled up

awhitfield — Fri, 24 Jul 2015 14:12:12 GMT

Hi Sid,

can you upload the txt file?

Andy

Re: Data loaded from text file appears jumbled up

vlad_komarov — Fri, 24 Jul 2015 15:10:15 GMT

Try to remove the 'msq' from format-spec in your load statement.

Regards,

Re: Data loaded from text file appears jumbled up

Mon, 27 Jul 2015 09:49:43 GMT

Hi Vladimir

I tried that but it didnt work. Unable to upload the text file due to privacy issues unfortunately. Have tried pretty much everything including saving as an excel file / csv but to no avail.

Re: Data loaded from text file appears jumbled up

petter — Mon, 27 Jul 2015 10:17:03 GMT

Do you have a fixed number of columns in your text file? Is there a fixed amount of vertical bars in the file for each line? Are you sure that this is a codepage 1252 encoded file?

Re: Data loaded from text file appears jumbled up

Mon, 27 Jul 2015 10:41:20 GMT

Hi in answer to your questions :

Yes

Not sure how to verify

Re: Data loaded from text file appears jumbled up

sasiparupudi1 — Mon, 27 Jul 2015 10:45:18 GMT

Does it get loaded fine in other applications like excel?

Re: Data loaded from text file appears jumbled up

Mon, 27 Jul 2015 10:58:56 GMT

Yes seems to work ok in excel ( using a small extract of data ) but not csv or txt.

Re: Data loaded from text file appears jumbled up

Mon, 27 Jul 2015 11:01:35 GMT

Sorry correction - no does not work in excel either

Re: Data loaded from text file appears jumbled up

sasiparupudi1 — Mon, 27 Jul 2015 12:01:25 GMT

Sorry I did not get you

Re: Data loaded from text file appears jumbled up

Mon, 27 Jul 2015 12:29:04 GMT

Just to clarify - It does not load fine in other applications ie excel

Re: Data loaded from text file appears jumbled up

Ralf-Narfeldt — Mon, 27 Jul 2015 12:30:48 GMT

One method of dealing with troublesome textfiles is to load them as fixed record files without specifying field breaks. Each record will then contain a single field holding the entire row.

The next step is to use the SubField() function, using the | delimiter, in a resident load to split up the data in fields. Hopefully you'll be able to load most data this way. If you have isolated the problem you may be able to do some damage control here on the problem rows as well.

You will need to specify each field (field1, field2...) of course

RawData:

LOAD @1:n as raw

FROM [your_file]

(fix, codepage is 1252, no labels);

Data:

Load

SubField(raw, '|', 1) As field1,

SubField(raw, '|', 2) As field2,

...

Resident RawData;

Re: Data loaded from text file appears jumbled up

vlad_komarov — Mon, 27 Jul 2015 14:31:52 GMT

You probably have a missing '|' field separator somewhere in your records.

I know it's very annoying to debug this kind of issues, but you can load whole line from the text file as one text field and calculate a number of '|' characters in each one of them.

The string(s) with anomaly (with the number of separators less than usual) should be pointed out easily.

Regards,

Vladimir

Re: Data loaded from text file appears jumbled up

Mon, 27 Jul 2015 15:27:48 GMT

Hi So I checked this but the offending lines do in fact have the same number of separators

Re: Data loaded from text file appears jumbled up

Mon, 27 Jul 2015 15:48:05 GMT

Also if I load the offending line on it's own with just the header row it loads fine. It's as if the structure of the file itself is what's causing the problem