[RESOLVED] convert csv to xml

[nf_vb]

hi ...

i got one file:

filename -test.csv

and i need to convert him to an xml file (ex:test.xml)

anyone know how can i make it possible.

i have already tried many things but nothing work's.

Please help me.

thx

[MartinLiss]

What does the file look like?

[nf_vb]

little example:

csv header:

tiplin,tipdoc,numdocid,merc,name........

csv data:

C,C,123456787,0,name1.......
C,C,123456788,1,name2.......
C,C,123456789,2,name3......

I was clear???

thx

[bgmacaw]

Essentially what you would do is read in the CSV file line-by-line and parse it into a XML document object then save the XML object to disk. It's a fairly straightforward task although the syntax of using the XML document object is a bit tricky at first glance. Is there a particular part of the process you're getting hung up on?

[MartinLiss]

Here's a small start.

Dim ff As Integer
    Dim strParts() As String
    Dim strOneLine As String
    ff = FreeFile
    Dim strNodeNames() As String
    Dim lngIndex As Long
    
    Open "C:\temp\csv.txt" For Input As #ff
    
    ReDim strNodeNames(0)
    
    Do Until EOF(ff)
        Line Input #ff, strOneLine
        strParts = Split(strOneLine, ",")
        If UBound(strNodeNames) = 0 Then
            ReDim strNodeNames(UBound(strParts))
            For lngIndex = 0 To UBound(strParts) - 1
                strNodeNames(lngIndex) = strParts(lngIndex)
            Next
        Else
            ' store the node values
        End If
    Loop
    
    ' When done write the nodes

[Al42]

Two other ways:

If you're only going to do this once, and have Excel available, read the file with Excel and save it as a worksheet.

If you're going to be doing it more than once (I do it at least once a week, due to a client who can't understand that a csv file isn't a spreadsheet) and you have Excel available, just automate Excel to load the file you choose and save it as a worksheet. There's an excellent tutorial, including code, here.

[MartinLiss]

Here's a complete solution.

Dim ff As Integer
    Dim strParts() As String
    Dim strOneLine As String
    ff = FreeFile
    Dim strAttributes() As String
    Dim lngIndex As Long
    
    Dim oxmlCommentNode As IXMLDOMNode
    Dim oxmlTips As IXMLDOMElement
    Dim oxmlDoc As MSXML2.DOMDocument40
    Dim oxmlPI As IXMLDOMProcessingInstruction
    Dim oxmlRootNode As IXMLDOMElement
    
    Open "C:\temp\csv.txt" For Input As #ff
    
    ReDim strAttributes(0)
    
    Set oxmlDoc = New DOMDocument40
    
    ' Create the XML declaration
    Set oxmlPI = oxmlDoc.createProcessingInstruction("xml", "version='1.0' encoding='ISO-8859-1'")
    oxmlDoc.appendChild oxmlPI
    
    ' Add a comment nodes
    Set oxmlCommentNode = oxmlDoc.createComment("Created by MartinLiss")
    oxmlDoc.appendChild oxmlCommentNode
 
    ' Create the oxmlRootNode element
    Set oxmlRootNode = oxmlDoc.createElement("Tips")
    oxmlDoc.appendChild oxmlRootNode
    
    Do Until EOF(ff)
        Line Input #ff, strOneLine
        strParts = Split(strOneLine, ",")
        If UBound(strAttributes) = 0 Then
            ReDim strAttributes(UBound(strParts))
            For lngIndex = 0 To UBound(strParts) - 1
                strAttributes(lngIndex) = strParts(lngIndex)
            Next
        Else
            ' Create a <Tip> element and add it to the oxmlRootNode node
            Set oxmlTips = oxmlDoc.createElement("Tip")
            For lngIndex = 0 To UBound(strAttributes) - 1
                oxmlRootNode.appendChild oxmlTips
                ' Add the attributes and their values
                oxmlTips.setAttribute strAttributes(lngIndex), strParts(lngIndex)
            Next
        End If
    Loop
    
 
    ' Save to the XML file
    oxmlDoc.save "C:\temp\csv.xml"
    Close #ff

If you want to be able to successfully copy/paste that code you can use the app you can download from the Copy/Paste VB Code link in my signature or an Add-In that does the same thing from the Paste VB Code Add-In link. You can also paste it into WordPad and then copy/paste from there into your app.

[MartinLiss]

There's a couple of lines that need to be changed

For lngIndex = 0 To UBound(strParts) '- 1

and

For lngIndex = 0 To UBound(strAttributes) ' - 1

[rnd me]

ok, i know i'm beating a dead horse here, but here my shot:



dim labs() as string

Function tags(t) As String
On Error Resume Next
Dim z As Long

For z = 0 To UBound(labs)
t(z) = Join(Array("<", labs(z), ">", t(z), "</", labs(z), ">"), "")
Next z

tags = Join(t, "")

End Function

Function csv2xml(strCsv As String)
On Error Resume Next 'handle missing blocks/invalid chars
Dim ln() As String : Dim x As Long

ln() = Split(strCsv, vbCrLf)
labs = Split(ln(0), ",")

For x = 1 To UBound(ln) 'if vba/vbs use a buffer var
ln(x) = tags(Split(ln(x), ","))
Next x

csv2xml = "<csv>" & Join(ln(), vbCrLf) & "</csv"
Erase labs()
End Function

'usage csv2xml(TheCsvAsString)


pros/cons-
non validating/parsing
-wont freeze on erronous data, but not guaranteed to be well formed (though my trys were).

main advantage is speed - >10X faster than msxml!
this is due to not using any string concatenation in the loop, mainly a javascript principle.

if you don't have xml-incompatible chars in your data, (your example didn't), this is the way to go. a few simple comment tags, and you can use it in IE/ASP as well.

i just wrote this over the last half hour, so it's not perfect, but i like it, and it seems to be working. i parsed the win32api csv in 1.7 seconds!

[nf_vb]

thx guys.

you saved me the day

I am owing one.