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/12/2005 8:06:02 PM

reloader

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

Thanks u Zifter,

It's a good code for me.
I hope, my last queestion, that how can I format my textfile, where I want to write the data?

For Each ChildNode In Node.ChildNodes
WScript.Echo ChildNode.NodeName & "; " & ChildNode.Text

If ChildNode.text="" Then wscript.echo (";")

Next

In this situtation, there will be a rowbreak and the file will contains this:

ref: F62EE0E760DA4F9FB893DED99A001A84;
const: 5144;
name;
;
displayName;
;
description; Winny;
port; 7665;
protocols; TCP UDP;

And I would like, that the file contains this:

ref: F62EE0E760DA4F9FB893DED99A001A84;const: 5144;name;;displayName;;description; Winny;port; 7665;protocols; TCP UDP

Thanks,
Attila

(in reply to Zifter)

Post #: 41

RE: XML to CSV in VBScript - 9/12/2005 8:14:16 PM

Zifter

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

Each time you use the Wscript.Echo command, the text is shown on a new line. If you want to echo it all on one line, you'll first have to add all your output to a variable and then echo that value of that variable.

Example 1:

WScript.Echo "This"
WScript.Echo " is"
WScript.Echo " a"
WScript.Echo " test"

Output:
This
is
a
test

Example 2:

strMyText = "This"
strMyText = strMyText & " is"
strMyText = strMyText & " a"
strMyText = strMyText & " test"
WScript.Echo strMyText

Output:
This is a test

Note: WScript.Echo isn't a command to write text to a file (it can, if you pipe your scripts output to a file), but if you want to write to a file, you'll need to use the FileSystemObject to open or create a file and then use the Write or WriteLine methods to put your text into this file. Check the documentation to see how to use the FileSystemObject or do a search on this forum (lots of topics)

(in reply to reloader)

Post #: 42

RE: XML to CSV in VBScript - 9/12/2005 9:59:56 PM

reloader

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

Many Thanks,

My really last question, that could u explain me, that how can I get the attributename, if I don't want to declare in code?

So: <State ref="FL" const="27">

I would like to read in varibales "ref" and "const" without, that I should declare as in it:

For Each Node In NodeList

WScript.Echo "ref: " & Node.getAttribute("guid")

WScript.Echo "const: " & Node.getAttribute("id")

(in reply to Zifter)

Post #: 43

RE: XML to CSV in VBScript - 9/12/2005 10:23:01 PM

Zifter

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

I answered this question in the update (edit) of post number40 (on page 2)

(saw this one cumming

)

(in reply to reloader)

Post #: 44

RE: XML to CSV in VBScript - 9/12/2005 10:59:32 PM

reloader

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

Oh, sorry Zifter, u are right:)

(in reply to Zifter)

Post #: 45

RE: XML to CSV in VBScript - 9/13/2005 1:32:57 AM

reloader

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

By the way, how can I compare elements of an array?

Dim a(8)

a(23)="aa"

a(1011)="ab"

a(2223)="bc"

a(3435)="cd"

a(4647)="aa"

a(5859)="cd"

a(7071)="dd"

a(8283)="aa"

I would like to store in variables all of differrent(in string) a(i) at once. So I would like to get this:

str(1)="aa"
str(1)="ab"
str(1)="bc"
str(1)="cd"
str(1)="dd"

(in reply to reloader)

Post #: 46

RE: XML to CSV in VBScript - 9/13/2005 11:18:28 PM

reloader

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

Zifter,

Option Explicit

Dim objXMLDoc
Dim NodeList
Dim Node
Dim ChildNode

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

Set NodeList = objXMLDoc.documentElement.SelectNodes("State")
For Each Node In NodeList
WScript.Echo "ref: " & Node.getAttribute("ref")
WScript.Echo "const: " & Node.getAttribute("const")
For Each ChildNode In Node.ChildNodes
WScript.Echo ChildNode.NodeName & ": " & ChildNode.Text
Next
WScript.Echo "-------------"
Next

