Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
vamshi_1957
Contributor II

Macro to export as a flat file! Also need a tab delimiter

Hi, I have written a macro to export a sheet object to a flat file (.csv). I then gave a "OnPostReload" trigger.

So when the qvw reloads, the macro executes and a .csv file will be exported. Here is the macro

sub Export

Dim docProp

set docProp = ActiveDocument.GetProperties

Dim tmpFile  'used to create relative filepaths

tmpFile = docProp.MyWorkingDirectory

Dim vSpl  'location of final backslash char

vSpl = InStrRev(tmpFile,"\")

tmpFile = Left(tmpFile,vSpl-1)

vSpl = InStrRev(tmpFile,"\")

tmpFile = Left(tmpFile,vSpl) & "External Data Sources\OperationalReportsOutput\Activity_" & DatePart("YYYY", Now()) & right( "0" & DatePart("m", Now()) ,2) & right( "0" & DatePart("d", Now()) ,2) & ".csv"    

set obj = ActiveDocument.GetSheetObject("CH01")

obj.ExportBiff tmpFile

end sub

As, i need the date at the end of the name of the export flat file, i used the date function to get the date in the format i need.

Now, they need a .txt file with space delimiter. I have tried that by just changing ".csv" to ".txt". Then, the output file is forming, but when i open the output file with a wordpad, the data is unreadable. Whereas when i open the same file with excel, it is showing the data.

My question is how to define the space delimiter? I have tried it by giving ".txt", (txt, delimiter is " ")

But it is showing the error! Hlep me...

Tags (1)
1 Solution

Accepted Solutions
jerrysvensson
Valued Contributor II

Re: Macro to export as a flat file! Also need a tab delimiter

From APIguide

set tb = ActiveDocument.Sheets("Main").CreateTableBox

tb.AddField "Class"

tb.AddField "Member"

tb.Export "C:\test.skv",";"

Try:

tb.Export "C:\test.txt"," "

don't know if " " will work though.

4 Replies
jerrysvensson
Valued Contributor II

Re: Macro to export as a flat file! Also need a tab delimiter

Use:

obj.Export tmpFile instead of

obj.ExportBiff tmpFile

ExportBiff export to Excel format.

Download the APIguide.qvw from the forum.

vamshi_1957
Contributor II

Re: Macro to export as a flat file! Also need a tab delimiter

Hi Jerry Svensson

Thanks for your reply.

It is not working if i use Export tmpFile

jerrysvensson
Valued Contributor II

Re: Macro to export as a flat file! Also need a tab delimiter

From APIguide

set tb = ActiveDocument.Sheets("Main").CreateTableBox

tb.AddField "Class"

tb.AddField "Member"

tb.Export "C:\test.skv",";"

Try:

tb.Export "C:\test.txt"," "

don't know if " " will work though.

vamshi_1957
Contributor II

Re: Macro to export as a flat file! Also need a tab delimiter

Thank You very much,

Yes it is working (aslo "C:\test.txt"," "). This is the final script which i used and it is giving the out put as required.

sub Export

Dim docProp

set docProp = ActiveDocument.GetProperties

Dim tmpFile  'used to create relative filepaths

tmpFile = docProp.MyWorkingDirectory

Dim vSpl  'location of final backslash char

vSpl = InStrRev(tmpFile,"\")

tmpFile = Left(tmpFile,vSpl-1)

vSpl = InStrRev(tmpFile,"\")

tmpFile = Left(tmpFile,vSpl) & "External Data Sources\OperationalReportsOutput\"

set obj = ActiveDocument.GetSheetObject("CH01")

obj.Export tmpFile & "Activity_" & DatePart("YYYY", Now()) & right( "0" & DatePart("m", Now()) ,2) & right( "0" & DatePart("d", Now()) ,2) & ".txt"," "

end sub

Community Browser