RE: XML to CSV in VBScript

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

RE: XML to CSV in VBScript

Logged in as: Guest

arrSession:exec spGetSession 2,2,25624

	Active Users: There are 0 members and 0 guests.
	Users viewing this topic: none

Printable Version

All Forums >> [Scripting] >> WSH & Client Side VBScript >> RE: XML to CSV in VBScript
Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!

Page: << < prev 1 2 3 [4] 5 next > >>

Message

<< Older Topic Newer Topic >>

RE: XML to CSV in VBScript - 9/19/2005 5:35:58 PM

reloader

Posts: 72
Score: 0
Joined: 9/5/2005
Status: offline

Zifter,

It's working fine, but I don't exactly understand this. As I experienced, the SelectSingleNode is look over the whole XML document, and I want, that it look over in each Node.

In example:

<Album>
     <title>Come On Over</title>
      <protocols/>
</Album>

<Album>
     <title>Red Dirt Girl</title>
     <artist>Emmylou Harris</artist>
<protocols/>
<Year>2002</Year>
</Album>

I want to search beetween the first <Album> </Album> and after the second one, until the end of the document.
I tried NextNode but it's not working as well as it should be.

How can I solve it if it's possible?

(in reply to Zifter)

Post #: 61

RE: XML to CSV in VBScript - 9/19/2005 6:24:03 PM

Zifter

Posts: 318
Score: 0
Joined: 1/5/2005
From: Belgium
Status: offline

Please don't get me wrong. It's not that I don't want to help but I have the feeling I'm answering the same questions over and over again. Each time with a minor difference, but things I think you should be able to find out yourself.

Or you should read the documentation more attentively and make some test scripts to try everything out or you could tell me exactly what your project is, what the script has to do with an example of the real XML file and I'll write the script for you. Then you can try to understand what it does and ask questions about the provided code.

To give you a start with your last question, you can either use the selectSingleNode and build the XPaths dynamically to go through the complete tree of nodes or you could do something similar with node lists (= collections) and check within these list seach node and/or childnodes.

(in reply to reloader)

Post #: 62

RE: XML to CSV in VBScript - 9/19/2005 7:07:13 PM

reloader

Posts: 72
Score: 0
Joined: 9/5/2005
Status: offline

Okay, U are right.

I 'm writing a script, what is convert an XML file.

From:
<Album ref="CD720" category="Pop">
     <title>Come On Over</title>
      <protocols/>
</Album>

To:
<Album>
     <ref>CD720</ref>
     <category>Pop</category>
     <title>Come On Over</title>
      <protocols/>
</Album>

It's done. Now, I don't have to handle Attributes, but Nodes.
After I want a loop, that is look over in each <Album></Album> for each nodes like "ref", "category", "title", "protocols". If a node doesn't exist(for instance <Year>) , the script should be recognize it, and give me a value or a text or something.
Important. the loop have to go row by row, beacuse it will be a CSV format at the end.

Sorry for my many silly question, but I do Object-Orientated programming first time in my life, and a bit complicate me for the present.

(in reply to Zifter)

Post #: 63

RE: XML to CSV in VBScript - 9/19/2005 10:04:54 PM

Zifter

Posts: 318
Score: 0
Joined: 1/5/2005
From: Belgium
Status: offline

It is still not completely clear what you're trying to do.
Do you first want to create a node for each attribute and then convert the changed XML to a CSV? If so, why do you botter modifying this XML in the first place?
Do you just want to be warned when a certain node doesn't exist or do you want to create an empty one?

Anyway, maybe you get further with the following examples

test.XML

This example will create a new node for each attirbute.
It will create a new XML file named testNEW.xml
(if you want to replace the existing XML with the new one, you can simply use the same path as the one you read)

This example shows how you could check if a certain node exists or not

Option Explicit

Dim objXML
Dim objNode
Dim objTempNode
Dim objChildNode

Set objXML = CreateObject("Msxml2.DOMDocument.5.0")
objXML.async = False
objXML.load("C:\test\testNEW.xml")

For Each objNode In objXML.documentElement.childNodes
 Set objTempNode = objNode.selectSingleNode("ref")
 If objTempNode Is Nothing Then
   WScript.Echo "node ref is missing"
 Else
   WScript.echo "node ref exists"
 End If
 Set objTempNode = objNode.selectSingleNode("category")
 If objTempNode Is Nothing Then
   WScript.Echo "node category is missing"
 Else
   WScript.Echo "node category exists"
 End If
 Set objTempNode = objNode.selectSingleNode("title")
 If objTempNode Is Nothing Then
   WScript.Echo "node title is missing"
 Else
   WScript.Echo "node title exists"
 End If
 Set objTempNode = objNode.selectSingleNode("protocols")
 If objTempNode Is Nothing Then
   WScript.Echo "node protocols is missing"
 Else
   WScript.echo "node protocols exists"
 End If
 Set objTempNode = Nothing
 WScript.Echo "-----------------------------"
