Solved

import xml into access 2007/2010

Posted on 2011-09-27
11
924 Views
Last Modified: 2012-06-13
Hi,
I`m new in xml world , & need help around.
well i have large xml file (more than 30mb) and need to import it into access table.
the problem is, that when i perform the import i just get many tables without relationship between them:{
when i open the same xml file in excel 2007/2010 i got it as speadsheet (columns and rows) , and that wat i need to have in access ,
how can i perform this task in access vba (automation-import process) and have the tables linked , or have it as 1 table with fields and rows?
0
Comment
Question by:drtopserv
  • 5
  • 3
  • 3
11 Comments
 
LVL 5

Expert Comment

by:eridanix
ID: 36707920
0
 

Author Comment

by:drtopserv
ID: 36707966
bro,
I think u r trying to answer the Q by applying the answer to implement THE SLA of this services website.
i know how to search the web (google) and have code to import the xml into access with table without no connection between them.that`s what i explain clearly in my Q .
u suggestion was to perfom the import normally without having the link between them.
i need to solve my problem mention in the Q. :}
again: How could i import xml into access 2007/2010 through vba and have the table links (with a relationship) exactly like spreadsheet in excel (when i import xml into excel speadsheet) i have them with columns and rows, that what i need to have in access 2007/2010 !:}?
0
 
LVL 5

Expert Comment

by:eridanix
ID: 36708015
So the solution is, to write vba script which will read and traverse xml file, create data row as you need and than inserts it into Access table by sql query.

How to traverse xml and insert row to Access table using vba, you can find easily on google.
0
 

Author Comment

