topic Re: Load CSV with odd delimiting in QlikView

Load CSV with odd delimiting

Wed, 08 Jul 2015 09:24:09 GMT

Hi,

Im trying to load data from a CSV file with odd delimiting.

Pls see attached.

Any clues how to come arounf this?

Re: Load CSV with odd delimiting

avinashelite — Wed, 08 Jul 2015 09:33:48 GMT

as per you data you can load the file with comma , as the delimiter rite ? what you mean by odd delimiter

Re: Load CSV with odd delimiting

jonathandienst — Wed, 08 Jul 2015 09:38:02 GMT

LOAD Date,

Counterparty,

Amount,

PurgeChar([CCY;;], '"[];') As CCY

FROM example.txt

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Re: Load CSV with odd delimiting

awhitfield — Wed, 08 Jul 2015 09:55:36 GMT

Hi Olle,

load as comma separated and then use purge char to remove the remaining " and ; then rename CCY;; to CCY

LOAD Date,
Counterparty,
Amount,
PurgeChar([CCY;;],';"') AS CCY
FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Andy

Re: Load CSV with odd delimiting

Wed, 08 Jul 2015 11:18:52 GMT

Hmm. Now that I go back to the original file I can see the structure was not as simple as the example file I made made before. Therefore your solutions did not really work when I tried it on the real data.

I've created a "true" example of my real data.

Can you pls show me how to apply your solution on that one instead?

Re: Load CSV with odd delimiting

tcullinane — Wed, 08 Jul 2015 11:42:54 GMT

You can use the , separated method above and use a preceding load to split the large text field using sting functions, mid/left/right hopefully if data is consistently present and same length in each row.

It may be possible to drop the field and a unique id out into a temp csv and reload with ' ' delimiter and map or join back in.

Re: Load CSV with odd delimiting

jonathandienst — Wed, 08 Jul 2015 12:12:38 GMT

You could apply a PurgeChar to each field loaded like this:

LOAD

PurgeChar([AS OF DATE], '";') As [AS OF DATE],

PurgeChar(BANK, '";') As BANK,

//...

PurgeChar([GROUP;;], '";') As GROUP

FROM example.txt

(txt, codepage is 1252, embedded labels, delimiter is ',', no quotes);

Note 'no quotes' to avoid a problem with unbalanced quotes.

Re: Load CSV with odd delimiting

Wed, 08 Jul 2015 13:21:31 GMT

Hi again Jonathan,

just realized some times the text field contains commas whithin the qoutes. Really annoying.

In other words I guess

For instance in the below example DATE will be hsjshskh 8267282

AMOUNT, TEXT, DATE;;

""999999"", ""XXXX uerururur 82726353, hsjshskh 8267282"", ""2015/01/01"";;

Any ideas how to come around this?