It's your code.
What should I do, if I want to declare in the code the TagName too?? So not WScript.Echo ChildNode.NodeName, but <name> and <capital> like in the Node.getattribute("ref")?

(in reply to reloader)

Post #: 47

RE: XML to CSV in VBScript - 9/13/2005 11:51:44 PM

Zifter

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

I covered this question also in the edit of post 40 on page 2.

Test the script I posted between the 'edit tags' and try to understand what I'm doing there.
If it's not clear what happens in my example I will be glad to explain.

<edit>
I reread your last post, and now I'm confused.
What is wrong with the ChildNode.NodeName and ChildNode.text ?
The ChildNode.NodeName is the name of the tag like <name> and <capital>
The ChildNode.Text is what value this tag has, like "Florida" and "Tallahassee"
If you want the "<"' and ">" around the NodeName, you could just concatenate them like WScript.Echo "<" & ChildNode.NodeName & ">"
</edit>

< Message edited by Zifter -- 9/13/2005 11:58:31 PM >

(in reply to reloader)

Post #: 48

RE: XML to CSV in VBScript - 9/14/2005 5:52:58 PM

reloader

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

Hi,

Okay, your loop is automatic at the moment, so I don't have to declare TagNames one by one, because your loop do it. But I want to declare myself, not with the loop.
I would like to write in the code the name of tags like <name> or <capital>. So I want a loop, where I declare the tags by myself, and after the loop get me their value.

PS.: Sorry for my many question, but I get a terrible XML format (created by a slob), and I have to re-write in a good and nice format with a script.

(in reply to Zifter)

Post #: 49

RE: XML to CSV in VBScript - 9/18/2005 7:18:40 PM

reloader

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

Hi All,

Could anyone help me, how can I check, that a node is exist or not?

In details, my sample XML:

<Album ref="CD024" category="Country">
      <title>Red Dirt Girl</title>
     <artist>Emmylou Harris</artist>
   <Year>2002</Year>
</Album>

And I wanna check, that <Year> and <Genre> is in there as node.

Thanks,
Attila

(in reply to reloader)

Post #: 50

RE: XML to CSV in VBScript - 9/18/2005 8:02:59 PM

Zifter

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

You can try to select the node and if the returned object "Is Nothing", then it means that the node couldn't be retrieved, so you can conclude that the node doesn't exist.
Check this example:

Option Explicit

Dim objXML
Dim objNode

Set objXML = CreateObject("Microsoft.XMLDOM")
objXML.async = False
objXML.load("c:\test\test.xml")

fnCheckNode "Year"
fnCheckNode "Genre"

Set objXML = Nothing
'-------------------------------------------------------------------------------
Function fnCheckNode(strNodeName)
Set objNode = objXML.documentElement.selectSingleNode(strNodeName)
If objNode Is Nothing Then
WScript.Echo "Node " & strNodeName & " doesn't exist"
Else
WScript.Echo "Node " & objNode.nodeName & " has a value of " & objNode.Text
Set objNode = Nothing
End If
End Function

For this example the XML file "test.xml" contains the XML you provided in your last post.

Note that the nodenames are case sensitive!

HTH

< Message edited by Zifter -- 9/18/2005 8:04:41 PM >

(in reply to reloader)

Post #: 51

RE: XML to CSV in VBScript - 9/18/2005 8:18:55 PM

reloader

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

Hi Zifter,

I tried before my post the select node method, but it was not working as now:

Option Explicit
Dim objXML
Dim objNode
Set objXML = CreateObject("Microsoft.XMLDOM")
objXML.async = False
objXML.load("P:\q.xml")

Set objXML = Nothing
'-------------------------------------------------------------------------------

Set objNode = objXML.documentElement.selectSingleNode("title") <-- Object Required

