topic Re: Export straight table to txt just as it is macro in QlikView

Export straight table to txt just as it is macro

daniel_kusiak — Wed, 25 Nov 2020 16:16:04 GMT

Hello Community,

I have a straight table:

What i want to do is to export this table to txt so the output will look like this:

taxonomy;measure;dimension;index;period;value

"F";"FBN01006";"FDWA000:FDWA002";;E;"0"

"F";"FBN01016";"FDWA000:FDWA002";;E;"0"

"F";"FBN01002";"FDWA000:FDWA002";;E;"26 105 178"

"F";"FBN01003";"FDWA000:FDWA002";;E;"26 105 178"

"F";"FBN01004";"FDWA000:FDWA002";;E;"26 105 178"

"F";"FBN01006";"FDWA000:FDWA025";;E;"0"

"F";"FBN01016";"FDWA000:FDWA025";;E;"0"

"F";"FBN01002";"FDWA000:FDWA025";;E;"26 105 178"

"F";"FBN01003";"FDWA000:FDWA025";;E;"26 105 178"

"F";"FBN01004";"FDWA000:FDWA025";;E;"26 105 178"

The problems i've encountered so fare are:

- addnig extra qoutes - for example "F" in my output is """F"""

- adding extras spaces - for emaple "F";"FBN01006";"FDWA000:FDWA002";;E;"0" is "F" ; "FBN01006" ; "FDWA000:FDWA002" ; ; E ;"0"

To export i'm using macro:

Sub Test

XLSFile = "e:\pulpit\Nowy SIZ\Nowe\test.xls"

TXTFile= "e:\pulpit\Nowy SIZ\Nowe\test.txt"

Set objExcelApp = CreateObject("Excel.Application")

Set objExcelDoc = objExcelApp.Workbooks.Add

set xlSheet = objExcelDoc.Sheets("Arkusz1")

ActiveDocument.GetSheetObject("CH07").CopyTableToClipboard true

xlSheet.Range("A1").Select

xlSheet.Paste

objExcelApp.DisplayAlerts = false

objExcelDoc.SaveAs XLSFile

objExcelDoc.SaveAs TXTFile, -4158

objExcelDoc.Close

objExcelApp.DisplayAlerts = true

End Sub

I've also tried to use Export option of Straight Table but results weren't satisfying.

Re: Export straight table to txt just as it is macro

muthukumar77 — Fri, 10 Mar 2017 11:21:17 GMT

Can you send sample qvw file?

Re: Export straight table to txt just as it is macro

adamdavi3s — Fri, 10 Mar 2017 11:32:18 GMT

Can you just use the store into option? *Edit* I guess this assumes your data is in a data model table though 😕

Store Table1 into Table1.txt (TXT, delimiter is ';');

Re: Export straight table to txt just as it is macro

marcus_sommer — Fri, 10 Mar 2017 11:41:38 GMT

Take a look here: Re: Export to CSV with quotation marks

- Marcus

Re: Export straight table to txt just as it is macro

daniel_kusiak — Fri, 10 Mar 2017 11:41:53 GMT

Adam, i need to export straight table, not store data.

🙂

Re: Export straight table to txt just as it is macro

daniel_kusiak — Fri, 10 Mar 2017 11:59:06 GMT

Marcus, the result of your macro is:

"taxonomy",";","measure",";","dimension",";","index",";","period",";","value"

""F"",";",""FBN01006"",";",""FDWA000:FDWA002"",";","",";","E",";",""0""

""F"",";",""FBN01016"",";",""FDWA000:FDWA002"",";","",";","E",";",""0""

""F"",";",""FBN01002"",";",""FDWA000:FDWA002"",";","",";","E",";",""26 105 178""

""F"",";",""FBN01003"",";",""FDWA000:FDWA002"",";","",";","E",";",""26 105 178""

""F"",";",""FBN01004"",";",""FDWA000:FDWA002"",";","",";","E",";",""26 105 178""

""F"",";",""FBN01006"",";",""FDWA000:FDWA025"",";","",";","E",";",""0""

""F"",";",""FBN01016"",";",""FDWA000:FDWA025"",";","",";","E",";",""0""

""F"",";",""FBN01002"",";",""FDWA000:FDWA025"",";","",";","E",";",""26 105 178""

""F"",";",""FBN01003"",";",""FDWA000:FDWA025"",";","",";","E",";",""26 105 178""

""F"",";",""FBN01004"",";",""FDWA000:FDWA025"",";","",";","E",";",""26 105 178""

In your solution the problem for me is:

1) for example ""F"" - should be "F", ""FBN01006"" - should be "FBN01006"

2) another delimiter - ,

3) delimiter is ";" - should be ;

4) column names "taxonomy" - should be taxonomy.

Re: Export straight table to txt just as it is macro

daniel_kusiak — Fri, 10 Mar 2017 12:25:18 GMT

Marcus, I modified your macro and now it works like charm.

sub CSVEportWithQuotes

set table = ActiveDocument.GetSheetObject( "CH07" )

for RowIter = 0 to table.GetRowCount-1

for ColIter =0 to table.GetColumnCount-1

set cell = table.GetCell(RowIter,ColIter)

if ColIter < table.GetColumnCount-1 then vDelimiter = ";" else: vDelimiter = ""

vContent = vContent & cell.Text & vDelimiter

vContent = vContent & chr(10)

msgbox vContent

set fso = CreateObject("Scripting.FileSystemObject")

set File = fso.OpenTextFile("e:\NoweTest.csv", 2, true)

File.Writeline vContent

File.Close

end sub

Thank you.

Re: Export straight table to txt just as it is macro

sasiparupudi1 — Fri, 10 Mar 2017 12:57:11 GMT

sub CSVEportWithQuotes

vContent=""

vDelimiter=";"

TXTFile= "C:\test.txt"

set obj = ActiveDocument.GetSheetObject( "CH10" )

for RowIter = 0 to obj.GetRowCount-1

for ColIter =0 to obj.GetColumnCount-1

set cell = obj.GetCell(RowIter,ColIter)

If ColIter=obj.GetColumnCount-1 then

vContent = vContent & cell.Text & vbcrlf

else

vContent = vContent & cell.Text & vDelimiter

end if

vContent = vContent & chr(10)

msgbox vContent

set fso = CreateObject("Scripting.FileSystemObject")

set File = fso.OpenTextFile(TXTFile, 2, true)

File.Writeline vContent

File.Close

Re: Export straight table to txt just as it is macro

sasiparupudi1 — Fri, 10 Mar 2017 12:57:50 GMT

Oops Sorry, I didn't realize that you got it already.

Re: Export straight table to txt just as it is macro

VivenReddy — Wed, 08 Sep 2021 15:43:27 GMT

This solution does not seem to work from the AccessPoint. It works from the Qlikview application. Anyone know how to get it working from Accesspoint?