topic Re: Avoiding double quotes when storing table in a text file! in QlikView

Avoiding double quotes when storing table in a text file!

qlikviewforum — Mon, 08 Oct 2012 05:21:47 GMT

Hi All

store Table into Data.bat(txt);

When I am trying to load the data into a txt file double quotes is getting added along with that.

For example:

If the original contains something like below.

"\\thrtjh568\qv\server\qvw\Development\QVD\Expense"

When it stores it into a txt file. It stores something like below.

""\\thrtjh568\qv\server\qvw\Development\QVD\Expense""

How can I eliminated double quotes when storing it in a text file. Please help!

Regards,

qvforum

Re: Avoiding double quotes when storing table in a text file!

qlikviewforum — Mon, 08 Oct 2012 08:46:12 GMT

Have already tried that but didn't workout!

Re: Avoiding double quotes when storing table in a text file!

stevedark — Thu, 16 Jan 2014 16:31:35 GMT

Did anyone find an answer to this one? In the past I have got around the problem by doing the store with a FileSystemObject write in a macro - but I've another case come up when I would much rather use a store on load.

Any suggestions welcome!

Steve

Re: Avoiding double quotes when storing table in a text file!

Fri, 17 Jan 2014 12:06:57 GMT

Are you populating your data from Excel file? It is seen in excel file, can you please try getting data from CSV file instead.

Re: Avoiding double quotes when storing table in a text file!

stevedark — Fri, 17 Jan 2014 13:30:49 GMT

As it happens the data is coming from Excel - but I'm not sure why that would affect the output?

When I get a chance I will give this a go. Thanks for the input!

Re: Avoiding double quotes when storing table in a text file!

sebastiandperei — Fri, 17 Jan 2014 13:43:52 GMT

It happens with all the fields?

Before the Store, try erasing the ", with replace, like:

replace (text_field, '"', '') (first parameter, the field; second, doble quotes between single quotes; last parameter, only a pair of single quotes)

And then Store it.

Re: Avoiding double quotes when storing table in a text file!

stevedark — Fri, 17 Jan 2014 15:07:06 GMT

The issues only occur when I have a comma or quote symbol in the data. QlikView tries to make things into a valid CSV file by quoting the values. The issue is though that I am writing out an HTML file with data in, so the quotation marks I have in the code are critical. Removing them as you suggest, with a Replace, would render my HTML incorrect.

It feels like there should be parameters to the STORE statement that would handle this - but I've not managed to hit upon the correct syntax yet.

-Steve

Re: Avoiding double quotes when storing table in a text file!

lalitinmbd — Thu, 15 May 2014 09:20:39 GMT

Hey Steve ,

Will you share the Macro that you used to eliminate the extra double quotes while storing.

Thanks

Lalit

Re: Avoiding double quotes when storing table in a text file!

stevedark — Thu, 15 May 2014 11:03:17 GMT

Hi Lalit,

I'm afraid the marco was written on site for a client so I don't have access to it, or to anything similar on hand.

It used the FileSystemObject and the Write statement. You should be able to find something similar if you search. You could try using the search on http://askqv.com/ to see if that turns up any suggestions.

Steve

Re: Avoiding double quotes when storing table in a text file!

Anonymous — Sun, 24 Jan 2016 06:58:07 GMT

In my case, once I open the data in csv, I see the double quotes in the original file. Excel seems to hide double quotes that start and end texts.

Re: Avoiding double quotes when storing table in a text file!

EmmaC — Thu, 24 Aug 2017 13:26:45 GMT

Hi! did you find a solution? I have the same problem!!

regards, Emma

Re: Avoiding double quotes when storing table in a text file!

stevedark — Thu, 24 Aug 2017 14:51:17 GMT

Hi Emma,

I have settled for writing out slightly malformed HTML, by not using double quotes in the text.

As far as I am aware there is not a solution. Pushing this thread back into view may turn something up though.

Steve

Re: Avoiding double quotes when storing table in a text file!

EmmaC — Fri, 25 Aug 2017 06:13:06 GMT

Hi Steve,

I had to settle with

(Get-Content Menu_List.html) | ForEach-Object { $_-replace '""', '"' } | Set-Content Menu_List.html

This cannot be the solution, though.

regards, Emma

Re: Avoiding double quotes when storing table in a text file!

Frank_Hartmann — Fri, 25 Aug 2017 10:32:51 GMT

this might be helpful

Generating SVG File in Qlikview Issue

Re: Avoiding double quotes when storing table in a text file!

Jonathan_Green — Tue, 17 Sep 2019 09:59:31 GMT

Writing HTML to file can cause one to run into three avoidable problems:

1. FIELD TRUNCATION:
Generating the HTML using LOAD INLINE will truncate your lines of code on commas which are being treated as field-delimiters.
We only want a single field, so avoid LOAD *, and most importantly, specify a non-printing-character delimiter:

LET vInlineDelim=CHR(07); // something that will not occur in text eg BEL

EMailOutput:
LOAD <!DOCTYPE html>
INLINE [
<!DOCTYPE html>
<html>
…… lines of html ….
</html>
] (DELIMITER IS ‘$(vInlineDelim)’);

2. DOUBLE QUOTES IN THE OUTPUT FILE
The next issue is caused by the STORE command, which ‘escapes’ quote marks by double-quoting as it attempts to create a CSV.
So, remove ALL single & double quotes from your HTML. However, for the HTML-tags still to work, you must also remove ALL SPACES from within tags:
<span style=font-family:Helvetica;font-weight:normal;Font-size:10pt;color:red> will give you Helvetica, red, 10pt.
<span style=font-family:Helvetica;font-weight:normal;Font-size:10pt;color: red> will give you Helvetica, 10pt but not in red, because of the leading space before “red”.

3. COMMAS CAUSING QUOTES AT START AND END OF RECORD
Any commas in the table’s field will by default cause STORE to enclose the field in double-quotes. To avoid this, include here also a DELIMITER IS:
STORE EMailOutput INTO [{filepath}EmailOutput.html] (txt, DELIMITER IS ‘$(vInlineDelim)’);