Import Pubmed XML (Child Nodes) to Access

[rpeare]

You're probably right, I do not know enough about XML to comment but I'm hell on wheels on parsing text files!

[orange]

I agree that altering original files can be an issue. I removed the dtd line and loaded the many tables. I notice that some tables there is a reference to PMID... so I think there are relationships even if not explicitly asked for or mentioned.

I also followed my original thought and read the file and just kept those records that had these terms ( a test really)

ID PubMedString
6 "<PMID Version=""1"">"
7 <ArticleDay>
8 <ArticleMonth>
9 <ArticleYear>
10 <Title>
11 <Affiliation>
12 <AbstractText>

This gave me a list as follows, that keeps the various fields within the PMID group

OOOps it's dropping the xml tags when I paste it here

PubId	KeptLine	jID
8	24581857	1979
25		1980
46	Faculdade de Odontologia, UNESP - Univ Estadual Paulista, Araçatuba, São Paulo, Brazil.	1981
52	Faculdade de Odontologia, UNESP - Univ Estadual Paulista, Araçatuba, São Paulo, Brazil.	1982
58	Faculdade de Odontologia, UNESP - Univ Estadual Paulista, Araçatuba, São Paulo, Brazil.	1983
64	Faculdade de Odontologia, UNESP - Univ Estadual Paulista, Araçatuba, São Paulo, Brazil. Electronic address: adelbem@foa.unesp.br.	1984
150	24494460	1985
172		1986
184	Canadian Academy for Esthetic Dentistry, University of Toronto Advanced Restorative Continuum, Canada. info@drmechanic.com	1987
262	24479280	1988
284		1989
306	Department of Preventive & Community Dentistry, College of Dentistry, University of Iowa, N335 Dental Science Building, Iowa City, IA 52340, USA.	1990
312	Dows Institute for Dental Research, College of Dentistry, University of Iowa, Iowa City, Iowa 52340, USA.	1991
318	Dows Institute for Dental Research, College of Dentistry, University of Iowa, Iowa City, Iowa 52340, USA.	1992
324	Dows Institute for Dental Research, College of Dentistry, University of Iowa, Iowa City, Iowa 52340, USA.	1993
330	Department of Pediatric Dentistry, College of Dentistry, University of Iowa, Iowa City, Iowa 52340, USA.	1994
454	24466626	1995
476		1996
488	General Dental Practitioner, Tadcaster, North Yorkshire, UK.	1997
614	24459747	1998
634		1999
653	Department of Cariology, Institute of Odontology Sahlgrenska Academy at University of Gothenburg Göteborg, Sweden. helen.isaksson@lj.se	2000

[ItsMe]

That's pretty cool. Earlier, I used the URL in the DTD and downloaded the DTD file. When I opened as text I could see some table headers that resemble what orange has there.

[Manish_05]

Hi friends, Many thanks to you guys. You are trying a lot to hep me.

Here i want to mention that, I tried to automate the import process for EXCEL by using VBA and i was successful in it. Only problem with it was it can not handle large file more than 30MB. SO i decided to import it by Access. But i am new to access to asking help from all you friends.

Here i am uploading the result file generated from the XML to Excel import tool. I have used the same XML file get the result which i have uploaded in my first post.

Maybe it will help you to parse the XMLfile, because after importing the XML file to Access it should look the same way and should contain the same number of results.

Here the Excel file XML to Excel Import.zip