mport Pubmed Xml (only Selected Child Nodes) To Access

[Manish84]

Dear friends,

I have cross posted m question at different forums, links of which are posted above. Below is my problem in detail.

I have an XML file which is downloaded from ncbi.nlm.nih.gov/pubmed

I am trying to import this XML file to Access by using VBA. I know Excel VBA but I have never worked with Access VBA, so I am finding it difficult to get the solution. There are few problems which I have facing while I am trying to do it manually.

- When I am trying to Import it, Access pops up a message saying DTD is prohibited. This error can be following these steps. Open XML file in notepad -> delete 2^nd line from the XML file -> save it
- Then if the previous error is eliminated by deleting the 2^nd line from XML file, then once the import starts, it pops up a message saying “Not all of your data has been successfully imported”. I don’t have any idea about this.
- Now once the data is imported with the error message, on left hand side many tables are being created (where as if I try to open this file in Excel it creates only one file), Don’t have any idea about this. Also no 2 tables have common column.

What I am trying to do.
a) Import XML file downloaded from Pubmed site to Access, in a single table.
b) There are more than 100 child nodes in the XML file (which varies from file to file); I don’t need to import all the fields to Access.
c) Only few files are required to be imported such as (Abstract, Article day, Article Month, Article year, Affiliation, Journal title, PMID)

Hope I have not confused with my words, please reply if you need any clarifications.

XML file to be imported pubmed_result(251).zip


Thanks & Regard,
Manish

[jonh]

I doubt Access (or any other (Office) application) could handle that xml in its raw form.
You'll need to create xsl transforms to convert the files into another format first e.g. csv and import those.

[Manish84]

John, I tried to manually import from CSV file, it is giving totally wrong result. I think we have use to VBA code to import from XML file to Access by using parse method, but the problem i am new to Access VBA. So i am not able to write the code.

If any of you can help me with this then that would be great help, as i am struggling with this for a month.

[jonh]

If csv isn't importing properly, use the import text wizard. After selecting the file, click Advanced to create an Import Specification. That lets you define datatypes for each field and various other things.

Save the specification (e.g. 'CSVImport1') and in your code you can use something like this

DoCmd.TransferText acImportDelim, "CSVImport1", "Table1", "C:\data.csv"