Transforming XML to HTML in Access with VBA

[Joyce]

Oke, to start off I'm pretty much a beginner when in comes to VBA. For a
project I got an XML file that just won't import correctly into Access, it
leaves out columns and or data. My predecessor solved this with a workaround
by importing the XML into Excel, doing some data/text changes and then
importing the xls into Access, and also importing the XML into Access and
then combining both with a query.

I figured there had to be an easier way and ended up making a XML
transfersheet to convert the XML into HTML, putting the data in the correct
order and already renaming the headings. So far so good. I just don't want to
manually convert every XML (they are invoices we receive) we get. Then I
found this website:
http://www.oreillynet.com/xml/blog/2005/04/transforming_xml_in_microsoft.html
and now I'm stuck.

I've gotten this far:

Private Sub Transform(sourceFile, stylesheetFile, resultFile)

Dim source As New MSXML2.DOMDocument30
Dim stylesheet As New MSXML2.DOMDocument30
Dim result As New MSXML2.DOMDocument30

' Load data.
source.async = False
source.Load "Y:\Staff\ufb\AC\DataExchange\XML\Essent\in te lezen\XML_Essent.
xml"

' Load style sheet.
stylesheet.async = False
stylesheet.Load "Y:\Staff\ufb\AC\DataExchange\XML\Essent\in te lezen\
transform.xsl"

If (source.parseError.errorCode <> 0) Then
MsgBox ("Error loading source document: " & source.parseError.reason)
Else
If (stylesheet.parseError.errorCode <> 0) Then
MsgBox ("Error loading stylesheet document: " & stylesheet.parseError.
reason)
Else
' Do the transform.
source.transformNodeToObject stylesheet, result
result.Save "Y:\Staff\ufb\AC\DataExchange\XML\Essent\in te lezen\
importeren.htm"
End If
End If

End Sub

and calling the transform:

Transform "Y:\Staff\ufb\AC\DataExchange\XML\Essent\in te lezen\XML_Essent.
xml", _
"Y:\Staff\ufb\AC\DataExchange\XML\Essent\in te lezen\transform.xsl", _
"Y:\Staff\ufb\AC\DataExchange\XML\Essent\in te lezen\importeren.htm"

DoCmd.TransferText SpecificationName:="acimporthtml", TableName:="TY_OUTPUT",
_
FileName:="Y:\Staff\ufb\AC\DataExchange\XML\Essent\in te lezen\importeren.
htm", HasFieldNames:=True

But it doesn't reckognize the Transform as a Sub and I don't have enough
knowledge to solve this. Hope someone can help me with this.

Regards, Joyce

 

[namliam]

I figured there had to be an easier way and ended up making a XML
transfersheet to convert the XML into HTML

Why not convert it to a csv? or something simular? Would be much easier

When your posting code please use the code wraps or the # button on the top of the post bar.

Private Sub Transform(sourceFile, stylesheetFile, resultFile)

Dim source As New MSXML2.DOMDocument30
Dim stylesheet As New MSXML2.DOMDocument30
Dim result As New MSXML2.DOMDocument30

' Load data.
source.async = False
source.Load "Y:\Staff\ufb\AC\DataExchange\XML\Essent\in te lezen\XML_Essent.xml"

' Load style sheet.
stylesheet.async = False
stylesheet.Load "Y:\Staff\ufb\AC\DataExchange\XML\Essent\in te lezen\transform.xsl"

If (source.parseError.errorCode <> 0) Then
  MsgBox ("Error loading source document: " & source.parseError.reason)
  Else
If (stylesheet.parseError.errorCode <> 0) Then
     MsgBox ("Error loading stylesheet document: " & stylesheet.parseError.reason)
  Else
     ' Do the transform.
     source.transformNodeToObject stylesheet, result
     result.Save "Y:\Staff\ufb\AC\DataExchange\XML\Essent\in te lezen\importeren.htm"
End If
End If

End Sub

And save yourself a headache, INDENT...

If (source.parseError.errorCode <> 0) Then
    MsgBox ("Error loading source document: " & source.parseError.reason)
Else
    If (stylesheet.parseError.errorCode <> 0) Then
        MsgBox ("Error loading stylesheet document: " & stylesheet.parseError.reason)
    Else
        ' Do the transform.
        source.transformNodeToObject stylesheet, result
        result.Save "Y:\Staff\ufb\AC\DataExchange\XML\Essent\in te lezen\importeren.htm"
    End If
End If

But it doesn't reckognize the Transform as a Sub and I don't have enough
knowledge to solve this. Hope someone can help me with this.

Regards, Joyce

It may have to do with the key word "Private" as that makes the Sub PRIVATE to the module/object it is in, try changing it to "Public"... that may just resolve your issue.

P.S. Groeten uit Amsterdam

 

[Banana]

Why not use Access' native method:

