Public Sub readText()
[COLOR=Green]'********************
'Code Courtesy of
' Paul Eugin
'********************[/COLOR]
Dim FileNum As Integer, iCtr As Long, jCtr As Long, kCtr As Long
Dim DataLine As String, tmpArr() As String, valStr As String, fieldStr As String
Dim upArr(0 To 20) As String, mainArr(0 To 20) As String, tmpStr As String
FileNum = FreeFile()
[COLOR=Green] 'This opens the file to be read.[/COLOR]
Open "C:\Users\pef\Desktop\sample.xml" For Input As #FileNum
While Not EOF(FileNum)
[COLOR=Green]'Read in data 1 line at a time[/COLOR]
Line Input #FileNum, DataLine
[COLOR=Green]'This line replaces all Double Quotes with Single Quotes,
'as Access struggles with parsing them[/COLOR]
DataLine = Replace(DataLine, """", "'")
[COLOR=Green]'Main seperation, as we are interested only with the Display tag.[/COLOR]
If InStr(DataLine, "<Display") <> 0 Then
fieldStr = vbNullString
valStr = vbNullString
[COLOR=Green]'Extracts the information, that we need other than '<Display' and '/>'[/COLOR]
tmpStr = Trim(Mid(DataLine, InStr(DataLine, " ") + 1, InStrRev(DataLine, " />")))
tmpStr = Trim(Mid(tmpStr, 1, InStrRev(tmpStr, " />")))
[COLOR=Green]'Split the information based on the Equals operator.[/COLOR]
tmpArr = Split(tmpStr, "=")
jCtr = 0
kCtr = 0
For iCtr = 0 To UBound(tmpArr)
If InStr(tmpArr(iCtr), "'") <> 0 Then
[COLOR=Green] 'Get the Field Name[/COLOR]
mainArr(kCtr) = Trim(Mid(tmpArr(iCtr), InStrRev(tmpArr(iCtr), "'") + 2))
[COLOR=Green] 'Get the Data corresponding to the Field[/COLOR]
upArr(jCtr) = Trim(Mid(tmpArr(iCtr), 2, InStrRev(tmpArr(iCtr), "'") - 2))
jCtr = jCtr + 1
Else
mainArr(kCtr) = Trim(tmpArr(iCtr))
End If
kCtr = kCtr + 1
Next
For iCtr = 0 To kCtr - 2
Select Case mainArr(iCtr)
[COLOR=Green]'Taking only the information we need from the data.[/COLOR]
Case "Author", "Title", "Genre", "Year"
fieldStr = fieldStr & mainArr(iCtr) & ", "
valStr = valStr & Chr(34) & upArr(iCtr) & Chr(34) & ", "
End Select
Next
fieldStr = "(" & Left(fieldStr, Len(fieldStr) - 2) & ") VALUES ("
valStr = Left(valStr, Len(valStr) - 2) & ")"
CurrentDb.Execute ("INSERT INTO tblMusicList " & fieldStr & valStr)
[COLOR=Green]'Debug.Print "INSERT INTO tblMusicList " & fieldStr & valStr[/COLOR]
End If
Erase tmpArr
Erase mainArr
Erase upArr
Wend
End Sub