Welcome to SPN

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

Sign Up Now!

Importing XML (by batch) into Access database

Discussion in 'Information Technology' started by l, Jul 28, 2006.

  1. l

    l
    Expand Collapse
    Guest

    Hi,
    I am needing to import batches of XML files into an Access database.
    Is there a way to do this through VBA macros?

    Thanks for your help,
    Louis
     
  2. Loading...

    Similar Threads Forum Date
    IBM focusing on XML development with tools (InfoWorld) Interfaith Dialogues Apr 30, 2005

  3. Ron2006

    Ron2006
    Expand Collapse
    Guest

    The following code will download and save XML from the web....

    ==============================
    fh = FreeFile

    Set msXML = CreateObject("Microsoft.XMLHTTP")
    msXML.Open "GET", strWebsite, False
    msXML.SetRequestHeader "Content-type", "text/xml"
    msXML.send
    strpageContent = msXML.responseText

    Open "c:\OSdrill.txt" For Output As #FreeFile
    Print #fh, strpageContent


    Set msXML = Nothing
    Close #fh
    =============================

    and then

    docmd.transfertext acimporthtml etc.

    will import html table information into access.

    =============================

    Ron
     
  4. l

    l
    Expand Collapse
    Guest

    Thanks Ron,
    Would this also work on multiple XML documents in a local folder?


    Ron2006 wrote:
    > The following code will download and save XML from the web....
    >
    > ==============================
    > fh = FreeFile
    >
    > Set msXML = CreateObject("Microsoft.XMLHTTP")
    > msXML.Open "GET", strWebsite, False
    > msXML.SetRequestHeader "Content-type", "text/xml"
    > msXML.send
    > strpageContent = msXML.responseText
    >
    > Open "c:\OSdrill.txt" For Output As #FreeFile
    > Print #fh, strpageContent
    >
    >
    > Set msXML = Nothing
    > Close #fh
    > =============================
    >
    > and then
    >
    > docmd.transfertext acimporthtml etc.
    >
    > will import html table information into access.
    >
    > =============================
    >
    > Ron
     
  5. Ron2006

    Ron2006
    Expand Collapse
    Guest

    As far as I know you cannot concatenate the files. I believe it
    basically will load 1 table per file. I practiced first just doing it
    manually to see what it would do. In this case this was the end of a
    larger process. I had to download about 100 XML pages. However when I
    looked inside I found that the data I wanted was the 7th table on each
    of those pages. What I ended up doing was using vba to open my
    sequentially named files one at a time and reading through each until I
    got to the 7th <table> then writing each line in that file until I got
    to the </table>. But I did not include those two identifiers. My output
    was a larger file that contained the guts of each of the about 20 input
    xml files. (I supplied the leading <table> and trailing </table>.) Then
    I used the importxml to import that larger table. Actually that whole
    process went a lot faster than I thought it would.

    If the table within the xml has a name associated with it (I am not
    sure of the format) then you may be able to be selective as to what is
    imported if there is more than one table in the document.

    Hope this helps.

    Ron
     
  6. l

    l
    Expand Collapse
    Guest

    Ron,
    Thanks for all your help. I'll give it a try.

    Regards,
    Louis


    Ron2006 wrote:
    > As far as I know you cannot concatenate the files. I believe it
    > basically will load 1 table per file. I practiced first just doing it
    > manually to see what it would do. In this case this was the end of a
    > larger process. I had to download about 100 XML pages. However when I
    > looked inside I found that the data I wanted was the 7th table on each
    > of those pages. What I ended up doing was using vba to open my
    > sequentially named files one at a time and reading through each until I
    > got to the 7th <table> then writing each line in that file until I got
    > to the </table>. But I did not include those two identifiers. My output
    > was a larger file that contained the guts of each of the about 20 input
    > xml files. (I supplied the leading <table> and trailing </table>.) Then
    > I used the importxml to import that larger table. Actually that whole
    > process went a lot faster than I thought it would.
    >
    > If the table within the xml has a name associated with it (I am not
    > sure of the format) then you may be able to be selective as to what is
    > imported if there is more than one table in the document.
    >
    > Hope this helps.
    >
    > Ron
     

Share This Page