Hi there,
anyone out there familiar with XML and such? I know I can import it into excel and do what i need from there but....
one of the feeds has xml inside XML that excel doesnt seem to want to play with. If anyone out there can help please let me know!
for example
Asus
<product_tech_spec><Product_ x0020_Group>Video Cards</Product_x0020_Group><Manufacturer>Asus< ;/Manufacturer><SKU>8682</SKU><Product_x0020_Name>ASUS 512MB RADEON X1900XT 2xDVI PCI-E etc etcetc
are there any apps out there that will easily read this?
Not sure about apps to read it in, XMLSpy might be worth a try if they do a free trial, looks like pretty broken XML to me though, surely if you have manufacturer as the top-level tag, you dont want to specify manufacturers as nodes underneath that?
Thats not actually XML as such, without going into full details
If your making a site purly from XML you have XML DTD definitions - basically XML then you have the XHTML Type aspect of the code that can call up the dtd and definitions and then database functionality etc.
So not sure what applications would read the other sets bar just a basic XML.
Youre more than likely going to have to write some VBA to load the XML for you. I would also guess that youre going to have to have to use nested DOM objects; if you dont know what I am on about:
http://www.thescarms.com/XML/DOMTutorial.asp
What youll need to do is use DOM1 to access the element. Youll then have to read the value of that element into DOM2. Youll then be able to access all of the elements of the embedded/second XML stream.
Alternatively, you could code a routine (or two) in VBA that does some rough XML data searching using string functions rather than the DOM. The function could take in the element name youre looking for and return the data held by that element (if any).
Althought this would break the DOM, it would provide you with a fast (and simple) method of accessing the data.
As Im a bit bored...
Option Explicit
Const XML_DATA As String = "AsusVideo CardsAsus< ;/Manufacturer>8682ASUS 512MB RADEON X1900XT 2xDVI PCI-E>"
Private Sub Form_Load()
MsgBox "Embedded: " & vbCrLf & nfsGetXMLValue("techDataXML", XML_DATA)
MsgBox "Product Tech Spec:" & vbCrLf & nfsGetXMLValue("product_tech_spec", XML_DATA)
End
End Sub
Private Function nfsGetXMLValue(ByVal vsElement As String, ByVal vsXMLStream As String) As String
-----------------------------------------------------------------------
Procedure : nfsGetXMLValue
Author : John Marden
Date Created : 01/08/2006
-----------------------------------------------------------------------
Purpose :
Notes :
Last Updated :
-----------------------------------------------------------------------
On Error GoTo Catch
Dim lsReturn As String
Dim llPos As Long
Dim llEnd As Long
lsReturn = vbNullString
If Len(vsElement) > 0 And Len(vsXMLStream) > 0 Then
Look for element, case insensitive...
llPos = InStr(1, UCase$(vsXMLStream), UCase$(vsElement))
If llPos > 0 Then
Found a match, find the end...
llEnd = InStr(llPos + Len(vsElement), UCase$(vsXMLStream), UCase$(vsElement))
Read out the data...
If llEnd > llPos Then
In the middle
lsReturn = Mid$(vsXMLStream, llPos + (Len(vsElement) + 1), (llEnd - (Len(vsElement) + 3) - llPos))
Else
Start to end...
lsReturn = Mid$(vsXMLStream, llPos + (Len(vsElement) + 1))
End If
End If
End If
Finally:
nfsGetXMLValue = Trim$(lsReturn)
Exit Function
Catch:
lsReturn = vbNullString
MsgBox "Error: " & vbCrLf & Err.Description
Resume Finally
End Function
Thats a VB function that will pull out any data based on an element name. Its a bit rough around the edges, e.g. if there is text within the stream that matches the tag name it *might* see the text and not the tag, and needs refining but its a framework...
Wow, thanks for the replies, i really wanst expecting anything helpfull on this one lol.
Ive tried XML spy as suggested and it doesnt read the nested objects.
I have NOOOOOO idea about VB and stuff like that (I remember the days of C++ programming in dos many years ago tho :D)
Nimrod. thanks for the code :D Umm where would I use it tho (embaressed to ask)
:shock:
;)
Youll need to stick the code in a Macro within Excel. Where exactley is going to depend on what you are trying to achieve.
From your initial post, I am guessing that youre trying to import an XML file into an Excel spreadsheet. Chances are you will have to write your own import routine using the Macro VBA editor.
Yep, you assume right, im just loading it into excel and then praying that it would work lol.
Its a shame that XML spy doesnt do any of this type of thing tho. Ill have a play and see what i can work out on it.
(Update)
Got the VB macro to work, now all i have to do is work out what the hell to do next. Do i then resave as an xml and work on it with a DTD From there?
I think that this might be way out of my league all this :cry:
Attach your Excel file to a post and I will, when I get a chance, take a look.
Everything has just gone a bit bonkers here; I have 23 firewalls that have just *forgotten* their configs!??!
Any objections if i take this to a PM for now as it contains a bit of sensative (oooer) information :D
crack on m8, if I can do a "generic" solution I will bang it up here ;)