Import XML (1 Viewer)

[Fooddeveloper]

Hello,

I use Access 2016 and try to import a XML-file with the command

Application.ImportXML DataSource:="N8E1853 - REPORT - Genetic lifestyle analysis.xml", ImportOptions:=acStructureAndData

.

The file starts with

<?xml version="1.0" encoding="utf-8"?>
<book>
<section>

but I get an error message "Runtimeerror '-2146697203 (800c000d)': The specified protokoll is not known."

Do I have to insert "some more" or a different header?

best regards from Austria
Martin

 

[Gasman]

Have you got the library included in References?

 

[arnelgp]

you post the Whole structure of the file.

shouldn't you be using acAppendData instead of acStructureAndData?

 

[Fooddeveloper]

Have you got the library included in References?

 

[Fooddeveloper]

Yes: "Microsoft XML, v6.0" is referenced.

 

[Fooddeveloper]

you post the Whole structure of the file.

shouldn't you be using acAppendData instead of acStructureAndData?

 

[sonic8]

Application.ImportXML DataSource:="N8E1853 - REPORT - Genetic lifestyle analysis.xml", ImportOptions:=acStructureAndData

.
[...]
but I get an error message "Runtimeerror '-2146697203 (800c000d)': The specified protokoll is not known."

Use the full file name to the XML file, including the path.

 

[Fooddeveloper]

The first lines of the structure:

<?xml version="1.0" encoding="utf-8"?>
<book>
<section>
  <name>GENERAL - Letter Module - DNA4ME</name>
  <code>LETTER_UK3JD | LETTER</code>
  <tokens>
    <token type="pageHeading">
      <text>ANSCHREIBEN</text>
    </token>
    <token type="text">
      <text>Sehr geehrte Frau  xxx,</text>
      <font>regular</font>
      <size>18</size>
    </token>
    <token type="text">
      <text>Ihre Probe für die Analyse ist am 12/06/2018 bei uns im Labor eingetroffen und wurde anschließend nach höchsten Labor-Qualitätsstandards untersucht. Die Ergebnisse wurden anschließend von zwei unabhängigen Genetikern und Molekularbiologen ausgewertet und freigegeben. Nach der Freigabe wurde Ihr persönlicher Bericht individuell für Sie zusammengestellt. Diesen möchten wir Ihnen hiermit in der gewünschten Form übermitteln.</text>
      <font>regular</font>
      <size>12</size>
    </token>
    <token type="text">
      <text>Wir bedanken uns herzlich für Ihr Vertrauen und hoffen, dass Sie mit unserem Service zufrieden sind. Wir freuen uns über Ihre Fragen und Anregungen, denn nur so können wir unseren Service kontinuierlich verbessern.</text>
      <font>regular</font>
      <size>12</size>
    </token>
    <token type="text">
      <text>Wir hoffen, die Analyse erfüllt Ihre Erwartungen.</text>
      <font>regular</font>
      <size>12</size>
    </token>
    <token type="text">
      <text>Mit freundlichen Grüßen</text>
      <font>regular</font>
      <size>12</size>
    </token>
    <token type="imageWithLabel">
      <name>Signature new.jpg</name>
      <override></override>
      <height>16</height>
      <width>55.65217391304348</width>
      <rotation>0</rotation>
      <labels>
        <label>
          <coordinateX>24.99999999999999</coordinateX>
          <coordinateY>181.1458333333333</coordinateY>
          <content>Dr. Daniel Wallerstorfer BSc.<br>Labordirektor<br>Novogenia GmbH</content>
          <textColor> #000000</textColor>
          <fontSize> 11</fontSize>
          <fontCode>regular</fontCode>
          <cellWidth>0</cellWidth>
          <alignment>L</alignment>
        </label>
      </labels>
      <link></link>
      <posX></posX>
      <posY></posY>
      <alignment></alignment>
    </token>
    <token type="imageWithLabel">
      <name>SignaturRohrmanstorfer.png</name>
      <override></override>
      <height>16</height>
      <width>37.73913043478261</width>
      <rotation>0</rotation>
      <labels>
        <label>
          <coordinateX>84</coordinateX>
          <coordinateY>181.1458333333333</coordinateY>
          <content> yyy xxx, MSc.<br>Laborleiter<br>Novogenia GmbH</content>
          <textColor> #000000</textColor>
          <fontSize> 11</fontSize>
          <fontCode>regular</fontCode>
          <cellWidth>0</cellWidth>
          <alignment>L</alignment>
        </label>
      </labels>
      <link></link>
      <posX></posX>
      <posY></posY>
      <alignment></alignment>
    </token>
    <token type="imageWithLabel">
      <name>Signaturexxx.png</name>
      <override></override>
      <height>16</height>
      <width>55.02645502645503</width>
      <rotation>0</rotation>
      <labels>
        <label>
          <coordinateX>143</coordinateX>
          <coordinateY>181.1458333333333</coordinateY>
          <content>Dr. asdf asdf<br>Geschäftsführer<br>asdf GmbH</content>
          <textColor> #000000 </textColor>
          <fontSize> 11</fontSize>
          <fontCode>regular</fontCode>
          <cellWidth>0</cellWidth>
          <alignment>L</alignment>
        </label>
      </labels>
      <link></link>
      <posX></posX>
      <posY></posY>
      <alignment></alignment>
    </token>
  </tokens>
