topic Export to text file with fixed length data in QlikView

Export to text file with fixed length data

Anonymous — Wed, 18 Dec 2013 14:41:28 GMT

I have written a macro to export the straight table in a qvw to a flat file (.txt) file and was able to specify the delimiter i wish to.

The macro which i used is as follows:

sub Export

Dim docProp

set docProp = ActiveDocument.GetProperties

Dim tmpFile 'used to create relative filepaths

tmpFile = docProp.MyWorkingDirectory

tmpFile = tmpFile & "External Data Sources\OperationalReportsOutput\"

set obj = ActiveDocument.GetSheetObject("CH01")

obj.Export tmpFile & "Activity" & ".txt",";"

end sub

Now i have to change the output data to a fixed length datatype. It means for suppose, i have 3 fields

Name Address Phonenumber

Sam Greenville,USA 2159627856

Vizard Charlotte, USA 2527255632

If i gave a fixed length of 20 to the fields:and i specify no delimiter,

the output should look like

Sam Greenville,USA 2139627856

Vizard Charlotte,USA 2527255632

So every record in the text file should end at the same point.

Any ideas!

Re: Export to text file with fixed length data

Gysbert_Wassenaar — Wed, 18 Dec 2013 14:45:50 GMT

You could do this by creating a straight table with only one expression that concatenates the fields into one string :

= left(Name,20) & repeat(' ', 20-len(Name1)) & left(Address,20) & repeat(' ', 20-len(Address)) & left(Phonenumber,20) & repeat(' ', 20-len(Phonenumber)).

Then export that straight table.

Re: Export to text file with fixed length data

Anonymous — Wed, 18 Dec 2013 14:56:13 GMT

Hi Gysbert Wassenaar,

That was a helpful solution, but they just want to know is it possible to do it in the macro so that the output file shows the data in the way they wish, without any change to the straight table

How can i specify it in the macro! i have no idea!

Re: Export to text file with fixed length data

Wed, 18 Dec 2013 15:35:46 GMT

In Qlikview, we don't have data types for fixed length fields. I suggest Please follow gwassenaarMethod or create the fixed length fields in the script it self.

LOAD IF(Len(Name) > 20, LEFT(Name,20) , Name & Repeat(' ', 20-Len(Name)) ) AS Name,

IF(Len(Address) > 20, LEFT(Address,20) , Address& Repeat(' ', 20-Len(Address)) ) AS Address,

IF(Len(Phonenumber) > 20, LEFT(Phonenumber,20) , Name & Repeat(' ', 20-Len(Phonenumber)) ) AS Phonenumber

SOURCE;

Re: Export to text file with fixed length data

Gysbert_Wassenaar — Wed, 18 Dec 2013 15:39:40 GMT

You don't have to change the original straight table. Create another straight table and hide it. Just make the button (or whatever) that calls the macro export the hidden straight table instead of exporting the original one.

Re: Export to text file with fixed length data

Anonymous — Wed, 18 Dec 2013 15:49:35 GMT

Hi Dathu

Thank you for your suggestion. I don't need the fixed length in qlikview. What i am asking is can we define the fixed length field while exporting to a text file. I mean it in the macro script. Just want to know can we do it in vbscript! If no other option is availabe, i can go with Gysbert Wassenaar's method.

Re: Export to text file with fixed length data

Anonymous — Wed, 18 Dec 2013 16:16:32 GMT

Gysbert Wassenaar

I have tried the method you said, by creating a new straight table with just one expression. But it is showing the output only when one Name or only one phonenumber is selected. without any selections, it is showing nothing!

Do i need to change something!

Re: Export to text file with fixed length data

Gysbert_Wassenaar — Wed, 18 Dec 2013 16:35:50 GMT

Ah, my mistake. Here's what you can do: Create a calculated dimension using the expression I posted. Then add an expression to the straight table, for example just 1 as expression will do. Set the total mode to None and then hide the expression on the presentation tab. The straight table with just that calculated dimension and the hidden expression should result in a table that will export to the format you want.

Re: Export to text file with fixed length data

Anonymous — Wed, 18 Dec 2013 16:42:03 GMT

I am using a aggregation function in the expression.

= left(TlrNbr,20) & repeat(' ', 20-len(TlrNbr)) & left(TlrName,20) & repeat(' ', 20-len(TlrName)) & left(Count(BrAccount),20) & repeat(' ', 20-len(Count(BrAccount)))

So, i think we cannot use it as a calculated dimension.

Re: Export to text file with fixed length data

Gysbert_Wassenaar — Wed, 18 Dec 2013 16:45:06 GMT

Try replacing count(BrAccount) with aggr(Count(BrAccount),TlrNbr,TlrName)

Re: Export to text file with fixed length data

Anonymous — Wed, 18 Dec 2013 17:02:20 GMT

Yes, It is working. Thank You

Re: Export to text file with fixed length data

Anonymous — Wed, 18 Dec 2013 20:03:40 GMT

But just as a question, can we do it in macro! i mean fixed length fields while exporting

Re: Export to text file with fixed length data

Gysbert_Wassenaar — Thu, 19 Dec 2013 06:47:55 GMT

Yes. Macro's are just vbscript. Find a good vbscript site to find out how to manipulate strings and write them to a file.