Help with Csv to Xml code plz!!

[samba2]

I have spent the last 8 hours trying to debug this and a new pair of eyes will probably help the situation.

This code converts csv to basic XML

Problems
(1) I can only use ";" as a delimiter and not "," even by changing the variables it still looks for ";"

(2) When this script runs it only converts the last line of csv to XML. I need to rewrite this but can't figure it out. Any help is greatly appreciated.

Private Sub Command1_Click()

Dim sFileName As String
Dim strReadVariable As String
Dim intFileNum As Integer: intFileNum = FreeFile
Dim strArr() As String
Dim iInfileNum As Integer
Dim iOutfileNum As Integer
Dim sInfileName As String
Dim sOutfileName As String

Dim iFirstSemi As Integer
Dim iSecondSemi As Integer

Dim sInrec As String
Dim sFirstPart As String
Dim sSecondPart As String
Dim sThirdPart As String

sInfileName = "C:\text.csv"
sOutfileName = "C:\text.xml"

Open sInfileName For Input As #intFileNum
Do Until EOF(intFileNum)
Input #intFileNum, strRead, sInrec
ReDim Preserve strArr(intLineNum)
strArr(intLineNum) = strRead
intLineNum = intLineNum + 1
Loop
Close #intFileNum


iFirstSemi = InStr(1, sInrec, ";&quot
iSecondSemi = InStr(iFirstSemi + 1, sInrec, ";&quot

sFirstPart = Trim(Left(sInrec, iFirstSemi - 1))
sSecondPart = _
Trim(Mid(sInrec, iFirstSemi + 1, iSecondSemi - iFirstSemi - 1))
sThirdPart = Trim(Mid(sInrec, iSecondSemi + 1))

iOutfileNum = FreeFile
Open sOutfileName For Output As #iOutfileNum

Print #iOutfileNum, &quot;<NAMEVALUE>&quot;
Print #iOutfileNum, &quot;<NAME>DESCRIPTION</NAME>&quot;
Print #iOutfileNum, &quot;<VALUE>&quot; & sFirstPart & &quot;</VALUE>&quot;
Print #iOutfileNum, &quot;</NAMEVALUE>&quot;

Print #iOutfileNum, &quot;&quot;

Print #iOutfileNum, &quot;<NAMEVALUE>&quot;
Print #iOutfileNum, &quot;<NAME>SUPPLIER_PART_NUMBER</NAME>&quot;
Print #iOutfileNum, &quot;<VALUE>&quot; & sSecondPart & &quot;</VALUE>&quot;
Print #iOutfileNum, &quot;</NAMEVALUE>&quot;

Print #iOutfileNum, &quot;&quot;

Print #iOutfileNum, &quot;<NAMEVALUE>&quot;
Print #iOutfileNum, &quot;<NAME>LONG_DESCRIPTION</NAME>&quot;
Print #iOutfileNum, &quot;<VALUE>&quot; & sThirdPart & &quot;</VALUE>&quot;
Print #iOutfileNum, &quot;</NAMEVALUE>&quot;

Close #iOutfileNum


End Sub

 

[MarkSweetland]

Both your looping and xml output code are all mixed up. Here:

Dim iInfileNum As Integer
Dim iOutfileNum As Integer
Dim sInfileName As String
Dim sOutfileName As String

Dim iFirstSemi As Integer
Dim iSecondSemi As Integer

Dim sFirstPart As String
Dim sSecondPart As String
Dim sThirdPart As String

sInfileName = &quot;C:\text.csv&quot;
sOutfileName = &quot;C:\text.xml&quot;

iOutfileNum = FreeFile
Open sOutfileName For Output As #iOutfileNum
Print #iOutfileNum, &quot;<parts>&quot;

Dim sInputRec As String

intFileNum = FreeFile
Open sInfileName For Input As #intFileNum
Do Until EOF(intFileNum)
'read in entire record line
Line Input #intFileNum, sInputRec
'remove any quotes - don't need with xml
sInputRec = Replace(sInputRec, Chr(34), Chr(32))
'determine the semicolon locations
iFirstSemi = InStr(1, sInputRec, &quot;;&quot
iSecondSemi = InStr(iFirstSemi + 1, sInputRec, &quot;;&quot
' parse out the info between the semi colons
sFirstPart = Mid(sInputRec, 1, iFirstSemi - 1)
sSecondPart = Mid(sInputRec, iFirstSemi + 1, iSecondSemi - iFirstSemi - 1)
sThirdPart = Mid(sInputRec, iSecondSemi + 1, Len(sInputRec))
' do whatever
Print #iOutfileNum, &quot;<part>&quot;
Print #iOutfileNum, &quot;<description>&quot; & sFirstPart & &quot;</description>&quot;
Print #iOutfileNum, &quot;<partnum>&quot; & sSecondPart & &quot;</partnum>&quot;
Print #iOutfileNum, &quot;<longdescription>&quot; & sThirdPart & &quot;</longdescription>&quot;
Print #iOutfileNum, &quot;</part>&quot;
Loop
Print #iOutfileNum, &quot;</parts>&quot;
Close #intFileNum
Close #iOutfileNum


Mark

 

[samba2]

Thank you VERY much for your time that works just perfect.

Thanks again

-Sam