topic Re: how to load this csv file? in QlikView

how to load this csv file?

Frank_Hartmann — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

i have csv data like:

Date,Country,Outlet,Sales

2017 CW 01,Germany,Outlet1,5530

2017 CW 01,Poland,Outlet2,4500

2017 CW 01,Germany,Outlet3,750

2017 CW 01,UK,"Outlet4,Ltd.",1900

2017 CW 01,France,Outlet5,552

2017 CW 01,Germany,Outlet6,7500

2017 CW 01,Spain,Outlet7,5700

Is there any possibility to load the text between the quotation marks (Outlet4,Ltd.) as Outlet?

Re: how to load this csv file?

awhitfield — Tue, 28 Mar 2017 14:32:13 GMT

It should work fine as is.

Andy

Re: how to load this csv file?

krishnacbe — Tue, 28 Mar 2017 14:42:19 GMT

After loading the data you can remove the ".

Load *,

Replace(Outlet,'",'') as Outlet1

resident TableName;

drop field Outlet;

Rename Field Outlet1 to Outlet;

Re: how to load this csv file?

Frank_Hartmann — Tue, 28 Mar 2017 14:47:56 GMT

If i try to load it looks like that:

I attached my sample csv

Re: how to load this csv file?

Frank_Hartmann — Tue, 28 Mar 2017 14:54:28 GMT

see above post. i think the problem is the comma inside the quotation marks.

Re: how to load this csv file?

awhitfield — Tue, 28 Mar 2017 15:15:25 GMT

Hi frank,

can you upload your .source file maybe?

Andy

Re: how to load this csv file?

Frank_Hartmann — Tue, 28 Mar 2017 15:35:25 GMT

see attached

Re: how to load this csv file?

Peter_Cammaert — Tue, 28 Mar 2017 15:59:23 GMT

This is in no way a step towards a solution, but may provide insight into the problem. The quoting is wrong. In your OP you showed that there are quotes around the third field in a specific row, but that's not really correct. The troublesome line is actually formatted like this:

"2013 CW 14,Angola,""Service - Comércial, L.t.d. (229)"",0"

which suddenly packages the entire line into a single string. Can you tell the exporter somehow to not do this anymore?

If not, maybe Marco has some time available to perfom his magic on a csv file like this?

Re: how to load this csv file?

Frank_Hartmann — Tue, 28 Mar 2017 16:14:24 GMT

hi peter,

thank you for answering. i was hoping that there are some known workarounds for this, but as you said,

it might be the easiest way to ask the people providing these exports to fix that!

Re: how to load this csv file?

cwolf — Tue, 28 Mar 2017 16:29:22 GMT

Hi Frank,

that is a typical problem of csv files. You can load it in the following way:

DATA:
LOAD 
concat(replace(Data,'""','"'),chr(13)&chr(10), RecNo) as Data
;
LOAD 
if(left(Data,1)='"' and Right(Data,1)='"', mid(Data, 2, Len(Data)-2), Data) as Data,
RecNo
;
LOAD 
RecNo() as RecNo,
@1:n as Data
FROM [.\Example.csv] (fix, codepage is 1252, no eof);

TABLE:
LOAD *
From_Field (DATA,Data) (txt, utf8, embedded labels, delimiter is ',', msq);

regards

Re: how to load this csv file?

Frank_Hartmann — Tue, 28 Mar 2017 17:57:13 GMT

thank you very much for sharing this 🙂

Re: how to load this csv file?

MarcoWedel — Fri, 31 Mar 2017 20:57:51 GMT

Hi,

maybe another solution (using the quoting feature twice) could be:

mapFields:
Mapping LOAD '@'&RowNo(), SubField(@1,',')
FROM [Example.csv] (txt, codepage is 1252, no labels, delimiter is '\t', msq)
Where RecNo()=1;

tabTemp:
LOAD * FROM [Example.csv] (txt, codepage is 1252, no labels, delimiter is '\t', msq)
Where RecNo()>1;

tabOut:
LOAD * FROM_FIELD (tabTemp, @1) (txt, utf8, no labels, delimiter is ',', msq);

DROP Table tabTemp;

RENAME Fields using mapFields;

hope this helps

regards

Marco