topic Re: STORE to csv - quotes in QlikView

STORE to csv - quotes

markadavies — Wed, 17 Feb 2016 12:17:16 GMT

I need to create a '|' separated csv file with each field value within quotes to allow upload to a central database.

My Store command creates the csv file, but the quotes that I have included with each value are written inside additional sets of quotes

For example, if I want to export a field value "ABC", the resultant csv file actually contains """ABC"""

Is there any way to prevent this happening without any further actions after my script has executed? (I don't want to use macros or have to process the file again in VB etc.)

I have tried to include a 'no quotes' parameter to my Store command to no avail

The STORE command I am using is similar to this

STORE MyTable INTO mycsvfile.csv (txt, delimiter is '|');

Re: STORE to csv - quotes

m_woolf — Wed, 17 Feb 2016 13:12:05 GMT

If all fields contain quotes, you could do:

replace(Field,'"','') as Field

That would remove the quotes from the field.

Re: STORE to csv - quotes

swuehl — Wed, 17 Feb 2016 13:18:04 GMT

You can vote for this idea and hope it's getting implemented soon..

Re: STORE to csv - quotes

marcus_sommer — Wed, 17 Feb 2016 13:28:02 GMT

A workaround could be to create already your table in this way, like:

csvTable:

Load F1 & '|' & F2 & '|' & F3 & '|' & F4 ... as csvField From xyz;

store ...

- Marcus

Re: STORE to csv - quotes

jonathandienst — Wed, 17 Feb 2016 13:39:53 GMT

Only problem is that produces a header line in line 1 reading csvField. One possible workaround

csvTable:

Load F1 & '|' & F2 & '|' & F3 & '|' & F4 ... as [F1|F2|F3|F4] From xyz;

store ...

but I think the F1 and F4 fields will contain the square brackets [F1 and F4]...

Re: STORE to csv - quotes

marcus_sommer — Wed, 17 Feb 2016 13:57:09 GMT

Of course, you are right and it might need a combination from double-quotes, square-brakets and grave accents.

- Marcus

Re: STORE to csv - quotes

markadavies — Wed, 17 Feb 2016 14:22:58 GMT

... so it looks like this is a well known issue.

I have voted as you suggest, have you any idea if Qlik have indicated any intention to look at this?

Re: STORE to csv - quotes

markadavies — Wed, 17 Feb 2016 14:27:13 GMT

Good idea Marcus,

I have tried it but it does not get around the issue unfortunately

It looks as though there is a well known issue, and trawling the community there are a number of similar queries that are unanswered.

There just is not enough control over the output when using the STORE command, QlikView will encapsulate quotes within another set of quotes wherever it finds them.

Re: STORE to csv - quotes

marcus_sommer — Thu, 18 Feb 2016 08:20:42 GMT

I'm not sure if you really needs quotes if you used '|' as delimiter - or maybe another unique char which is very unlikely to be within the data (such a other unique char could be also used as a quotes-replacing which in a further processing could be reversed)

- Marcus

Re: STORE to csv - quotes

markadavies — Mon, 22 Feb 2016 09:25:39 GMT

Unfortunately I do need to include quotes as it is the specification for files that will be imported into a central database.

If I find any work around I will update the thread

Thanks

Re: STORE to csv - quotes

Anonymous — Wed, 17 Oct 2018 11:37:40 GMT

I have seen looking around many post the same issue since 2013 and I really feeling shocking that qlik has not declare an oficial solution. I have the same issue and this issue has generated several questioning around my company to prospect to migrate a qlik environment. Another developer is just working now in an PHP cron that solve the problem. Quotes 1, QLIK 0.

Regards

Re: STORE to csv - quotes

markadavies — Wed, 09 Jan 2019 08:14:00 GMT

Yes, as no solution was forthcoming I have now created the file within SSIS rather than QlikView. Originally I did not have SQL Server to be able to do this so was reliant on QlikView.

Very much get the impression that Qlik are not very interested in such matters unfortunately.