how to load this csv file?

[Frank_Hartmann]

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?

[awhitfield]

It should work fine as is.

Andy

[krishnacbe]

After loading the data you can remove the ".

Load *,

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

resident TableName;

drop field Outlet;

Rename Field Outlet1 to Outlet;

[Frank_Hartmann]

If i try to load it looks like that:

I attached my sample csv

[Frank_Hartmann]

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

[awhitfield]

Hi frank,

can you upload your .source file maybe?

Andy

[Frank_Hartmann]

see attached

[Peter_Cammaert]

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?

[Frank_Hartmann]

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!

[cwolf · Accepted solution]

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