Next

Set objXML = Nothing

These are just examples. There are other ways of achieving the same results. I'm just trying to help you creating your own script.

HTH

< Message edited by Zifter -- 9/19/2005 10:10:00 PM >

(in reply to reloader)

Post #: 64

RE: XML to CSV in VBScript - 9/19/2005 10:43:24 PM

reloader

Posts: 72
Score: 0
Joined: 9/5/2005
Status: offline

Zifter,

First of all, thank u very much.

Okay, I try to explain, why do I need for these codes.

1. Create a new - where are only nodes - XML from an old one, where are Attributes for some Node, beacause I can handle simplier than with Attr's.
2. Your code above need because my old XML file is a bit unstructural:( I show u a detail:

<sid id="11138">
<description>Cyrus IMAPD Multiple Remote Unspecified Vulnerabilities</description>
</bid>
<sid id="6282" guid="A9CA7FC73F474878877FA1F6F89AA083" version="1012" versionSerial="1172">
<description>cPanel guestbook.cgi Command Execution</description>
<os>Windows</os>
</bid>

U see, there are some different attributes, and some different nodes.

3. With your code I can give a ";" where the node does not exist. It need becasue of the Comma Seperated Format, otherwise some field value will be not in the right field.
4. In this step I have to solve the encoding because of the ISO-8859-1 encoding system(old XML File).
5. The script have to create the CSV file(it's simple with your codes).

(in reply to Zifter)

Post #: 65

RE: XML to CSV in VBScript - 9/20/2005 1:27:03 AM

Zifter

Posts: 318
Score: 0
Joined: 1/5/2005
From: Belgium
Status: offline

All you are trying to do can be specified in a XSL and then you just transform your XML with your XSL to a CSV (need some more abbreviations ?)
No need for a script that changes attributes to nodes or prompts when nodes are empty or missing, build your own CSV file, ... re-inventing the wheel Just use a XSL!

mconnelly already suggested this method in the third post of this topic.

Unfortunatelly I'm far from a XSL(T) expert. And it's been a long time since I used them, so maybe somebody else can help you further with this ?

<edit>
I experimented a little and this is what I came up with.
I think this should be a good start for your XML tranformation.

XML (test.xml)


XSL (test.xsl)


Script


This script will create a file test.csv

</edit>

HTH

< Message edited by Zifter -- 9/20/2005 2:02:14 AM >

(in reply to reloader)

Post #: 66

RE: XML to CSV in VBScript - 9/20/2005 5:47:59 PM

reloader

Posts: 72
Score: 0
Joined: 9/5/2005
Status: offline

Hi Zifter,

I ran your script, but the result( the CSV file) was thje same as the XSL file.

...........
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text" />
<xsl:template match="/">
<xsl:for-each select="//Albums/Album">
<xsl:value-of select="@ref" />;<xsl:value-of select="@category" />;<xsl:value-of select="title" />;<xsl:value-of select="protocols" />;
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>
...........
This is the test.csv :( So the script only can copy the XSL as CSV.

Does it work for u?

(in reply to Zifter)

Post #: 67

RE: XML to CSV in VBScript - 9/20/2005 7:23:17 PM

Zifter

Posts: 318
Score: 0
Joined: 1/5/2005
From: Belgium
Status: offline

That's strange. I did indeed test the script and I got a CSV file, which opened in Excel when I double clicked it and the data was displayed nicely in columns.

Did you do any modifications to the script file (except for the paths to the files) or changed anything in the XML or XSL?

I tested it again and I got the following as CSV file

(in reply to reloader)

Post #: 68

RE: XML to CSV in VBScript - 9/21/2005 7:04:10 AM

reloader

Posts: 72
Score: 0
Joined: 9/5/2005
Status: offline

Hi Zifter,

No, I don't change anything. Otherwise I don't trust in the XSL. I think it's too manual. Now, I'm working with an automatic parser, where u don't need to give the Attr or Nodenames, and no need for stylesheet. So the script create the CSV file by own. It's important because the script will be in a batch file, what is a job and run generally, and I don't know, that in the XML's when are the datas changed like nodenames or attrs.

(in reply to Zifter)

Post #: 69

RE: XML to CSV in VBScript - 9/21/2005 8:45:17 PM

reloader

Posts: 72
Score: 0
Joined: 9/5/2005
Status: offline

Hi,

How can I do, that the script get all attrs in all Nodes and Childnodes (while there is childnodes) in a loop?
I tried haschildnodes in loop but it's not working as good as I want. When I want to get the attributes of the last childnode, an error message is appeared: Object Not a Collection. . .

(in reply to reloader)

Post #: 70

RE: XML to CSV in VBScript - 9/21/2005 10:13:52 PM

reloader

Posts: 72
Score: 0
Joined: 9/5/2005
Status: offline

So, my code is:

Option Explicit
Dim objXMLDoc
Dim NodeList

Dim Node

Dim ChildNode, CCNodes

Dim NodeAttr

Set objXMLDoc = CreateObject("Microsoft.XMLDOM")

objXMLDoc.async = False

objXMLDoc.load("P:\audits2.xml")

For Each Node In objXMLDoc.documentElement.childNodes

   For Each NodeAttr In Node.Attributes

      WScript.Echo NodeAttr.Name & ": " & NodeAttr.Value

   Next

   For Each ChildNode In Node.ChildNodes

      WScript.Echo ChildNode.NodeName & ": " & ChildNode.Text

      For Each NodeAttr In ChildNode.Attributes

         WScript.Echo NodeAttr.Name & ": " & NodeAttr.Value

     Next

      For Each CCNodes In ChildNode.ChildNodes
         For Each NodeAttr In CCNodes.Attributes
             WScript.Echo NodeAttr.Name & ": " & NodeAttr.Value
         Next
     Next

   Next

WScript.Echo "-------------"
Next

And the script is stop in the red marked line, because Object not a collection,and I don't know, why? I declared noly, that it should read in the Attributes of CCNodes.

(in reply to reloader)

Post #: 71

RE: XML to CSV in VBScript - 9/22/2005 1:51:52 AM

Zifter

Posts: 318
Score: 0
Joined: 1/5/2005
From: Belgium
Status: offline

For some strange reason, if you move deeper and deeper into an XML tree by handling each node's childs, you end up with the text level.

Example:



This last one isn't exactly a node and that is where your problems begin.
If you ask the attributes of this last 'node' you get "Nothing" returned, while if you ask for the attributes of a 'normal node' and that node doesn't have any attributes, you still get a collection returned but with a length of 0.
You then do a For Each on an object which is nothing, so that is why you get that error message.

You can solve this by first testing if the returned object is Nothing. If it is not nothing, you check if the length is 0 and if it is not zero then you do your For Each loop.
Like this:


the same for the attributes



One other remark; If you want the children of the children of the children of the ... you better write a recursive function in stead of defining all those For Each loops.
That way you don't have to care about how many levels deep your XML goes, you just handle them all (and you'll end up with much shorter,neater code).

(in reply to reloader)

Post #: 72

RE: XML to CSV in VBScript - 9/22/2005 5:49:21 PM

reloader

Posts: 72
Score: 0
Joined: 9/5/2005
Status: offline

Thx Zifter,

Yes, I thought should be check attrs and nodes before the loop. Thanks again.

For your other question. Yes, u are absolutely right. I should be write a recursive function, because it's a not a too dynamical solution, and I will do that, but first of all I have to do this as soon as I can, because of my boss.

(in reply to Zifter)

Post #: 73

RE: XML to CSV in VBScript - 9/23/2005 12:01:51 AM

reloader

Posts: 72
Score: 0
Joined: 9/5/2005
Status: offline

Zifter,

Could u help me in this recursive function? I know, what u are thinking about, and I'm trying, but I'm not too successful in this.

(in reply to reloader)

Post #: 74

RE: XML to CSV in VBScript - 9/23/2005 12:30:03 AM

Zifter

Posts: 318
Score: 0
Joined: 1/5/2005
From: Belgium
Status: offline

Something like this:

Option Explicit

Dim objXMLDoc

Set objXMLdoc = CreateObject("Msxml2.DOMDocument.5.0")
objXMLDoc.async = False 
objXMLDoc.load("C:\test\test.xml")

fnEchoChildsAndAttributes objXMLDoc.documentElement

Set objXMLDoc = Nothing

Function fnEchoChildsAndAttributes(objTempNode)
 Dim objTempChildNode
 Dim objTempAttribute

If Not objTempNode.ChildNodes Is Nothing Then
   If Not objTempNode.ChildNodes.Length = 0 Then
     For Each objTempChildNode In objTempNode.ChildNodes
       If IsNull(objTempChildNode.NodeValue) Then
         WScript.Echo objTempChildNode.NodeName
       Else
         WScript.Echo objTempChildNode.NodeName & ": " & objTempChildNode.NodeValue
       End If
       fnEchoChildsAndAttributes(objTempChildNode)
     Next
   End If
 End If
 If Not objTempNode.Attributes Is Nothing Then
   If Not objTempNode.Attributes.Length = 0 Then  
     For Each objTempAttribute In objTempNode.Attributes
       WScript.Echo objTempAttribute.Name & ": " & objTempAttribute.Value
     Next
   End If
 End If
End Function

You will need to fine-tune this script to your requirements.

HTH

(in reply to reloader)

Post #: 75

RE: XML to CSV in VBScript - 9/23/2005 1:03:11 AM

reloader

Posts: 72
Score: 0
Joined: 9/5/2005
Status: offline

Thanks u very much Zifter,

My last problem, that how can I do that this function run for every (Main)Node what I want?

For instance:

<Node>
.
.
.Here runs the function
.
.

</Node>
<Node>
.
.
.Here runs the function
.
.

</Node>

and so on until the and of XML. . .

As in your previous code(where nodes and attrs have cheked in each First node(documentElement.childNodes ) and after was seperated by "--------------"):

Option Explicit

Dim objXMLDoc
Dim NodeList
Dim Node
Dim ChildNode
Dim NodeAttr

Set objXMLDoc = CreateObject("Microsoft.XMLDOM")
objXMLDoc.async = False
objXMLDoc.load("C:\test\States.xml")

For Each Node In objXMLDoc.documentElement.childNodes
For Each NodeAttr In Node.Attributes
WScript.Echo NodeAttr.Name & ": " & NodeAttr.Value
Next
For Each ChildNode In Node.ChildNodes
WScript.Echo ChildNode.NodeName & ": " & ChildNode.Text
Next
WScript.Echo "-------------"
Next

(in reply to Zifter)

Post #: 76

RE: XML to CSV in VBScript - 9/23/2005 3:10:48 AM

DiGiTAL.SkReAM

Posts: 1140
Score: 6
Joined: 9/6/2005
From: Florida, USA
Status: online

Wow.

I am really impressed by your patience and willingness to go the extra 15 miles to help out a stranger, Zifter.

No sarcasm, irony, or joking around on my part. Just honest respect.

I don't think... no, I *know* that I couldn't stick with it this long.

_____________________________

"Would you like to touch my monkey?" - Dieter (Mike Meyers)

"It is better to die like a tiger, than to live like a pussy."
-Master Wong, from Balls of Fury

(in reply to reloader)

Post #: 77

RE: XML to CSV in VBScript - 9/23/2005 4:08:40 AM

reloader

Posts: 72
Score: 0
Joined: 9/5/2005
Status: offline

Hi D. S.

Zifter is really in the theme. I hvae no books, no any useful documentation(or just some part), contrary to Zifter himself. I think he is very patient too, luckily.
It's a bit sticky, that I'm asking (forever), but hopefully the others can learn as much as I during this discussion:)

(in reply to DiGiTAL.SkReAM)

Post #: 78

RE: XML to CSV in VBScript - 9/23/2005 7:10:13 PM

reloader

Posts: 72
Score: 0
Joined: 9/5/2005
Status: offline

Okay,

No any porblem. I solved it.

(in reply to reloader)

Post #: 79

RE: XML to CSV in VBScript - 9/23/2005 7:53:00 PM

Snipah

Posts: 1341
Score: 6
Joined: 11/1/2004
From: Netherlands
Status: offline

Hiya Reloader,

Please share with us the complete resolution that you have...

include the XML file where you run the code on, the code itself, and the output, please....

that would be a nice closure of this thread....

_____________________________

For more information, please see the "Read me First" topic.

http://www.visualbasicscript.com

(in reply to reloader)

Post #: 80

Page: << < prev 1 2 3 [4] 5 next > >>

If you found our site useful please link to us <a href="http://www.visualbasicscript.com">VisualBasicScript.com</a>.

All Forums >> [Scripting] >> WSH & Client Side VBScript >> RE: XML to CSV in VBScript

Page: << < prev 1 2 3 [4] 5 next > >>

Jump to:

New Messages	No New Messages
Hot Topic w/ New Messages	Hot Topic w/o New Me