by:drtopserv
ID: 36708175
this what i need, but i don`t know what "traverse xml " means?
also i have search the net around there is lots of articles about xsd/xsl/xml schema etcs...
i need simple way or person who works with this field that may help me out (maybe u) and tell me how i can perform this task,.
0
 
LVL 5

Expert Comment

by:eridanix
ID: 36708202
Traversing xml means to read each (or only some) of nodes in xml, get the values and do with values what you need.

Good example how to get values from xml is in this soluted question from EE.
http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_23015675.html

If you will need more help, send example of data in your xml and I'll try.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 57
ID: 36708979
I haven't read through the other links, so pardon me if I'm repeating here.

Access expects XML to be "well formed" for import.  If you run the import process and don't like what you get, then your only choice is to parse the file yourself.

  That can be done using the document object like shown in the attached code.  Basically you end up with a tree structure that you walk and grab the value for each of the elements.

Jim.

Public Function ImportXMLFile(ByVal strFile As String) As Boolean
 
        ' OCS - JRD - 04/05/08 - Initial write.
 
        ' This function will process a single "time form" XML file
        ' and makes fundamental assumptions about the format of the XML file.
        ' If the XML file format is changed in any way, this code will fail.
 
        ' Each file is expected to have the following data:
        '
        '     SmartformInstance        (multiple per file)
        '       Form                   (one set per form instance)
        '         JobInfo              (one set per form instance)
        '         TravelStart          (one set per form instance)
        '         TravelEnd            (one set per form instance)
        '         ClockIn              (one set per form instance)
        '         StartBreak           (one set per form instance)
        '         EndBreak             (one set per form instance)
        '         ClockOut             (one set per form instance)
        '
        ' For the sections (Travel Start through Clock Out), seven (7)
        ' employee fields are expected.
 
        Dim objDomDoc As New DOMDocument
        Dim objNodeSmartForm As IXMLDOMNode
        Dim objNode_Sections As IXMLDOMNode
        Dim objNodeData As IXMLDOMNode
        Dim objNodeItem As IXMLDOMNode
 
        Dim dbs As DAO.Database
        Dim rst As DAO.Recordset
        Dim strSQL As String
 
        Dim lngForm As Long
        Dim intSection As Integer
        Dim intEmployee As Integer
 
        Dim strInstanceID As String
        Dim strInstanceDate As String
        Dim strInstanceType As String
        Dim strFormName As String
        Dim strJobNumber As String
        Dim strPerDiem As String
        Dim strValue As String
 
10      On Error GoTo ImportXMLFile_Error
 
20      ImportXMLFile = False
 
        ' Open a DAO Database connection
30      Set dbs = CurrentDb()
 
        ' Verify that such a file exists.  If not, then exit
40      If Dir(strFile) <> "" Then
 
          ' Open DOM object by loading the xml file.
50        objDomDoc.async = False
60        objDomDoc.Load strFile
 
          ' Loop through the form nodes.
70        For Each objNodeSmartForm In objDomDoc.documentElement.childNodes
 
 
            '  Get the header info
80          strInstanceID = Nz(objNodeSmartForm.childNodes(0).Text, "")
90          strInstanceDate = Nz(objNodeSmartForm.childNodes(1).Text, "")
100         strInstanceType = Nz(objNodeSmartForm.childNodes(2).Text, "")
110         strFormName = Nz(objNodeSmartForm.childNodes(3).childNodes(0).Text, "")
120         strJobNumber = Nz(objNodeSmartForm.childNodes(3).childNodes(2).childNodes(1).childNodes(2).Text, "")
130         strPerDiem = Nz(objNodeSmartForm.childNodes(3).childNodes(2).childNodes(2).childNodes(2).Text, "")
 
 
            ' Save header info
140         strSQL = "SELECT * FROM tblSmartFormInstance WHERE 1=0"
150         Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
160         rst.AddNew
170         rst![FormInstanceID] = strInstanceID
180         rst![FormInstanceDate] = CDate(Mid(strInstanceDate, 6, 2) & "/" & Mid(strInstanceDate, 9, 2) & "/" & Mid(strInstanceDate, 1, 4) & " " & Mid(strInstanceDate, 12, 8))
190         rst![TypeCode] = strInstanceType
200         rst![FormName] = strFormName
210         rst![JobNumber] = strJobNumber
220         rst![PerDiem] = strPerDiem
230         rst.Update
 
240         rst.Close
 
            ' Now get the details for the form
 
            ' Open detail table
250         strSQL = "SELECT * FROM tblSmartFormInstanceDetail WHERE 1=0"
260         Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
 
            ' Process the sections.  The code below depends on the same sections
            ' being there each time, having the same number of elements,
            ' and being in the same order.
            ' A more flexible approach would be to check the element data,
            ' then do a lookup on the section and field names and map to the tables.
            ' However we are doing only one form at the moment and the data will
            ' always be provided in the current format.
270         Set objNode_Sections = objNodeSmartForm.childNodes(3)
 
280         For intSection = 3 To 8
290           Set objNodeData = objNode_Sections.childNodes(intSection)
              ' objNodeData.childNodes(0).text is the section name
300           For intEmployee = 1 To 7
310             Set objNodeItem = objNodeData.childNodes(intEmployee)
320             If Nz(objNodeItem.childNodes(2).Text, "") <> "" Then
330               strValue = Nz(objNodeItem.childNodes(2).Text, "")
340               If strValue <> "" Then
350                 rst.AddNew
360                 rst![FormInstanceID] = strInstanceID
370                 rst![SectionTypeID] = intSection - 2
380                 rst![EmployeeListOrder] = intEmployee
390                 rst![EmployeeName] = strValue
400                 rst.Update
410               End If
420             End If
430             Set objNodeItem = Nothing
440           Next intEmployee
450           Set objNodeData = Nothing
460         Next intSection
 
470         rst.Close
 
480       Next
 
490       ImportXMLFile = True
 
500     End If
 
ImportXMLFile_Exit:
510     On Error Resume Next
 
        ' Clean up
520     Set rst = Nothing
530     Set dbs = Nothing
 
540     Set objNodeItem = Nothing
550     Set objNodeData = Nothing
560     Set objNode_Sections = Nothing
570     Set objNodeSmartForm = Nothing
580     Set objDomDoc = Nothing
 
590     Exit Function
 
ImportXMLFile_Error:
600     MsgBox "Unexpected Error"
610     Resume ImportXMLFile_Exit
 
End Function

0
 

Author Comment

by:drtopserv
ID: 36984485
wow.seems intreseting code, i need first to learn it!!!:}
0
 
LVL 57
ID: 36985467

 It actually is fairly simple.  You delcare a document object and then load the XML file into it.  When that's done, you have a tree structure (think of a tree view control) for all the nodes of the XML file.

  You can then "walk" that structure, picking and choosiing the elements you need and save as appropriate.

  With this method, it doesn't matter what the XML looks like.

Jim.
0
 

Author Comment

by:drtopserv
ID: 37360520
may u plz post me xml file example that attached with mdb to try it, still hard to understand if u maY:}}
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 37363731

 It is not so much the code there, but the overall technique that you need to focus on.  

Once you have set a reference to the MS XML lib in Access, you can then work with the Document Object.  The document object has a set of properties and methods that you can use to work with an XML file.

  First you load the XML file into the document object:

60        objDomDoc.Load strFile

and once that is done, can access the various objects within it:

          ' Loop through the form nodes.
70        For Each objNodeSmartForm In objDomDoc.documentElement.childNodes

  This is all covered at:

http://www.w3schools.com/dom/default.asp

Look at the menu down the left hand side of that page.  That explains in detail what the Dcoument object is, its properties and methods, and how to use it to work with XML files along with plenty of examples.

Jim.
0
 

Author Closing Comment

by:drtopserv
ID: 38082010
explination help out.\
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now