Solved

Importing third party XML data into MS Visio

Posted on 2012-04-01
11
3,548 Views
Last Modified: 2012-04-04
I type text into shapes in Visio drawings derived from Templates. Now I want to add automation,  by importing text automatically from various third party XMLs. Can I and ought I import the data fields of the 3rd party into MSOffice XML derived from my drawing template? I use Visio 2010 Premium.

This would involve a learning curve on my part. I am aware that third party XML should be established as well formed, and they will not comply with the MS schema.

Can you recommend procedures/applications whereby I can:
- Select, for each relevant data field in Visio MSOffice XML, which data field should be imported from the third party XML?
- Make a sensible shell in vba to automate the import process, so that a series of third party XML specifications of the same structure but different content can be imported into different instances of a Visio template.

If available, on the grounds of cost, freeware / vba code would be an attractive first step, to gain a working model in the first instance. However, a commercial application would be of interest if it can be used 'out of the box'.

Thanks

Kelvin4
0
Comment
Question by:Kelvin4
  • 5
  • 5
11 Comments
 

Author Comment

by:Kelvin4
ID: 37800131
Thanks for your advice:

-Sample of third Party XML is attached in the file. Reference to XML Schema is included in <MyDoc>.  I had thought that XML validation would be carried out in Excel. The size of the third party XML file could be much larger than the demo of 28 lines. For example there are four elements: "Text1 identifier" ... "Text4 identifier", and could be many more. But the overall complexity of the file is not great.

- Visio MSOffice XML  I don’t have a sample of this run off and annotated at present. Well over 95% of the data in the Visio XML file will not be copied from the third party XML, but generated de novo from within the Visio file.

- DATA IMPORTED from Third Party file will be Text and numbers, but mainly text.

Text is not currently limited in length, but I don’t anticipate lengths in excess of 1500 characters.

Some text will contain HTML tags. I can parse those tags so that the formatting they specify is viewable in Visio.shape.text, by creating the appropriate data in the shape character file (in the shapesheet character section).

I hope this is useful information.
Thanks,
Kelvin
Sample-xml-for-EE-04032012.docx
0
 
LVL 45

Expert Comment

by:aikimark
ID: 37800352
I looked at the XML in the Word document, using XML Notepad (a handy free tool from Microsoft).

I had to clean it up in a few places, specifically:
* Word smart quotes used instead of Chr(34) ASCII quote characters a couple of times
* Missing space before xsi:schemaLocation= and method1=
* space in closing tag </rInput> between the slash and the "r"

Cleaned up version of the code:
<MyDoc xmlns="Thisxmlns" xmlns:xsi="ThisXMLSchema-instance" xsi:schemaLocation="ThisSchema.xsd" identifier="MyID" title="MyTitle" method1="false" time="false">
  <rInput identifier="myInput" type="single" baseType="identifier">
    <cInput>
      <value>TextZ</value>
    </cInput>
  </rInput>
  <output identifier="myOutput" type="single" dataType="integer">
    <defaultValue>
      <value>0</value>
    </defaultValue>
  </output>
  <docBody>
    <p>Look at the text in the picture.</p>
    <p>
      <img src="images/myGraphic.png" alt="image title" />
    </p>
    <theTexts responseIdentifier="RESPONSE" param1="true" param2="1">
      <prompt>What does it say?</prompt>
      <Text1 identifier="myTextIdentifier1">MyText1</Text1>
      <Text2 identifier="myTextIdentifier2">MyText2</Text2>
      <Text3 identifier="myTextIdentifier3">MyText3</Text3>
      <Text4 identifier="myTextIdentifier4">MyText4</Text4>
    </theTexts>
  </docBody>
  <VisioProcessing template="ThisVisioTemplate" />
</MyDoc>

Open in new window


If you are editing this XML, use XML Notepad, Notepad, or some editor that will will not introduce changes that invalidate/malform the XML.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 37800373
Once the XML was 'cleaned' I was able to open it with Excel without any problems.

I would like to see the XML without your editing it.  Please upload your XML file.
0
 

Author Comment

by:Kelvin4
ID: 37803601
Thank you for the trouble taken; my effort to simplify the text has been unhelpful. Attached is the original, but I added spaces as you did, as XML Notepad reported the lack of spaces that you identified.

XML Notepad also reported the use of incorrect quote marks, but I could not identify them.

The material comes from the qti standards for setting computer-based questions, and is a useful place to start experimenting with the import of third party XML.

I hope this is helpful
Thanks!
EE-XML-unedited-example-corrects.txt
0
 
LVL 45

Expert Comment

by:aikimark
ID: 37804180
The vendor is producing malformed XML.  The sample you posted contained the following incorrect closing tag:
</ responseDeclaration>

Open in new window


Such errors can be easily fixed by some simple editing (manual or programmatic), replacing all "</ " with "</" until no more are found in the file.

I didn't get any other error messages when opening the file with XML Notepad.  It did take a long time to open the file because of the xmlns: links.  I assume your open times are much faster.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:Kelvin4
ID: 37804523
Yrs, thanks, the corrected XML file opened immediately in XML Notepad.
0
 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
ID: 37804539
You should contact the vendor and let them know of this problem.
0
 

Author Comment

by:Kelvin4
ID: 37804843
Thanks. You have looked at my files, corrected them, got me started  with XML Notepad, and aware of malformed third party XML. I will sign this question off as Solved and ask a second question, to address the problem of importing selected well-formed third party XML data into certain data fields of MS Office XML for Visio.

I have run off some XML from a small visio test file, with one data field of interest. The XML data field into which I want to export third party data is visible in XML NotePad.  

If I forward these in a new question, would you be in a position to help me tackle the data transfer method?

Kelvin
0
 

Author Closing Comment

by:Kelvin4
ID: 37804847
Thanks for the start. Kelvin
0
 
LVL 45

Expert Comment

by:aikimark
ID: 37805021
@Kelvin

>>If I forward these in a new question...
I'm currently monitoring http:/Q_27661631.htm
However, I don't have Visio, so I can't help you as a subject matter expert with that part of your problem.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

MS Access 2003 or later To MySQL Migration Project Hello All, this is my second article in the category of MS-OFFICE Automation. In internet I am not able to find any comprehensive resource on the Migration of MS Access back-end to MySQL so I fin…
Many times as a report developer I've been asked to display normalized data such as three rows with values Jack, Joe, and Bob as a single comma-separated string such as 'Jack, Joe, Bob', and vice versa.  Here's how to do it. 
Viewers will learn the different options available in the Backstage view in Excel 2013.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now