Importing XML Data in Access 2003

  • Thread starter Thread starter NetteC
  • Start date Start date
N

NetteC

I have an XML file with first & Lastname and other pertinent info that I
would like to import into a blank Access db, but the data imports as several
tables grouped by the column headings. So when you finish importing, one
record takes up several rows, instead of one. How can I import this xml data
so that everything pertaining to a person is imported into one record and
only takes up one row? I've also tried to import the xml data file into
Excel and I get the same problem.
 
Don't know how your XML is formatted but I think you should be able to use
something like:

Dim InPut, FoundStr as string
Dim StrArr
Set db = CurrentDb
Set rec = db.OpenRecordset("table", dbOpenDynaset)
InPut = "filename.xml"

Open InPut For input As #1
Do While Not EOF(1)
Line Input #1, FoundStr
... 'Use code to split line into array elements
... 'Use code to format individual array elements
' Now write the cleaned elements into a table
rec.AddNew
rec("FirstName") = StrArr(<element>)
rec("LastName") = StrArr(<element>)
...
rec.Update

Loop

Close #1
Set db = Nothing
Set rec = Nothing

Good luck!
 
Back
Top