topic Re: Convert TXT/QVD to XML ? in QlikView

Convert TXT/QVD to XML ?

dmohanty — Sun, 15 Mar 2015 05:02:35 GMT

Hi All,

I have a Text File data (attached - LDAPExtracts.txt). I need to convert this to XML (may be using QlikView) in the format like the attached XML (CustomDirectoryData.xml).

This is required for Portal Login Authentication in QlikView.
Every user should have a UserName and Password to login.
The XML should have 3 main parameters/fields. The attached XML has these below:
- DisplayName (Ex VCC59434)
- GroupName (Ex QV_GPCS_Common_dev , QV_GPCS_Fin_dev , QV_GPCS_Initial_dev)
- Each GroupName has some members (Ex VCC59434)

Could someone please help here?

Re: Convert TXT/QVD to XML ?

dmohanty — Mon, 16 Mar 2015 12:17:49 GMT

Hi All,

Any possible help on this please?

Re: Convert TXT/QVD to XML ?

agni_gold — Mon, 16 Mar 2015 13:16:54 GMT

You can try like this and then export it into .xml type.

LOAD //@1,
'<?xml version="1.0" encoding="utf-8" ?>'
& '<CustomDirectory>'
& '<Versioninfo>'
& '<FileVersion>' & QlikViewVersion() & '</FileVersion>'
& '</Versioninfo>'
& '<CustomUser>'
& '<DisplayName>' & TextBetween( @1,'<','>', 2 ) & '</DisplayName>'
& '<Enabled>' & 'True' & '</Enabled>'
& '<Name>' & TextBetween( @1,'<','>', 2 ) & '</Name>'
& '</CustomUser>'
& '<CustomGroup>'
& '<Member>' & TextBetween( @1,'<','>', 2 ) & '</Members>'
& '<Name>' & TextBetween( @1,'<','>', 1 ) & '</Name>' AS XMLDATA
// ,TextBetween( @1,'<','>', 1 ) AS Group_Name,
// TextBetween( @1,'<','>', 2 ) AS Display_Name

FROM
C:\Users\agnivesh_kumar\Desktop\LdapExtracts.txt
(txt, codepage is 1252, no labels, delimiter is ' ', msq);

Re: Convert TXT/QVD to XML ?

Mon, 16 Mar 2015 13:27:27 GMT

Hi,

See that attachment.

Thanks,

krish

Re: Convert TXT/QVD to XML ?

marcus_sommer — Mon, 16 Mar 2015 14:00:10 GMT

You could export a tablebox or a table-chart to xml - maybe with a triggered macro like this:

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

tb.AddField "Class"

tb.AddField "Member"

tb.ExportXml "C:\test.xml",1200

If this don't work you will have to built these xml-structure manually by doing a (nested) loop through your source-table and store this result as output.xml (txt). But this will need some efforts to build a valid structure - but you could start with something like this:

source:

Load * From source;

for i = 0 to noofrows('source') - 1

let vField1 = peek('Field1', $(i), 'source');

.....

- Marcus

Re: Convert TXT/QVD to XML ?

dmohanty — Mon, 16 Mar 2015 14:37:30 GMT

Hi Marcus,

Thank you for help.

But I am a novice in Macro and XML. Could you please help in building it?

Re: Convert TXT/QVD to XML ?

dmohanty — Mon, 16 Mar 2015 14:46:57 GMT

Hi Agnivesh,

Thank you for the efforts in building this.

Though I am able to store this into a .XML file, the file says, "The XML page cannot be displayed", while I launch it.

Please help more.

Re: Convert TXT/QVD to XML ?

marcus_sommer — Mon, 16 Mar 2015 16:35:11 GMT

Try this:

sub x

set chart = ActiveDocument.GetSheetObject("CH01")

chart.ExportXml "D:\test.xml"

end sub

- Marcus

Re: Convert TXT/QVD to XML ?

dmohanty — Tue, 17 Mar 2015 14:29:18 GMT

Thank you Marcus,

Can you help me in converting the Text File just like the format in attached XML? I can help in walk you through the attached XML.

Re: Convert TXT/QVD to XML ?

marcus_sommer — Wed, 18 Mar 2015 07:59:25 GMT

When after exporting the table the xml-structure don't fit exactly to your requirements you will need some manually adjustment-steps maybe with a load-loop-statement like above mentioned. But maybe you could as an alternatively also adjust the target from the xml.

If at all the structure is more complicated it could be useful to look to another tools to convert or build the xml-structures: https://www.google.de/?gws_rd=ssl#q=tools+create+xml

- Marcus