How can I import data from a file that looks like XML but has a wddx Packet header?

[mikegrattan]

I am trying to import data into Qlik Sense from a file that kind of looks like XML, but it has a header of <wddxPacket version="1.0">

Sample data below:

<wddxPacket version="1.0">

<header/>

<data>

<string><agrian_reports> <pur id="12980404" date_created="2023-02-01 09:31:53" agrian_user_id="xxxx"> <when_applied>2023-01-31 15:10:00</when_applied> <show_commodity></show_commodity> <method>Ground</method> <grower>.</grower> <grower_account_number></grower_account_number> <bill_to></bill_to> <bill_to_account></bill_to_account> <applicator_name>IPMS</applicator_name> <applicator_address>4070</applicator_address> <applicator_city>Central</applicator_city> <applicator_zip>99999</applicator_zip> <applicator_id>36748</applicator_id> <reentry>24</reentry> <reentry_unit>Hours</reentry_unit> <preharvest>45</preharvest> <preharvest_unit>Days</preharvest_unit> <supervisor>Applicator Applied</supervisor> <comments>NO POSTING80&quot; BEDSTREAT BED TOP &amp; SHOULDERS / READY AT 3 PM Night Work Pre-Shift Safety Meeting Completed: YES OR NOWind Speed Below 10 MPH: YES or NO | Restrictions: Avoid Drift -- See Label Regarding Feeding/Grazing -- California Code of Regulations 6690-6692 go into effect starting January 2018. In summary these codes state pesticide use within a quarter mile of a school site or day care facility is prohibited between the hours of 6 AM to 6 PM on days when school is in session. For details or exceptions to this general summary please reference the code or speak to your County Ag Commissioner for clarification.</comments> <internal_workorder_id></internal_workorder_id> <status>open</status> <sent>True</sent> _id> <rec_tank_size>300</rec_tank_size> <rec_pca>Justin Grainger</rec_pca> <rec_pca_license>73647</rec_pca_license> <rec_applicator_id>36748</rec_applicator_id> <rec_applicator_internal_id></rec_applicator_internal_id> <rec_status>closed</rec_status> <site> <name>RANCH - LETTUCE ROMAINE</name> <organic>False</organic> <planted>300</planted> <planted_unit>Acres</planted_unit> 

[marcus_sommer]

I suggest to adjust the header to get a valid statement like:

<?xml version="1.0" encoding="UTF-8"?>

to find out if this file has a for Qlik valid xml-structure. Maybe some more adjustments might be needed to get such file loaded into Qlik.

If this isn't possible you will need any kind of connector to read the files or loading them purely as txt and applying a lot of string-functions to extract the wanted content (is probably rather the worse case).

But if this worked with rather less efforts and there aren't many files and they doesn't come/change too often you may remain by such manually approach. If such adjustments aren't practically you may look for any external script/batch to manipulate the content or you may do this manipulation within Qlik and then loading it with from_field.

[mikegrattan]

I ran into other issues downloading the web file and opening in Notepad ++ to edit it.  Somehow, it ended up without the <> brackets and instead looked something like this:

&lt;site&gt;
&lt;name&gt;19 - CELERY - 140 ACRES - TNA RANCH/&lt;/name&gt;

I ended up downloading the web file into an Access database and I used a VBA routine to extract the data and load it to a SQL Server table.