Solved

import xml into access 2007/2010

Posted on 2011-09-27
11
926 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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
 
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Confluence of Individual Knowledge and the Collective Intelligence At this writing (summer 2013) the term API (http://dictionary.reference.com/browse/API?s=t) has made its way into the popular lexicon of the English language.  A few years ago, …
Many times as a report developer I've been asked to display normalized data such as three rows with values Jack, Joe, and Bob as a single comma-separated string such as 'Jack, Joe, Bob', and vice versa.  Here's how to do it. 
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

773 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