topic How to export a table to a text file without extra quotes in QlikView

How to export a table to a text file without extra quotes

Tue, 15 Mar 2011 18:23:30 GMT

I have a table that has a single text field in it. When I export it to a csv file in a version 10 script, for example

store mytable into myfile.txt (txt)

QlikView wraps double quotes around the lines in myfile.txt if there are spaces in the line. Also, if there is a " in the text itself QlikView adds another " next to it.

I would like for the exported text file to not contain the added quotes. I can certainly post-process the text file but I would prefer not to. Does anyone have a solution for this?

Thanks,

Barry

How to export a table to a text file without extra quotes

Wed, 16 Mar 2011 08:02:08 GMT

Hi,

The STORE takes exactly same parameters as the LOAD. Make a suitable LOAD using the wizard, and copy the string 🙂

For example:


store * from Fact into [x.txt]
(txt, codepage is 1252, no labels, delimiter is '\t', no quotes)

-----------

As a side note, if one column contains spaces, there will be still qutes in the output file . Use for example good old "tr" command line


tr -d \042 x.txt > y.txt

-Alex

How to export a table to a text file without extra quotes

Wed, 16 Mar 2011 08:34:04 GMT

Actually quotes appear inthe output file if the field values contain the separator character. In wich case it is time to choose another separator, or clean up the data.

-Alex

Re: How to export a table to a text file without extra quotes

Wed, 24 Aug 2011 15:09:15 GMT

Hi,

I'm also running into this same problem:

I'm using this command:

STORE

From Material INTO $(File) (txt, delimiter is '|', no quotes, msq);

MATNR, MAKTX, MEINS

But the outcome is:

HM200PS|"UNIFLEX HM200 SERVICE CRIMPER 1-1/4"""|PCS

So quotes around the field MAKTX and an extra quote in front of the existing quote.

Originl MAKTX was : UNIFLEX HM200 SERVICE CRIMPER 1-1/4"

Any help would be highly appreciated!

How to export a table to a text file without extra quotes

Anonymous — Thu, 25 Aug 2011 07:33:02 GMT

What is the purpose of the txt file? Is it to be loaded into another QV application, Excel or something. If so, remove the quotes during load at that time. If not, post proces the txt file to replace the quotes. I once needed to do and put this together with help from forum:

'...more code above......>>

set obj = ActiveDocument.GetSheetObject("SAS")

set pth = ActiveDocument.Variables("vPath")

obj.Export pth.getcontent.string&"output\clause.txt", 1

' Replace the double quotes from the txt file. Could not find a way to export without??

Const ForReading = 1

Const ForWriting = 2

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFile = objFSO.OpenTextFile(pth.getcontent.string&"output\clause.txt", ForReading)

strText = objFile.ReadAll

objFile.Close

strOldText = Chr(34)

strNewText = Chr(0)

strNewText = Replace(strText, strOldText, strNewText )

Set objFile = objFSO.OpenTextFile(pth.getcontent.string&"output\clause.txt", ForWriting)

objFile.WriteLine strNewText

objFile.Close

PS: vPath is the path variable I define in QlikView.

How to export a table to a text file without extra quotes

Thu, 25 Aug 2011 14:19:51 GMT

The purpose is to create a file that can be uploaded into SAP.

I used the suggested workaround, only I created a smal program to do the trick. This is only slowing down the entry process as it basically has to write the file twice.

I also reported it back to QlikView, so let's see if they come up with a solution, basically the 'no quotes'statement shoud do the trick but.....

Thanks anyway!!

Re: How to export a table to a text file without extra quotes

Thu, 08 Sep 2011 21:14:41 GMT

Hi,

I reported the fact that the 'no quotes'does work in the store command to the Qlikview team. They have marked it as a bug which will be fixed in one of the next relases

Arend

How to export a table to a text file without extra quotes

rbecher — Thu, 06 Oct 2011 11:11:39 GMT

Hi Arend,

do you have a bug number for this? When they will fix it?

Can you please make also a note here: http://community.qlik.com/groups/qlikbug

- Ralf

How to export a table to a text file without extra quotes

Fri, 07 Oct 2011 14:31:09 GMT

Ralf,

The ID is 69149. unfortunately I can't post at qlikbug as I don't have have access to it 😉

