Export straight table to txt just as it is macro

[daniel_kusiak]

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.

[muthukumar77]

Can you send sample qvw file?

[adamdavi3s]

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]

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

- Marcus

[daniel_kusiak]

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

🙂

[daniel_kusiak]

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 · Accepted solution]

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.

[sasiparupudi1]

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]

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

[VivenReddy]

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?