topic Re: Saving a table with quotes in the data to a csv in App Development

Saving a table with quotes in the data to a csv

chriys1337 — Mon, 20 Dec 2021 21:08:46 GMT

Hi,

I would like to save my table to a .csv, and the values should be semicolon seperated.

Inside, the values are looking like "CompanyName" (including the quoation marks).

But it seems that the output is trippeling my quotation mark, in Qlik Datamodel everything looks fine.

Script:

source:
Load * INLINE [
MonthYear_num,customer_code
082019,OVH
082029,OVA
];

table:

Load
'"' & MonthYear_num & '"' as MonthYear_num2,
'"' &customer_code & '"' as customer_code2

Resident source;

Drop Table source;

STORE table. INTO $(vGoogleDriveTEMPPath)table.csv (txt, delimiter is ';');

The output looks like:

MonthYear_num2;customer_code2
"""082019""","""OVH"""
"""082029""","""OVA"""

Does anybody know how to save the .csv and the output just has one (instead of three quotes) in front and after each value?

Thanks a lot for your help.

chriys

Re: Saving a table with quotes in the data to a csv

Ksrinivasan — Sat, 15 Aug 2020 15:40:36 GMT

Hi,

you have to do one more load with MID function and stored csv.(txt);

then your result will be your way.

in addition to Script add

Drop Table table;

Test:
Load
MID("MonthYear_num2;customer_code2",1,8) & MID("MonthYear_num2;customer_code2",10,1) & MID("MonthYear_num2;customer_code2",12,5)as "MonthYear_num2;customer_code2"

From where you stored your table file,

Store Tablename into [path/XXX.csv](txt);

i have verified the result.

MonthYear_num2;customer_code2

"082019";"OVH"

"082029";"OVA"

Re: Saving a table with quotes in the data to a csv

stevedark — Sat, 15 Aug 2020 15:56:49 GMT

You could try having no quotes in the text format specifier:

https://help.qlik.com/en-US/sense/June2020/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptSpecifiers/quotes.htm

Not sure if this will work, but certainly worth a try.

Re: Saving a table with quotes in the data to a csv

chriys1337 — Sun, 16 Aug 2020 13:19:12 GMT

Thanks a lot for trying this out @Ksrinivasan , can you please copy your complete script. I can't rebuild it at the moment, getting an error because MonthYear_num2 and customer_code2 are 2 seperate fields in my initial script.

Re: Saving a table with quotes in the data to a csv

Ksrinivasan — Sun, 16 Aug 2020 13:51:50 GMT

hi,

I have tried for 3 hours,

try with more combination of "" but failed,

then use with chr(10) which is ascii for " this also failed,

then come to conclusion for workaround, finally as below

TTT:
source:
Load * INLINE [
MonthYear_num,customer_code
082019,OVH
082029,OVA
];
table:
Load
'"' & MonthYear_num & '"' as MonthYear_num2,
'"' &customer_code & '"' as customer_code2
Resident source;
Drop Table source;
Store table into [xxxxx/xxx//TTTFunction.csv](txt, delimiter is ';');
Drop Table table;
Test:
Load
MID("MonthYear_num2;customer_code2",1,8) & MID("MonthYear_num2;customer_code2",10,1) & MID("MonthYear_num2;customer_code2",12,5)as "MonthYear_num2;customer_code2"
FROM [[xxxxx/xxx//TTTFunction.csv]
(txt, utf8, embedded labels, delimiter is '\t', msq);
Store Test into [XXX/XXXX/TT1TFunction.csv](txt);

best of luck,