ImportXML, ExportXML and TransformXML. You should be able to use Access' TransformXML to change XML into a structure that Access can then parse correctly.

I'm unclear on how the HTML fits in this since we're importing the data into Access and it should be able to read the XML as an input - the trick is that the XSLT file needs to transform into the table's names and nest it properly so when you run ImportXML with append option, it will load all data from XML into right tables where the table's name match the containing nodes.

 

[Joyce]

Why not use Access' native method:

ImportXML, ExportXML and TransformXML. You should be able to use Access' TransformXML to change XML into a structure that Access can then parse correctly.

I'm unclear on how the HTML fits in this since we're importing the data into Access and it should be able to read the XML as an input - the trick is that the XSLT file needs to transform into the table's names and nest it properly so when you run ImportXML with append option, it will load all data from XML into right tables where the table's name match the containing nodes.

Well, the reason why i'm not just importing the XML is cause it's not working. I've tried importing it without any tables present and with a table present with the same structure as the XML, in both imports Access didn't import all of the data, some columns weren't imported at all. Opening the same XML in Excel is no problem, all the data is present.

That is why i tried to transform it to HTML, i could also easily transform it to scv with the transform script. The problem is that after transforming it with xls, it still has an .xml format and I don't want to rename / save as it manually every time. There must be some way to do this in VBA, I just haven't figured it out yet.

 

[Joyce]

It may have to do with the key word "Private" as that makes the Sub PRIVATE to the module/object it is in, try changing it to "Public"... that may just resolve your issue.

P.S. Groeten uit Amsterdam

Groeten uit Assen terug.

I tried making it Public, but still, in the second part it doesn't reckognize Transform as anything. Keeps asking for a macro when trying to run it. I guess I have to dive into the VBA more to get this working.

 

[DCrake]

Where have you save the function Transform? Hopefully as a public function in a standard module not named Transform.

Also you could try

Call Transform(a,b,c)

Where a,b & c are your arguments.

 

[namliam]

Is the transform function in the same database(file)? as that may cause issues if it isnt.

Also to prevent issues of reserved words it may be wize to call it FN_Transform or somehting.

If it loads properly into excel, it should be 'easy' to call excel, open file, manipulate it a bit, save as csv and then import into access all in one go from access wihtout manual intervention.

A(nother) alternative is to process the XML as a plain text file, making your own code to find and extract data out of the text file.
XML is more hassle than its worth really, why not simply use csv? It is in 99% of all cases just as good, except it dont look as fancy smancy.

 

[namliam]

Groeten uit Assen terug.

Was thinking of offering my services, except I dont fancy driving 2 hours on an every day basis there AND back

 

[Joyce]

Actually I work in Groningen, so that wouldl be 2,5 hours.

I put a test database here:

http://www.joycewolthuis.nl/wp-content/uploads/2010/05/test.mdb

let me explain the purpose of this database. Every month we receive energy invoices from Essent, about 80 to a 100 each month. Combined in one XML form. This has to be imported into Access so we can process them. The end-users have little knowledgde of Access, all they know is what button to press when.

My predecessor obviously found out the same problem I did, when using XML import (both GUI and VBA) somehow the XML structure doesn't get imported entirely, he just doesn't see some columns. So, she tried Excel with didn't work cause some numbers are to large and Excel butchers them. Her work-around was combining an excel import with an xml import, using a key to form the document in the end. (dates from american notation (with left, mid and right) to european, replacing . with , etc).

I figured there must be a faster way and looking into transforming xml I somehow ended up with html (don't ask me why I didn't go for transforming csv). Now when linking the xml to the xslt my internet browers have no problem reading the XML as HTML, but when importing it into Access it goes all wrong. Either with putting in the transform in the GUI or just leaving it linked into the XML.

What I need now is for Access to open the XML, transform it using the xslt and then saving the files as html (or csv for that matter) and then importing that saved file into a table.

I hope I'm making sense here.

 

[namliam]

Actually I work in Groningen, so that wouldl be 2,5 hours.

Ugh, nevermind

(dates from american notation (with left, mid and right) to european, replacing . with , etc).

you know offcourse that access is an american product and is best suited to work with .... *badam bam* american dates
No need to replace anything if the input is american, the reason most people actually run into problems IS trying to work with euro dates

What I need now is for Access to open the XML, transform it using the xslt and then saving the files as html (or csv for that matter) and then importing that saved file into a table.

I hope I'm making sense here.

xslt is Excel offcourse, why utilize a outside source when access itself can do it just fine (even if with a little help )
The "best" format of xml would be somthing like so:
<name>namliam</name>
<Lastname>namliam</Lastname>
<City>namliam</City>
<Workplace>Zeker geen groningen</Workplace>
Nicely one field/column per line

A worse format would be
<name>namliam</name><Lastname>namliam</Lastname><City>namliam</City><Workplace>Zeker geen groningen</Workplace>
All of one record in one line

