load a txt file with a structure

[FrankGrimm]

Hello together,

i have a txt file which has a structure like a xml file.

Please see attached file.

How can i load the information with the posibility to define different tables for the structure informations, so that every information of "Schablone Nr 1 " .... is available in fileds.

Thank to all.

[marcus_sommer]

Did you try to load the file as xml? I'm not sure if the content would be regarded as a valid xml-syntax and if not you may consider to "repair" the data. If it's just one file it might be adjusted manually within an editor. By multiple files it's of course quite tedious but the necessary steps might be doable with Qlik, for example by loading a valid xml-header within an inline-table and concatenating this file (loaded with fixed length) to it by maybe implementing various string-functions to check and repair missing/wrong tags. And afterwards you could apply the xml-load  per from_field feature.

[FrankGrimm]

Hello Marcus,

thank you for your help. yes, i cloud load it a a xml, but some structures where not dissolved. i´m loosing informations.

I thought i could use in the load order if conditions which load the content to defined fields.

like:

if (substringCount(@1,'<Benutzerschablone>')>0,....

Is there a possibility to skip to the next row to read the following content?

Thanks

[ogster1974]

There is a away to load it.  I did find an error in your file missing " not sure if intentional or not.

I loaded the file with a rowno() then looped through the data.  Identifying table structure headers and footers and pushing the content into the tables via variables I'd pre loaded in that structure.

[marcus_sommer]

I assume I understand - that you don't want to load the values between the tags else the various information within the tags. I'm not sure but I believe to remember that there was a feature to load these information by loading them as html and not as xml. The topic with needing a valid file-format with a header and so on will probably remain. Beside further searches within the community to this matter you may create a very small dummy html and then playing within the file-wizard if you could load it in a sensible way.

Nevertheless I think it's possible to load the file directly as txt and extracting everything with string-functions, for example identifying each layer per counting the leading space and/or tabs or if this isn't unique enough the row-content could be checked with previous() and with it an appropriate layer-counter is set. Afterwards subfield() without the third parameter is used to split the multiple entries to multiple records. And on top come then various cleaning measures to remove unwanted content and the tag-garbage. Depending on the complexity it may also necessary to split some task in extra loads with/without string-aggregations which are afterwards again splitted with subfield() and the results joined/concatenated ... Surely not a nice work but possible.

[FrankGrimm]

Hy Marcus, i discussed the problem with my wife, and  she gave me the same idea to use the spaces as an identifier. I´ll try it. Thanks a lot!

[FrankGrimm]

Hey, ogster1974,

i looks greate. Could i get your script?

That would be a big big help!!

Thanks for your time and help!

Frank

[ogster1974]

I'll give you the recipe rather than the script as its a good challenge to work through.

1. Initialise variables one per attribute I wanted to extract from the file

2. create empty tables for my model one per entity so I can concatenate the data from the files into them later.

3. Load the text file in 

4. create  a for loop to run through the data line by line

5. for each line check for entity the line relates to using an if statement and store the fields found in the appropriate variable.  I used the =  identifier in subfield and position to extract.

6. concatenate the variables into the appropriate table.

Note.  Add checks to clear variables when end of section detected

use chr(39) to resolve any issues handling "

Good luck.

[FrankGrimm]

Hi ogster1974,

thanks 😉  It seems you are a something like a teacher 😉 This is the way to learn and don´t forget ist.

Have a nice day!

[ogster1974]

“If you give a man a fish, you feed him for a day. If you teach a man to fish, you feed him for a lifetime.” 🙂 

Good luck.