XML Import

E

Eddie Mason

Registered User.
Local time
Today, 19:39
Joined
Jan 31, 2003
Messages
142
I want to write an import specification that will import a XLM file into an Access table. I can import the file using 'External Data' 'import XLM file' which imports the table but does not allow me to save the import specification. Is there any way to save the specification or if this is not possible can it be imported using vb?

Hope someone can help.

Eddie
 
Best and savest way to import XML files is to write some VBA. The access import tends to create a lot of orphans and all that causing a lot of headaches.

In VBA you are in full control and you can fix things you want them to work.
 
Can you show me how to write an XLM import specification in vb?

kind regards,

Eddie
 
A lot depends on your XML formatting.... it is a bit easier if you have each tag on a seperate line...

Code like this has served me well for both sequenced tags and all tags on seperate lines
(tried make make it little unspecific for privacy reasons and very cut back, but I hope you get the idea)
Code: 
Sub ImportXMLFile(myFileName As String)
    Dim strLine As String
    Dim rs As DAO.Recordset, ColNr As Integer, myFieldName As String
    Dim tagStart As Integer, tagLength As Integer, tagEnd As Integer
    Dim valStart As Integer, valLength As Integer
    Dim myFileID As Integer, RecordNummer As Double, LineNr As Double
    Dim TotalRecords As String
    Dim LeadingTags As String, ThisTag As String, ThisValue As Variant
    prevTag = ""
    
    Dim rsH As DAO.Recordset, rsD As DAO.Recordset
    
    Set rsH = CurrentDb.OpenRecordset("Select * from tblHeaders")
    Set rsD = CurrentDb.OpenRecordset("Select * from tblDetails")
    Open myFileName For Input As #1
    RecordNummer = 0
    LineNr = 0
    Line Input #1, strLine
    strLine = LTrim(strLine) ' FirstLine
    rsH.AddNew
    myFileID = Nz(DMax("FileID", "tblHeaders"), 0) + 1
    rsH!Fileid = myFileID
    rsH!FileName = myFileName
    rsH!processdate = Now()
    rsH!FirstLine = strLine
    Line Input #1, strLine
    LineNr = LineNr + 1
    strLine = LTrim(strLine) ' SecondLine
    rsH!SecondLine = strLine
    Line Input #1, strLine
    LineNr = LineNr + 1
    strLine = LTrim(strLine) ' first nest, start loop
    
    TotalRecords = "?"
    Forms("frmImport").lblProgress.Caption = RecordNummer & " / " & TotalRecords
    Forms("frmImport").lblProgress.Visible = True
    DoEvents
    
    Do While 1 = 1
        tagStart = InStr(1, strLine, "<") + 1
        tagLength = InStr(tagStart, strLine, ">") - tagStart
        tagEnd = tagLength
        valStart = tagStart + tagLength + 1
        valLength = InStr(valStart - 1, strLine, "<") - valStart
        ThisTag = Mid(strLine, tagStart, tagLength)
        ThisValue = Null
        If valLength > 0 Then
            ThisValue = Mid(strLine, valStart, valLength)
        Else
            If ThisTag = "/NearingEndXML" Then
                ' nearly end of the file, stop reading
                Exit Do
            ElseIf Left(ThisTag, 1) = "/" Then
                LeadingTags = Left(LeadingTags, Len(LeadingTags) - Len(ThisTag))
            ElseIf Right(ThisTag, 1) = "/" Then
                ' un-nesting tag
            Else
                ' nesting tag
                LeadingTags = LeadingTags & "/" & ThisTag
            End If
            ThisValue = Null
        End If
        If 1 = 2 Then
            ' dummy for formatting purposes
        ElseIf LeadingTags = "/SomeTag" Then
        ElseIf LeadingTags = "/SomeTag/SubTag" Then
            If 1 = 2 Then
                ' dummy for formatting purposes
            ElseIf ThisTag = "TagWithValue1" Then
                rsH.Fields(ThisTag) = ThisValue
            ElseIf ThisTag = "TagWithValue2" Then
                rsH.Fields(ThisTag) = ThisValue
            ElseIf ThisTag = "TagWithValue3" Then
                rsH.Fields(ThisTag) = ThisValue
                TotalRecords = ThisValue
                Forms("frmImport").lblProgress.Caption = RecordNummer & " / " & TotalRecords
                DoEvents
            ElseIf ThisTag = "CheckSum" Then
                rsH.Fields(ThisTag) = CDbl(Replace(ThisValue, ".", "")) / 100
            End If
        ElseIf LeadingTags = "SomeTag/SubTag/MoreTag" Then
            If 1 = 2 Then
                ' dummy for formatting purposes
            ElseIf ThisTag = "Name" Then
                ' there are more tags with name, so I prefix it with something to make it unique in the table.
                rsH.Fields("MoreTag" & ThisTag) = ThisValue
            End If
        ElseIf LeadingTags = ....
.... etc....
....
        ' cut off the current tag, so we can check for multiple tags on one line
        If valLength > 0 Then
            strLine = LTrim(Mid(strLine, tagLength + 3 + valLength))
        Else
            strLine = LTrim(Mid(strLine, tagLength + 3))
        End If
        If Left(strLine, tagEnd + 3) = "</" & ThisTag & ">" And valLength > 0 Then
            '   closing tag, hopefully we got a value
            strLine = LTrim(Mid(strLine, tagEnd + 4))
        End If
        If Len(strLine) = 0 Then
            ' nothing left on the line read the next
            Line Input #1, strLine
            LineNr = LineNr + 1
            strLine = LTrim(strLine)
        Else
        End If
'       for debugging only
'        DoEvents
'        Debug.Print "Line: " & strLine
'        Debug.Print "Tags: " & LeadingTags
'        Stop
    Loop
    rsH.Close
    rsD.Close
    Close #1
End Sub

I hope you can work it out from here, if you need more help let me know.
 
Many thanks for your help I've got it working now.

Kind regards,

Eddie
 
Already? Wow that is FAST, must not be an XML file with a lot of tags :/
 
Using the default sollutions that access profides works fine if its a single XML in (very) proper format.

If you get more (or very) complex or slightly mallformed but still valid XML you are up for a world of pain. I have had one XML using the default solution created 118 tables without reference between them to create the proper records.
Which is why I came up with my solution to read the tags one by one and treat them one by one. It probably isnt the most efficient way of doing it, it works to satisfaction and in a decent enough speed so that a file containing 200k records is read, checked, processed and exported in about 7 minutes.

Most of that time offcourse is actually lost on the checks and processing, but shows that even for large files this doesnt create 10 hour processing times.
 
You must log in or register to reply here.

Similar threads

R
Solved Import Excel file into existing table with an autonumber primary key field
Replies
6
Views
162
Isaac
Isaac
S
The text file specification <name> does not exist. You cannot import, export, or link using the specification. (Error 3625)
Replies
12
Views
458
The_Doc_Man
The_Doc_Man
oxicottin
Solved Import from excel and delete unwanted data
Replies
14
Views
364
oxicottin
oxicottin
Papa_Bear1
Getting Run-time error '3625' (The text file specification 'x' does not exist...) - but it does.
Replies
12
Views
369
The_Doc_Man
The_Doc_Man
H
Importing HTML table results into a table
2
Replies
20
Views
566
KitaYama
K

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom