XML to CSV in VBScript

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

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 >> XML to CSV in VBScript
Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!

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

Message

<< Older Topic Newer Topic >>

XML to CSV in VBScript - 9/7/2005 1:15:02 AM

reloader

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

Hi All,

Does anybaody has a suggest or reference , that how I can do a script, what can convert an XML file to SCV?

Thanks,
Attila

Post #: 1

RE: XML to CSV in VBScript - 9/7/2005 10:11:53 AM

TNO

Posts: 1302
Score: 12
Joined: 12/18/2004
From: thenewobjective.com
Status: offline

What does the XML look like, and how do you want the CSV to look?

_____________________________

To iterate is human, to recurse divine. -- L. Peter Deutsch

(in reply to reloader)

Post #: 2

RE: XML to CSV in VBScript - 9/7/2005 11:09:00 AM

mconnelly

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

Here is how I would do in VBA using xslt transform from xsl file.
You could use other xslt transforms to convert to HTML
Assuming simple element xml file. No error checking.

Sub xmltocsv()
Dim oDOM As MSXML2.DOMDocument
Dim oXML As MSXML2.DOMDocument
Dim oXSL As MSXML2.DOMDocument
Dim strHTML As String
Dim strTransform As String
Set oDOM = CreateObject("MSXML2.DOMDocument")
oDOM.async = False
oDOM.Load ("C:\XML\CSV Reader\XmlCsvReader\test.xml")
Set oXSL = CreateObject("MSXML2.DOMDocument")
oXSL.async = False
oXSL.Load "C:\XML\CSV Reader\XmlCsvReader\ConvertToCsv.xsl"
'your XSLT stylesheet should be saved as unicode or UTF not ansii
'note encoding instruction maybe needed for european language encoding say swedish characters

strTransform = oDOM.transformNode(oXSL)
strHTML = "<?xml version='1.0' encoding=""ISO-8859-1""?>" & vbCrLf & _
           "<root>" & strTransform & "</root>"

WriteFile "C:\Access files\xmltests\CSVtype" & Format(Now, "yyyymmddhhmmss") & ".csv", strTransform
'
'the above XSLT transform with xsl file converts this to a flat csv format file

Set oDOM = Nothing
Set oXML = Nothing
Set oXSL = Nothing

End Sub
Public Sub WriteFile(ByVal sFileName As String, ByVal sContents As String)
' Dump XML String to File for debugging
   Dim fhFile As Integer
   fhFile = FreeFile
' Debug.Print "Length of string=" & Len(sContents)
   Open sFileName For Output As #fhFile
   Print #fhFile, sContents;
   Close #fhFile
   Debug.Print "Out File" & sFileName
End Sub