If objNode Is Nothing Then
WScript.Echo "Node " & strNodeName & " doesn't exist"
Else
WScript.Echo "Node " & objNode.nodeName & " has a value of " & objNode.Text
Set objNode = Nothing
End If

but in my XML file, there is title as node.

(in reply to Zifter)

Post #: 52

RE: XML to CSV in VBScript - 9/18/2005 8:23:39 PM

reloader

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

F.....ck

Sorry, I fault, everything is okay.

(in reply to reloader)

Post #: 53

RE: XML to CSV in VBScript - 9/18/2005 8:34:35 PM

Zifter

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

In all the XML examples I posted I used the object "Microsoft.XMLDOM"
I just read somewhere on the internet (lost the URL), that this is an old object and you better use the most recent one (they folow the W3C standards better)

So change the statement

Set objXML = CreateObject("Microsoft.XMLDOM")

into

Set objXML = CreateObject("MSXML2.DOMDocument.3.0")

or

Set objXML = CreateObject("MSXML2.DOMDocument.4.0")

or

Set objXML = CreateObject("MSXML2.DOMDocument.5.0")

...
take the highest version you have installed.

HTH

(in reply to reloader)

Post #: 54

RE: XML to CSV in VBScript - 9/18/2005 9:28:44 PM

reloader

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

Oh, thanks,

I've tried and I've the higest version(5.0). What are the adventages of this version?

(in reply to Zifter)

Post #: 55

RE: XML to CSV in VBScript - 9/18/2005 9:38:16 PM

Zifter

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

I only know that the "MSXML2.DOMDocument.x.x" objects follow the W3C standards better then the "Microsoft.XMLDOM" object.
I don't know (and couldn't find) the differences between all the versions of the "MSXML2.DOMDocument.x.x" objects...

(in reply to reloader)

Post #: 56

RE: XML to CSV in VBScript - 9/18/2005 10:36:54 PM

reloader

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

When I write a data to a file, what will be the encoding?

Because, I'm writing an XML (secondary)file with a script, from an XML file. When I want to read in Excel the secondary file, I got a message, that: Invalid character in line . . . but the two files are similar except the nodes.

I think it in connection with the encoding, and if I'm right, how can I change this? (I begin to use Replace method, but it's not a useful one because of the 100(?) or more characters, what are different in every encoding.)

(in reply to Zifter)

Post #: 57

RE: XML to CSV in VBScript - 9/19/2005 12:48:27 AM

reloader

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

Zifter,

How can I solve, that the script find a value for a Node?

In example:

<books>
<ref>CD0123</ref>
<genre>Sience Fiction</genre>
<Year>1975</Year>
</books>

I want to get the Value of "ref" and "genre" and "Year", one by one, not in a loop. I need for these values because I want to work with these ones as detached datas.

I know, u explain me the SelectSingleNode method, but it's not working as good as I want at all.

(in reply to reloader)

Post #: 58

RE: XML to CSV in VBScript - 9/19/2005 1:17:07 AM

Zifter

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

If I understand what you are trying to do, then the selectSingleNode method is the one you need. If it's not working like you expect it, then it's probably because your XPath is not correct.

The parameter you give with the selectSingleNode method needs to be an XSL Patterns query OR an XPath expression if you specify the green statement before the selectSingleNode method

objXML.setProperty("SelectionLanguage", "XPath")
objNode = objXML.selectSingleNode("//books/ref")

(in reply to reloader)

Post #: 59

RE: XML to CSV in VBScript - 9/19/2005 11:13:16 AM

mconnelly

Posts: 3
Score: 0
Joined: 9/7/2005
Status: offline

Here are some differences between msxml parser versions

http://www.topxml.com/parsers/default.asp
http://www.topxml.com/xsl/XSLTRef.asp
http://www.topxml.com/xsl/XPathRef.asp

Oh and here is a xml version browser sniffer

look at the html source on the page
http://www.topxml.com/parsers/sniffer/default.asp

(in reply to Zifter)