Arend

How to export a table to a text file without extra quotes

Fri, 14 Oct 2011 00:22:59 GMT

Hey!

I had the exact same problem and found this post. Started to make a macro but out of pure luck (trial and error)

I got this to work.

Store ExportTest Into C:\temp\SpacesExport.txt (txt, codepage is 1252, embedded labels, delimiter is 'spaces' ,no quotes);

The trick is to have the delimiter is 'spaces' and the spaces word within single quotes.

I want to remove the labels though but havent got that to work. Tried this:

Store ExportTest Into C:\temp\SpacesExport.txt (txt, codepage is 1252, no labels, delimiter is 'spaces' ,no quotes);

Set it to no labels instead of embedded but QV doesn't pick that up.

How to export a table to a text file without extra quotes

rbecher — Fri, 14 Oct 2011 09:07:29 GMT

Hi babushka,

unfortunately it's not working. Which version you have?

- Ralf

Re: How to export a table to a text file without extra quotes

qlikviewforum — Sat, 06 Oct 2012 05:35:43 GMT

Hi All

I am also facing similar problem. In the attached spread sheet I have file location with double quotes. When I store it as a text file it adds two more double quotes along with that. I want to store the location in text file only with single double quotes. May I know how that can be achieved?

I know if I dont pass the double quotes along with the location txt file automatically adds single double quotes. But for some reasons I want to pass the double quotes along with the location. I am working on filesystems where I should consider the spaces between the folders(Eg: New Folder).

Any idea how that can be acheived please?

Output:

FilesLocation

XYZs"""C:\Test\New Folder"""

Oops also i think in the above output s got added along with the XYZ by iteself. That also need to be avoided! But first thing is achieving below output.

Expected output:

FilesLocation

XYZs"C:\Test\New Folder"

Hope some can help me out. Have attached the file for your reference!

Re: How to export a table to a text file without extra quotes

Fri, 24 May 2013 04:20:14 GMT

Hi Nicolai!

REALLY USEFUL POST!!

Thanks alot for sharing.

Re: How to export a table to a text file without extra quotes

Fri, 24 May 2013 06:23:18 GMT

HI,

This is one of the Issue which everybody is facing .

What i found as per my understanding it is not a QlikView Bug .

http://community.qlik.com/thread/79439

//Yusuf

Re: How to export a table to a text file without extra quotes

stevedark — Thu, 16 Jan 2014 16:33:29 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: How to export a table to a text file without extra quotes

lalitinmbd — Thu, 15 May 2014 12:32:19 GMT

I am using this Macro to remove the extra Quotation marks in my txt file. It is removing the quotes but it is adding 1 into the txt file which is wrong. Any ideas why it is happening.

Re: How to export a table to a text file without extra quotes

stevedark — Thu, 15 May 2014 13:14:50 GMT

Hi Lalit,

Can you post up the Macro code you are using? Is the 1 on the first line? If so, it is probably the column heading or a total. If it's the latter you may be able to suppress it by turning off totals for the expressions.

I seem to recall that in the macro I used previously I loaded the whole text file into a variable in the macro and then did a replace on it to fix some of the odd things that QlikView was doing.

I really should have kept a copy of that code!

Steve

Re: How to export a table to a text file without extra quotes

lalitinmbd — Thu, 15 May 2014 13:43:00 GMT

Hi Steve,

I am using the below Macro Code

Sub Export

set obj = ActiveDocument.GetSheetObject("TB01")

set pth = ActiveDocument.Variables("path")

obj.Export pth.getcontent.string&"Export.txt", 1

' Replace the double quotes from the txt file. Could not find a way to export without??

Const ForReading = 1

Const ForWriting = 2

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFile = objFSO.OpenTextFile(pth.getcontent.string&"Export.txt", ForReading)

strText = objFile.ReadAll

objFile.Close

strOldText = Chr(34)&chr(34)

strNewText = chr(0)

strNewText = Replace(strText, strOldText, strNewText )

Set objFile = objFSO.OpenTextFile(pth.getcontent.string&"Export.txt", ForWriting)

objFile.WriteLine strNewText

objFile.Close

End Sub

Re: How to export a table to a text file without extra quotes

