Convert TXT/QVD to XML ?

[dmohanty]

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?

[dmohanty]

Hi All,

Any possible help on this please?

[agni_gold]

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

A:

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

[#4]

Hi,

See that attachment.

Thanks,

krish

[marcus_sommer]

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

[dmohanty]

Hi Marcus,

Thank you for help.

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

[dmohanty]

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.

[marcus_sommer]

Try this:

sub x

set chart = ActiveDocument.GetSheetObject("CH01")

chart.ExportXml "D:\test.xml"

end sub

- Marcus

[dmohanty]

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.

[marcus_sommer]

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