Or just the entire file in one line would be the most awfull one.

Any of the 3 versions though you can (and should IMHO) 'simply' open as a text file, find your values you need, pick them up and load into your table(s).
This way you have FULL control, no potential screw ups by excel 'working for you' changing numbers and what not... Just loading from the textfile and only access thats 100% under control and in your hands.

This I know is not what your asking, but this is what I think is (ultimatly) the best solution, though at the cost of some extra work.

 

[Joyce]

XSLT is not Excel, its a css for XML files. Supposedly in Access 2010 you can even transform an XML in the GUI with a XSLT, but that doesn't work.

The date notation is like this: YYYYMMDD, Access hasn't been able to define that as DDMMYYYY.

But how do I do this simply:

'simply' open as a text file, find your values you need, pick them up and load into your table(s).

it might be worth mentioning that I'm blonde

 

[chergh]

xslt is Excel offcourse

I think you are getting confused as far as I know xslt is nothing to do with excel but is used to translate xml documents between different schemas.

 

[namliam]

XSLT is not Excel, its a css for XML files. Supposedly in Access 2010 you can even transform an XML in the GUI with a XSLT, but that doesn't work.

Oh OK...

As you may have guessed I am not a fan of XMLs

The date notation is like this: YYYYMMDD, Access hasn't been able to define that as DDMMYYYY.

No you have to code that, something like:
Dateserial ( Left(yourtext,4), mid(yourtext,5,2), right(yourtext,2)

But how do I do this simply:

it might be worth mentioning that I'm blonde

this solution is best when your file looks like Option 1.
Look into the Open and Input line functions of VBA, read the help and see if you can read the text file.

After that its a basic IF THEN ELSE type thing with some MID to extract all the data and a Recordset (look that up) to store it to your table.

Wont be the fastest ever, but will be 100% controlable and 100% readable to get it right.

 

[Banana]

Joyce -

I'm curious because I've been using ImportXML, ExportXML and TransformXML for processing SOAP messages just fine... Do you have a test XML file I could look at and see what's happening?

 

[Joyce]

Joyce -

I'm curious because I've been using ImportXML, ExportXML and TransformXML for processing SOAP messages just fine... Do you have a test XML file I could look at and see what's happening?

well, thats going to be a problem since the file contains confidential data. It contains invoices with and one XML file contains around 80 invoices. In total an invoice can have 51 elements, but not all invoices do, some have only 20 or 30, depending on what is being invoiced. When importing the xml into access some elements are not imported. In the original XML these elements usually don't show up until halfway the document. When just copying that invoice that contains data in that element to a different xml file it has no problem importing that element, but when the invoice is part of the entire xml the element doesn't get imported. Its like it somehow doesn't reckognize the structure when the first time an element is mentioned is further along in the document, instead of in the beginning. Hmm, its still early, I hope that makes sense.

Anyway I have a table with all the element names, importing the XML into that table just leaves the elements without data. The good thing about all of this is that I'm at least learning a lot

 

[Banana]

Cool - good for me to know, too. In my case, I've always built the tables for exporting/importing and just appending/extracting so I know there's no elements to be missed. Just didn't trust the "create a new table when importing" options.

Hopefully you've now sorted the process now?

 

[Joyce]

so you build a script around the importxml per element?

 

[Banana]

No. I just call TransformXML/ImportXML (Append to tables) or ExportXML/TransformXML. XSLT handles the rest.

For exporting, I use a temporary database with temporary tables which has relationships defined. That way, ExportXML will emit a properly nested XML and thus less work for me in writing the XSLT to nest the elements. AFAIK, it will emit empty elements as "<element />". Of course, I could just ExportXML using queries but that always makes a flat XML which is okay in some cases but I use temporary tables for more complicated XMLs. ImportXML is always specified to be append to tables and never create new tables. So, empty or missing elements are inserted as a null in the table.

HTH.

 

[Joyce]

omg! how simple can it be.

I love you!

Function vertalen()

' xml vertalen naar html dmv xsl
TransformXML "Y:\Staff\ufb\AC\DataExchange\XML\Essent\in te lezen\XML_Essent.xml", _
"Y:\Staff\ufb\AC\DataExchange\XML\Essent\in te lezen\transform_to_html.xsl", _
"Y:\Staff\ufb\AC\DataExchange\XML\Essent\in te lezen\transform.html"

End Function

Sub importeren()

'importeren van html
DoCmd.TransferText acImportHTML, "Transform Import Specification", "facturen_huidige_maand", "Y:\Staff\ufb\AC\DataExchange\XML\Essent\in te lezen\transform.html", True

End Sub

Its working

 

[Banana]

I'm glad you got it to work, but why the acImportHTML? Why not just Transform then Import? If you use the XSLT to change the containing node, it'll append the data to the previously created tables without missing element (columns)?