Results 1 to 4 of 4
  1. #1
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Importing XML (XP/SP2)

    I am bringing data over from Excel in a specially made XML file. No matter what I do, it is arriving as text instead of numbers.
    <pre><?xml version="1.0"?>
    <dataroot xmlnsd="urn:schemas-microsoft-comfficedata"
    xmlns:xsi="http://www.w3c.org/2000/10/XMLschema-instance"
    xsi:noNameSapceSchemaLocation="Disc.XSD">
    <Others>
    <OtherDisc>
    <SKU>497</SKU>
    <BYear>2005</BYear>
    <Q1>0.02</Q1>
    <Q2>0.02</Q2>
    <Q3>0.02</Q3>
    <Q4>0.02</Q4>
    </OtherDisc>
    </pre>

    Sample of the XML file above. I am sure I am doing something dumb but really stumped. (I tried bringing the Excel file directly but it brought 65000 rows).
    <pre><?xml version "1.0" encoding "UTF-8"?>
    <xsd:annotation>
    <xsd:documentation xml:lang="en">
    Discounts schema for Sales Forecast Database.
    </xsd:documentation>
    </xsd:annotation>

    <xsd:element name="SKU" type="xsd:number"/>
    <xsd:element name="BYear" type="xsd:number"/>
    <xsd:element name="Q1" type="xsd:double"/>
    <xsd:element name="Q2" type="xsd:double"/>
    <xsd:element name="Q3" type="xsd:double"/>
    <xsd:element name="Q4" type="xsd:double"/>
    </xsd:schema>
    </pre>


    I wrote a VBA ditty <pre>Sub ImportReturns()
    Application.ImportXML "S:Budget05ForestBudgetInProgressSAP Uploads" _
    & "OtherDisc.XML", acStructureAndData
    DoCmd.OpenTable "OtherDisc", acViewDesign
    End Sub</pre>

    to bring the file in and open it so I can verify. The next step is to Append or Update exisiting files.
    Alan

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Importing XML (XP/SP2)

    <hr>(I tried bringing the Excel file directly but it brought 65000 rows).<hr>
    Usually the simplest way to solve this problem is to take the data you want in Excel and copy it to a new workbook with a paste, save it, and then import. It has the advantage of being much quicker than fiddling with XML orther programmatic solutions. I am not well versed in XML, so I'll let someone else look at that aspect of your post.
    Wendell

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Importing XML (XP/SP2)

    I took your sample, closed the tags and imported it into Access without any problems. What exactly are you encountering? To append, you can use exactly the same code but change the option to acAppendData.
    Charlotte

  4. #4
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Importing XML (XP/SP2)

    After beating my head against the wall, I discovered that I have a customer who finds it cute to duplicate things that are primary keys. After totally re-writing the code, I found on another extraction that the SKUs were skewed.

    Thanks for the help. I still plan to use XML to import one piece that I completed last week.
    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •