topic Re: Storing into CSV with Comma and Double Quotes in QlikView

Storing into CSV with Comma and Double Quotes

haymarketpaul — Sat, 16 Nov 2024 21:23:00 GMT

QV Nov 2017 SR4

Having trawled through numerous posts (although some quite old) I'm not sure if this is even possible but I need to save a table to a CSV file with all fields enclosed in double quotes. It is for uploading into another system that is very specific on the format.

So this sort of thing:-

"ID","CompanyName","JobTitle"

"12345","Haymarket","Data Analyst"

I can happily save to a csv using this variable and STORE command:-

LET vCSVExport = '\\' & '$(vHammstore)' & '\Direct\Circulation\Data ops\Directories\SDL Export\Directories_$(vTodaysDate).csv (txt)';

STORE temp into $(vCSVExport);

I'm not sure how if possible to apply the double quotes?

I've tried putting msq before and after the txt part ie ...(msq,txt)'; or ...(txt,msq)';

but get this...

Unexpected token: 'msq', expected one of: 'qvd', 'txt', 'qvx', 'delimiter'

STORE temp into \\HAMMSTORE\Direct\Circulation\Data ops\Directories\SDL Export\Directories_19-02-2019.csv (>>>>>>msq<<<<<<,txt)

and this respectively...............

Unexpected token: 'msq', expected one of: ')', 'qvd', 'txt', 'qvx', 'delimiter'

STORE temp into \\HAMMSTORE\Direct\Circulation\Data ops\Directories\SDL Export\Directories_19-02-2019.csv (txt,>>>>>>msq<<<<<<)

Not sure what else to try so any ideas much appreciated

Re: Storing into CSV with Comma and Double Quotes

pradosh_thakur — Tue, 19 Feb 2019 11:36:51 GMT

may be use

chr(34) & your_field & chr(34) as  your_field

in your data mode

Re: Storing into CSV with Comma and Double Quotes

haymarketpaul — Tue, 19 Feb 2019 12:29:23 GMT

Thanks for the idea - unfortunately testing it on a couple of fields ends up with this sort of thing (for some reason?) in the CSV file.

"""145323""","""Angus Heathers""",

Also it doesn't affect the header row

Re: Storing into CSV with Comma and Double Quotes

sunny_talwar — Tue, 19 Feb 2019 12:46:12 GMT

@haymarketpaul wrote:
Also it doesn't affect the header row

For this may be you would want to load your first row as data itself and then you will have an extra row, but your header row will have double quotes around it

LET vDoubleQuote = Chr(34);

Table:
LOAD '$(vDoubleQuote)' & F1 & '$(vDoubleQuote)' as [F1],
	 '$(vDoubleQuote)' & F2 & '$(vDoubleQuote)' as [F2],
	 '$(vDoubleQuote)' & F3 & '$(vDoubleQuote)' as [F3];
LOAD * INLINE [
	F1, F2, F3
    ID, CompanyName, JobTitle
    12345, Haymarket, Data Analyst
];

STORE Table into Table.csv (txt);

This will create a csv like this

Re: Storing into CSV with Comma and Double Quotes

haymarketpaul — Tue, 19 Feb 2019 13:13:20 GMT

Thanks Sunny - still gives me this though with extra "s for some reason

F1,F2,F3
"""ID""","""CompanyName""","""JobTitle"""
"""12345""","""Haymarket""","""Data Analyst"""

Re: Storing into CSV with Comma and Double Quotes

haymarketpaul — Tue, 19 Feb 2019 15:42:52 GMT

Strangely it appears this is/was possible according to this post from Sept last year....

https://community.qlik.com/t5/QlikView-Deployment/Storing-into-CSV-with-Comma-and-Double-Quotes/m-p/93944?advanced=false&collapse_discussion=true&q=STORE%20to%20csv%20with%20double%20quotes&search_type=thread

...but even Miguel's (Qlik Employee) attached example gives me the same multiple quote problem?

Alas I'll have to explore some other way of achieving it I guess.

Re: Storing into CSV with Comma and Double Quotes

MattG — Fri, 12 Jul 2024 01:54:17 GMT

@haymarketpaul long shot, but did you ever make this work?

I'm in the exact same boat and can't figure it out.

Re: Storing into CSV with Comma and Double Quotes

haymarketpaul — Fri, 12 Jul 2024 06:44:02 GMT

No sorry never cracked it from within Qlik - as it was only needed for a short while I ended up just using ASAP Utilities within Excel which has the adding double quotes function built into it. You could also write some python code to do it (ask AI if unsure)

Re: Storing into CSV with Comma and Double Quotes

MattG — Fri, 12 Jul 2024 06:46:06 GMT

Thankyou for the reply, yep that's the route I'm looking to go down I reckon. A bit frustrating.

Thanks!