Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
daniel_kusiak
Creator II
Creator II

Export straight table to txt just as it is macro

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.

1 Solution

Accepted Solutions
daniel_kusiak
Creator II
Creator II
Author

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

    next

    vContent = vContent & chr(10)

next

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.

View solution in original post

9 Replies
muthukumar77
Partner
Partner

Can you send sample qvw file?

Muthukumar Pandiyan
adamdavi3s
Master
Master

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 ';');

marcus_sommer
MVP & Luminary
MVP & Luminary

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

- Marcus

daniel_kusiak
Creator II
Creator II
Author

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

🙂

daniel_kusiak
Creator II
Creator II
Author

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.

daniel_kusiak
Creator II
Creator II
Author

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

    next

    vContent = vContent & chr(10)

next

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.

View solution in original post

sasiparupudi1
Master III
Master III

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

    next

    vContent = vContent & chr(10)

next

msgbox vContent

set fso = CreateObject("Scripting.FileSystemObject")

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

File.Writeline vContent

File.Close

sasiparupudi1
Master III
Master III

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

VivenReddy
Partner
Partner

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?