stevedark — Thu, 15 May 2014 13:53:25 GMT

If you can work out what the 1 is, and make it something else (eg. if it was a column label rename that column). You could then do a further replace:

strNewText = Replace(Replace(strText, strOldText, strNewText ), 'Column Header', '')

Steve

Re: How to export a table to a text file without extra quotes

lalitinmbd — Thu, 15 May 2014 14:00:51 GMT

Dear Steve,

I am surprised from where the 1 is coming as there is nowhere 1 in the column headers. When i do export using the macro. Below is my result.

Company1BO1Shop1Sales

Samsung1 "DAVIDOFF GOLD LIGHTS SLIM FI BOX 200 "1"TO00000031"130.50

"Samsung'"1BENSON & HEDGES GOLD FF KS FI BOX 2001TO00000006127.50

"Samsung "1BENSON & HEDGES GOLD FF KS FI BOX 4001TO00000002149.50

Sony1CAFE CREME 5X201TO00000224124.00

"Sams'ung"1CAMEL BLUE LIGHTS KS FI BOX 6001"TO00000017"175.00

Samsung*1CAMEL FF KS FI BOX 2001"TO00000013"129.50

"Samsung "1CAMEL FF KS FI BOX 4001"TO00000014"153.00

"Samsung ' Samsung"1DAVIDOFF CLASSIC FF KS FI BOX 2001TO00000030130.50

Samsung1DUNHILL BUTTON RED FF KS FI BOX 2001TO00000039130.00

Samsung1DUNHILL INTERNATIONAL BUTTON RED FF KS FI BOX 2001TO000000451"31.00"

Samsung1FORTUNA RED FF KS FI BOX 2001TO00000052127.50

Samsung1GAULOISES BLONDES BLUE FF KS FI BOX 2001TO00000059127.50

Samsung1GLAMOUR MENTHOL SS FI BOX 2001"TO00000071"1-

Samsung1KENT HD SILVER LIGHTS KS FI BOX 2001TO00000076130.00

MicroMax1L&M RED FF KS FI BOX 2001TO00000079127.50

MicroMax1LAMBERT & BUTLER FF KS FI BOX 4001TO00000082149.50

MicroMax1LUCKY STRIKE RED FF KS FI BOX 2001TO00000089127.50

MicroMax1LUCKY STRIKE RED FF KS FI BOX 4001TO00000090149.50

MicroMax1MARLBORO GOLD LIGHTS KS FI BOX 2001"TO00000104"130.00

Sony1MARLBORO GOLD LIGHTS KS FI BOX 4001"TO00000105"153.50

MicroMax1MARLBORO RED FF KS FI BOX 2001TO00000099130.00

Originally My Data is like

Company	BO	Shop	Sales
"Samsung"	BENSON & HEDGES GOLD FF KS FI BOX 400	TO00000002	49.50
Samsung'	BENSON & HEDGES GOLD FF KS FI BOX 200	TO00000006	27.50
Samsung*	CAMEL FF KS FI BOX 200	TO00000013	29.50
""Samsung""	CAMEL FF KS FI BOX 400	TO00000014	53.00
Sams'ung	CAMEL BLUE LIGHTS KS FI BOX 600	TO00000017	75.00
Samsung ' Samsung	DAVIDOFF CLASSIC FF KS FI BOX 200	TO00000030	30.50
Samsung	"DAVIDOFF GOLD LIGHTS SLIM FI BOX 200"	TO00000031	30.50
Samsung	DUNHILL BUTTON RED FF KS FI BOX 200	TO00000039	30.00
Samsung	DUNHILL INTERNATIONAL BUTTON RED FF KS FI BOX 200	TO00000045	31.00
Samsung	FORTUNA RED FF KS FI BOX 200	TO00000052	27.50
Samsung	GAULOISES BLONDES BLUE FF KS FI BOX 200	TO00000059	27.50
Samsung	GLAMOUR MENTHOL SS FI BOX 200	TO00000071
Samsung	KENT HD SILVER LIGHTS KS FI BOX 200	TO00000076	30.00
MicroMax	L&M RED FF KS FI BOX 200	TO00000079	27.50
MicroMax	LAMBERT & BUTLER FF KS FI BOX 400	TO00000082	49.50

Regards

Lalit