Load CSV with odd delimiting

[#1]

Hi,

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

Pls see attached.

Any clues how to come arounf this?

[avinashelite]

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

[jonathandienst]

LOAD Date,

     Counterparty,

     Amount,

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

FROM example.txt

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

[awhitfield]

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

[#5]

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?

[tcullinane]

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.

[jonathandienst · Accepted solution]

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.

[#8]

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?