</section>
<section>
  <name>REPORT - LIFESTYLE - Details and Chapter toc</name>
  ...
</book>

As the whole file consists of app 69000 lines I can´t copy all of them into here.

And as I don´t know the structure of the table I wanted to let it build. That´s why acStructureAndData ...

 

[Fooddeveloper]

Use the full file name to the XML file, including the path.

Good point!

Now I get a new runtimeerror:
"
RuntimeError '31593': ~ Error on proceeding the XML-Schema in file 'N8E1853 - REPORT - Genetic lifestyle analysis.xml'.

The name contained in the item's end tag must match the item type in the start tag.
"

But I think the end tag matches:

...
<section>
  <name>GENERAL - Handwritten Notes</name>
  <code>ADDITIONALINFO_NOTES</code>
  <tokens>
    <token type="text">
      <text>NOTIZEN:</text>
      <font>medium</font>
      <size>14</size>
    </token>
  </tokens>
</section>
</book>

<book>... </book> --> thats the beginning tag and the end tag?

 

[sonic8]

The name contained in the item's end tag must match the item type in the start tag.

I'm not sure if it is the cause of this error, but your raw <br> HTML tags are not valid inside the XML. You must encode them as &lt;br&gt;.
EDIT: There are no raw HTML tags in the XML, they are encoded properly. I was mislead by something decoding them for the above post.

 

[Fooddeveloper]

Attached sample file to let you see all the data. I cleared all names and companies. I had to change the filetype from xml to txt to upload it.

 

[arnelgp]

which Program did you use to Export this xml?
probably not a valid xml (as far as access is concern).
normal xml, for access to recognize, is that the Tag don't have any attributes in them.

 

[gemma-the-husky]

Here's a different approach. I don't actually try to automate any process for xml. I just couldn't make sense of the inbuilt procedures.

I have uploaded a code sample a long while ago that walks through every node of an xml file, and produces a structure plan of the file in an access table. So, using that as a separate exercise, I then work out which fields I want to process.

Then, to do the actual import, I walk through the whole file a node at a time, and based on my previous analysis, I know how to process each node, and each record. It's very fast. A second or two for large files.

Once I develop the import process, it handles subsequent versions of this same file with no work required - but will report finding unexpected data elements and so on.

 

[Fooddeveloper]

which Program did you use to Export this xml?
probably not a valid xml (as far as access is concern).
normal xml, for access to recognize, is that the Tag don't have any attributes in them.

I get it from an external source. No idea which program they use. They are doctors and physicans, I don´t think that anyone there knows about it.

 

[arnelgp]

I get it from an external source.

based on it's structure, it is a report of some sort.
i don't think you can import that as "normal" xml file to access.
it will only be importable on the same program you export it.

 

[Gasman]

If I try in Word I get

 

[Gasman]

You can also see issue here? https://codebeautify.org/xmlviewer

 

[Fooddeveloper]

If I try in Word I get
View attachment 102441

Thank you very much for your trying and your help!

 

[gemma-the-husky]

the msxml library module has a boolean setting to determine whether the xml is valid or not. I forget the name offhand, but it's something you would always check before doing anything with an xml file normally, I would think.