topic Re: How to add double quotes in QlikView

How to add double quotes

Anonymous — Mon, 30 Nov 2015 18:49:21 GMT

Hello,

I know how to read a QVD and export it to a CSV. But how would I go about adding double quotes to my

field data? For example, say my QVD contains 3 columns. First name, last name and street. I want the content of

my CSV file to look like this:

"JOHN", "DOE", "123 ABC STREET"

Thank you

Re: How to add double quotes

sinanozdemir — Mon, 30 Nov 2015 18:57:47 GMT

Hi,

See the below example, I used chr(34):

And here is the data model:

Hope this helps.

Re: How to add double quotes

Anonymous — Mon, 30 Nov 2015 21:00:08 GMT

When I used the chr(34) to surround the date field, it produced this format in my CSV which isn't what I wanted:

"""7/6/2011 12:00:00 AM"""

I only want to surround the data value with double quotes. How can I fix this?

Thanks for the help!

Re: How to add double quotes

sinanozdemir — Mon, 30 Nov 2015 21:31:11 GMT

Can you post a sample data-set here? Because you shouldn't be having this issue unless there are already double quotes in this field?

Re: How to add double quotes

Anonymous — Tue, 01 Dec 2015 13:32:47 GMT

Here's a screenshot of the first two columns in my QVD file.

As you can see, there are no quotes. I can produce a CSV as the way it is. I just needed to surround each

column with the double quotes. Thanks

Re: How to add double quotes

Anonymous — Tue, 01 Dec 2015 13:47:12 GMT

Here are the two lines of code in my load statement which produces the output shown in the screenshot.

chr(34) & Timestamp(TransactionPostDate) & chr(34) as 'TransactionPostDate2',

Date(TransactionPostDate) as 'TransactionPostDate',

Re: How to add double quotes

Anonymous — Tue, 01 Dec 2015 16:22:51 GMT

Even when I tried this, I got six double quotes. Is there some type of global setting that I need to look at?

LOAD

chr(34) & 'Test' & chr(34) as 'Col1'

"""Test"""

Re: How to add double quotes

sinanozdemir — Tue, 01 Dec 2015 16:56:24 GMT

Another question is how does your store function look like to export to csv?

I have been using the below and never had this problem:

Store Test into 'C:/Desktop/Text.csv'(txt);

Re: How to add double quotes

sinanozdemir — Tue, 01 Dec 2015 18:18:34 GMT

Probably. I think the best way to find out is looking at you app so if you could attach your qvw, it would be better.

Re: How to add double quotes

sinanozdemir — Tue, 01 Dec 2015 18:21:52 GMT

I think your text delimiter is "" ie "Test" in the source file so you may need to change that in the load script if you are loading the data from a csv or excel.

Re: How to add double quotes

Anonymous — Tue, 01 Dec 2015 18:22:44 GMT

I used the same syntax as yours.

Re: How to add double quotes

sinanozdemir — Tue, 01 Dec 2015 20:49:51 GMT

Can you actually post your app? Because your screenshots may not reflect the true state of your problem.

Re: How to add double quotes

anlonghi2 — Tue, 01 Dec 2015 21:26:53 GMT

Hi sidneylu,

try using

chr(96) & chr(34) & Timestamp(TransactionPostDate) & chr(34) as 'TransactionPostDate2',

Best regards

Andrea

Re: How to add double quotes

Anonymous — Tue, 01 Dec 2015 22:34:19 GMT

Andrea,

It did not work. It produced this format with a single tick on the "left".

"`""7/1/2011 12:00:00 AM"""

Re: How to add double quotes

anlonghi2 — Tue, 01 Dec 2015 23:23:33 GMT

Sidneylu,

wich version of excel do you have ?

Are you sure that in the excel is not defined a macro that changes the content of a cell ?

something like this:

Public Sub QuoteWrap()

Const q = """" 'double the double quotes

Dim ws As Worksheet

Dim rCell As Range

For Each ws In ActiveWorkbook.Worksheets

For Each rCell In ws.UsedRange

If Not IsEmpty(rCell) Then _

rCell.Value = q & rCell.Value & q

Next rCell

Next ws

End Sub

Re: How to add double quotes

Anonymous — Wed, 02 Dec 2015 01:13:17 GMT

Hi anlonghi2

Excel is not involved.

I'm simply loading a QVD file and storing it in a CSV file. After the CSV file is generated, I open it with

TextPad which is just a text editor to see the content as you can see from the screenshot I posted earlier.

This is really strange. Is there any setting I need to look? I am new to Qlikivew.

Re: How to add double quotes

Anonymous — Wed, 02 Dec 2015 01:40:24 GMT

I just created this at home. I'm experiencing the same issue.

Can someone use the below, try it and post your result? I'd be curious to find out.

contact:

LOAD

chr(34) & fname & chr(34) as 'fname1',

fname,

lname

FROM

C:\Qlikview\Test.txt

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

store contact into C:\Qlikview\contact.csv (txt);

//Test.txt

fname,lname

john,doe

//contact.csv

fname1,fname,lname

"""john""",john,doe

Re: How to add double quotes

rwunderlich — Wed, 02 Dec 2015 03:13:34 GMT

The store statement only generates quotes when the data contains something (like quotes) that should be quoted according to the MSQ quoting rules. So when you include the dquotes in your data, you trigger the quotes.

If your data is seen as not needing quotes, they don't get generated.

It's been a long standing request to have more control over output options in the Store statement. I feel your pain.

-Rob

Re: How to add double quotes

jonathandienst — Wed, 02 Dec 2015 05:02:26 GMT

There is no easy way to do this - QV is not designed as a data export tool, so although you can set the field delimiter, the txt/csv export is only done with standard quoting - fields that do not include the delimiter, or quote marks are not quoted, field values that contain the delimiter or quotes are quoted.

The easiest way is to do this in post processing with a text processing tool like sed (available as part of the GnuWin32 tool set), or with a powershell script.

Re: How to add double quotes

anlonghi2 — Wed, 02 Dec 2015 11:41:23 GMT

Hi Sidneylu,
please try the following approach.

TabletoExport:
LOAD MyFirstName & ',' & MyLastName & ',' & MyAddress as mystring;
LOAD
    chr(34) & [First Name] & chr(34) as MyFirstName,
    chr(34) & [Last Name] & chr(34) as MyLastName,
    chr(34) & [Address] & chr(34) as MyAddress
FROM
PEOPLE.csv
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

store * from TabletoExport into Myexport.csv (txt);

Best Regards
Andrea