ConvertToCsv.xsl ( note cut and paste and save as UTF-8 or Unicode not ANSI


<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
   xmlns:msxsl="urn:schemas-microsoft-com:xslt"
               xmlns:user="mynamespace"
               version="1.0">
<msxsl:script implements-prefix="user"><![CDATA[
]]></msxsl:script>
<xsl:output method="text"/>
<xsl:template match="/">
   <xsl:apply-templates select="/*/row[1]" mode="names"/>
   <xsl:apply-templates select="/*/row"/>
</xsl:template>
<xsl:template match="row" mode="names">
   <xsl:for-each select="*">"<xsl:value-of select="name()"/>"<xsl:if test="last() > position()">,</xsl:if></xsl:for-each><xsl:text> 
</xsl:text>
</xsl:template>
<xsl:template match="row">
   <xsl:for-each select="*"><xsl:value-of select="."/><xsl:if test="last() > position()">,</xsl:if></xsl:for-each><xsl:text> 
</xsl:text>
</xsl:template>
</xsl:stylesheet>

(in reply to reloader)

Post #: 3

RE: XML to CSV in VBScript - 9/7/2005 5:56:50 PM

reloader

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

mconelly,

Many thanks for your reply. I'm testing it, but I have an error message with this: Dim oDOM As MSXML2.DOMDocument
The message is: expected end of a statement

TNO,

I have a simpel XML file, and the Output look of the CSV is not too important.

(in reply to mconnelly)

Post #: 4

RE: XML to CSV in VBScript - 9/7/2005 6:10:54 PM

Zifter

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

The reason why you get this error message is because in VbScript all variables are of the variant type. You can't specify the variable type when declaring variables in VbScript. The example from mconelly is in VBA!

Check the XMLDOM object for handling XML files in VbScript
http://www.devguru.com/Technologies/xmldom/quickref/xmldom_index.html

Or do a search here on the forum

HTH

(in reply to reloader)

Post #: 5

RE: XML to CSV in VBScript - 9/7/2005 6:23:31 PM

reloader

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

Hi Zifter,

I cannot find any doc for DOM neither from the page (examples only in JavaScript) nor on this forum:(

(in reply to Zifter)

Post #: 6

RE: XML to CSV in VBScript - 9/7/2005 7:06:30 PM

Zifter

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

The page I linked to in my previous post contains examples in VbScript, not in Javascript. But the examples are VbScript to be included in a webpage (or HTA). Changing the "document.write" into "wscript.echo" should make those examples work as stand alone VbScripts (vbs,wsf)

And here is one other post concerning the XMLDOM object:
http://www.visualbasicscript.com/m_3465/tm.htm

Try to see what you can come up with.
If you have any problems or questions creating your script, feel free to post them here.

HTH

(in reply to reloader)

Post #: 7

RE: XML to CSV in VBScript - 9/7/2005 7:42:01 PM

reloader

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

Thanks Zfiter, but I think I will start again on another way. But questions are on this way too.

Could you help me, that how I can search a character in a file? (I think it's on msdn.microsoft.com - but this documentation is awful for me).

(in reply to Zifter)

Post #: 8

RE: XML to CSV in VBScript - 9/7/2005 7:49:52 PM

Zifter

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

Look at the function InStr() which returns the position of the first occurrence of a certain string (this can be one character) in another string (and this can be the file name or the content of the file or ...)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/vsfctinstr.asp

HTH

(in reply to reloader)

Post #: 9

RE: XML to CSV in VBScript - 9/7/2005 8:20:06 PM

reloader

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

Thanks Zifter, it's working! But is it always read only the first line?

(in reply to Zifter)

Post #: 10

RE: XML to CSV in VBScript - 9/7/2005 8:33:34 PM

Zifter

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

I'm sorry, but I don't understand your question.
What is reading only the first line? Where?

The InStr() function works with strings.

strMyTestString = "ABCDEFG"
WScript.Echo InStr(strMyTestString,"D")
Output: 4

strMyTestString = "ABCDEFG"
WScript.Echo InStr(strMyTestString,"Z")
Output: 0

(in reply to reloader)

Post #: 11

RE: XML to CSV in VBScript - 9/7/2005 8:41:33 PM

reloader

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

Yes, sorry, I watched may code.

Dim filesys, filetxt,contents, strKeyword

strKeyword= "Wf"

Const ForReading = 1, ForWriting = 4041, ForAppending = 8

Set filesys = CreateObject("Scripting.FileSystemObject")

Set readfile = filesys.OpenTextFile("P:\b.txt", ForReading, False)

contents = readfile.ReadLine()

If InStr (112113,contents, strKeyword) > 0 Then

   Wscript.echo ("Found")

    Wscript.echo Instr

   Else

    Wscript.echo ("Not found")

End If

readfile.close

So: I open a file, and the InStr contents is the Readline. But as I saw, the readline is reading only the first line in the file. How can I do, that it reads for instance the second line?

_____________________________

Ati

(in reply to Zifter)

Post #: 12

RE: XML to CSV in VBScript - 9/7/2005 8:59:31 PM

Zifter

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

There are a couple of ways to read an entire file.

The ReadLine() method will indeed read the first line. Then "goes to the second line and waits for further instructions". If you would then invoke the ReadLine method again, it will read the second line and will go to the third line.

strLine1 = readfile.ReadLine
strLine2 = readfile.ReadLine

If you want to handle each line you can put this in a loop

Do Until readfile.AtEndOfStream
   contents = readfile.ReadLine
   If InStr(contents,strKeyword) > 0 Then
       ...
   End If
Loop

There is also a method called ReadAll(). This method will read the complete content of the file. So this would make:

contents = readfile.ReadAll
If InStr(contents,strKeyword) > 0 Then
   ...
End If

A third method Read(n) also exists. This will read "n" characters from the file and positions itself after that "n"-th character. But I don't think this is usefull for what you want to do.

See where this will get you and don't hesitate to post your questions or problems.

(in reply to reloader)

Post #: 13

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

reloader

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

Great thanks! Very effecitve, but I give an error message (Object variable not set), for:

Do Until readfile.AtEndOfStream

Above this line there is the set: Set readfile = filesys.OpenTextFile("P:\b.txt", ForReading, False)

Then, what is the problem?

(in reply to Zifter)

Post #: 14

RE: XML to CSV in VBScript - 9/7/2005 10:00:14 PM

reloader

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

Okay, I solved it.

(in reply to reloader)

Post #: 15

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

reloader

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

Maybe my last question:

I want, that my script save the three next character(for instance) in a varibale from a file.

So the script positioned after the searched string and save the next x character in a variable.

For example I search for "s"

The file contain:

reverse0102

And the script store the red part in a variable.

(in reply to reloader)

Post #: 16

RE: XML to CSV in VBScript - 9/7/2005 11:44:57 PM

Zifter

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

Something like this:

strTest = "reverse0102"
intPos = InStr(strTest,"s")
strVariable = Mid(strTest,intPos + 1,3)
WScript.Echo strVariable

Remarks:
1. The red +1 is because the InStr functions finds the position of the searched character but we want to extract starting from the next character, thus +1
2. The InStr functions finds only the position of first occurence of the searched character. So in this example, if the string would have contained multiple times the letter "s", InStr would only find the position of the first "s". So be carefull with this!

(There also exists another function called InStrRev, which does the same as the InStr function, but starts at the end of the string, so finding the position of the last occurence of the character to be found)

HTH

(in reply to reloader)

Post #: 17

RE: XML to CSV in VBScript - 9/8/2005 12:04:41 AM

reloader

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

Thanks a lot Zifter. It's birlliant!

And if the stored data between two characters or words(by> <by or > <)? For instance:

by>reverse<by

Can I do this from your previous code?

(in reply to Zifter)

Post #: 18

RE: XML to CSV in VBScript - 9/8/2005 12:49:29 AM

Zifter

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

I suddenly notice...Are you trying to write your own XML parser? I don't think the effort is worth it. Don't re-invent the wheel.
Did you take a look at that other post I linked to concerning the XMLDOM object?

(in reply to reloader)

Post #: 19

RE: XML to CSV in VBScript - 9/8/2005 12:55:25 AM

reloader

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

Yes, you are right, but I work for a company and it has a strong IT-security.

For XML-handling some XML dll's is missing from our machine. There is no chance, that I can get it, so I have to write a basic parser. By the way it's a good practice for me.

(in reply to Zifter)