Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

ImportXML

Discussion in 'Information Technology' started by nmadrigal@att.net, Jul 28, 2006.

Tags:
  1. nmadrigal@att.net

    nmadrigal@att.net
    Expand Collapse
    Guest

    We receive XML files from a third-party that we import into Access on a
    daily basis. I created a VB for importing this XML file based on the
    test form we received. The VB does the following:

    1. Converts the attribute-centric XML into element-centric XML using an
    XSL
    2. Imports the converted XML file into Access using ImportXML function.
    This creates five separate tables "on the fly".
    3. Imports data from the five tables into the main table Loans using an
    append query.
    4. Performs file and table cleanup

    The problem we are now running into is this. The main Loan table and
    the append query were created based on the the test file and all
    possible fields. Now that we are receiving real data, not all of the
    fields are present. When the module is called, we are receiving the
    "Enter Parameter Value" when importing into the main table Loan for any
    missing fields. If we enter through these messages, the VB errors with
    "data type mismatch." I believe the "data type mismatch" error is
    occuring because there are SQL replace statements in the append query
    for some of the missing fields.

    Is it possible to append data using VB instead of using an Access
    query, doing the following:
    a. Check if the field exists
    b. If not, move on to next field
    c. If so, copy data in that field from one table to another.

    Thank you in advance.
     
  2. Danny J. Lesandrini

    Danny J. Lesandrini
    Expand Collapse
    Guest

    As it turns out, I've done this with XML files using VBA code and there's an
    article with sample download at the Database Journal website.
    http://www.databasejournal.com/features/msaccess/article.php/3310901

    I ended up creating a couple of functions that help me dynamically grab the
    correct field name based on the node name in the XML. If one can't be found,
    then (say, they added a new field I wasn't expecting, or renamed a node) then
    that value would be skipped, but all data that can be mapped is added.

    The first function signature looks like this ...
    Private Function GetFieldName(sTable As String, sField As String) As String

    Given the table it's going into, and the XML field name, the correct Access
    table-field-name is returned. If one can't be found, then processing skips it.

    The code for inserting the record uses a recordset based on the table, like this ...

    ' The WHERE clause simply returns an empty recordset, avoiding overhead.
    sSQL = "SELECT * FROM tblProblems WHERE 1=0"
    Set rst = dbs.OpenRecordset(sSQL, dbOpenDynaset)

    ' Create a new record
    rst.AddNew

    ' The tblProblems table has 52 columns. The child nodes are processed in
    ' what ever order they come. The corresponding table field is looked
    ' up through the GetFieldName() function.
    For ielement = 0 To 51
    Set objNodeItem = objNode_DETAILS.childNodes(ielement)
    If Nz(objNodeItem.Text, "") <> "" Then
    sField = objNodeItem.nodeName
    sColumn = GetFieldName("PRBLMS", sField)

    ' Here is where the recordset field is updated, but only if a field
    ' mapping could be found, and if a value exists.
    If sValue <> "" And sColumn <> "" Then rst(sColumn) = sValue
    End If
    Set objNodeItem = Nothing
    Next ielement

    ' Call the Update method to save the record.
    rst.Update
    rst.Close

    You can download my code and step through it. It works pretty slick, but I'm
    sure my code can be made more flexible and powerful, based on what you need.
    --

    Danny J. Lesandrini
    dlesandrini@hotmail.com
    http://amazecreations.com/datafast


    <nmadrigal@att.net> wrote ...
    > We receive XML files from a third-party that we import into Access on a
    > daily basis. I created a VB for importing this XML file based on the
    > test form we received. The VB does the following:
    >
    > 1. Converts the attribute-centric XML into element-centric XML using an
    > XSL
    > 2. Imports the converted XML file into Access using ImportXML function.
    > This creates five separate tables "on the fly".
    > 3. Imports data from the five tables into the main table Loans using an
    > append query.
    > 4. Performs file and table cleanup
    >
    > The problem we are now running into is this. The main Loan table and
    > the append query were created based on the the test file and all
    > possible fields. Now that we are receiving real data, not all of the
    > fields are present. When the module is called, we are receiving the
    > "Enter Parameter Value" when importing into the main table Loan for any
    > missing fields. If we enter through these messages, the VB errors with
    > "data type mismatch." I believe the "data type mismatch" error is
    > occuring because there are SQL replace statements in the append query
    > for some of the missing fields.
    >
    > Is it possible to append data using VB instead of using an Access
    > query, doing the following:
    > a. Check if the field exists
    > b. If not, move on to next field
    > c. If so, copy data in that field from one table to another.
    >
    > Thank you in advance.
    >
     
  3. Danny J. Lesandrini

    Danny J. Lesandrini
    Expand Collapse
    Guest

    Ooops. In an effort to simplify things, I cut out an important line of code.

    My original line of code read like this, with a function to clean up the data:
    sValue = URLDecode(objNodeItem.Text)

    A more typical line would look like this
    sValue = objNodeItem.Text

    Without this line, the code below will, of course, always skip every field,
    since the variable, sValue, will always be the empty string. My Bad.
    --

    "Danny J. Lesandrini" <dlesandrini@hotmail.com> wrote ...
    >
    > ' The WHERE clause simply returns an empty recordset, avoiding overhead.
    > sSQL = "SELECT * FROM tblProblems WHERE 1=0"
    > Set rst = dbs.OpenRecordset(sSQL, dbOpenDynaset)
    >
    > ' Create a new record
    > rst.AddNew
    >
    > ' The tblProblems table has 52 columns. The child nodes are processed in
    > ' what ever order they come. The corresponding table field is looked
    > ' up through the GetFieldName() function.
    > For ielement = 0 To 51
    > Set objNodeItem = objNode_DETAILS.childNodes(ielement)
    > If Nz(objNodeItem.Text, "") <> "" Then
    > sField = objNodeItem.nodeName
    > sColumn = GetFieldName("PRBLMS", sField)
    >
    > ' Here is where the recordset field is updated, but only if a field
    > ' mapping could be found, and if a value exists.
    > If sValue <> "" And sColumn <> "" Then rst(sColumn) = sValue
    > End If
    > Set objNodeItem = Nothing
    > Next ielement
    >
    > ' Call the Update method to save the record.
    > rst.Update
    > rst.Close
     

Share This Page