XML to Excel

S

Sarah

I have an XML file (below) and I am trying to write a macro in excel
to extract the headers and data in to a spreadsheet.

Any advice would be greatly appreciated.

<?xml version="1.0" encoding="UTF-8" ?>
- <xml>
- <Person Key
<Reference>026527</Reference>
<Surname>BLACK</Surname>
<PrevSurname />
<Forename>JOHN</Forename>
<OtherForenames>PETER</OtherForenames>
<Initials>J P</Initials>
<Title>MR</Title>
<TitleSuffix />
<Sex>M</Sex>
<DateOfBirth>1977-11-29 00:00:00</DateOfBirth>
<DOBVerified>1</DOBVerified>
<DateOfDeath />
<DODVerified />
<NationalIDNumber>JJ418191C</NationalIDNumber>
<NationalIDValidType>GBR</NationalIDValidType>
<Nationality>GBR</Nationality>
<Salutation />
<FamiliarName />
<StateRetDate>2042-11-29 00:00:00</StateRetDate>
<ResidentOverseas>0</ResidentOverseas>
<LanguageSpoken />
</xml>

Thank you.
 
H

Harlan Grove

Sarah said:
I have an XML file (below) and I am trying to write a macro in excel
to extract the headers and data in to a spreadsheet.

Any advice would be greatly appreciated.

<?xml version="1.0" encoding="UTF-8" ?>
- <xml>
- <Person Key
<Reference>026527</Reference>
<Surname>BLACK</Surname>
<PrevSurname />
<Forename>JOHN</Forename>
<OtherForenames>PETER</OtherForenames>
<Initials>J P</Initials>
<Title>MR</Title>
<TitleSuffix />
<Sex>M</Sex>
<DateOfBirth>1977-11-29 00:00:00</DateOfBirth>
<DOBVerified>1</DOBVerified>
<DateOfDeath />
<DODVerified />
<NationalIDNumber>JJ418191C</NationalIDNumber>
<NationalIDValidType>GBR</NationalIDValidType>
<Nationality>GBR</Nationality>
<Salutation />
<FamiliarName />
<StateRetDate>2042-11-29 00:00:00</StateRetDate>
<ResidentOverseas>0</ResidentOverseas>
<LanguageSpoken />
</xml>
Click to expand...

This may be an ignorant question, but isn't this record ill-formed? There
doesn't appear to be a Person Key end tag. I wonder if that could be a
problem.

Are you using Excel 2003? Some subversions provide XML support. If you have
such a subversion, you may be better off using built-in XML import
facilities.

As a general matter, if you don't have a version of Excel that supports XML
import/export, you'd need to make use of Excel's inadequate text parsing
capabilities to do this in Excel. Spreadsheets are meant to crunch numbers,
not to parse text. You could do this in VBA, but it'd be MUCH easier if you
made use of VBScript regular expressions for parsing. It's almost certain
you have VBScript on your machine unless your IT department has been
particularly fastidious (and shortsighted).

However, there are Perl, Python, Ruby and Tcl modules freely available that
can convert record data in XML format into delimited or fixed width flat
text files. There are likely to be thousands of programmers and users world
wide that use these modules on a regular basis, so the quality of the code
is likely to be higher than most of the code you'd get here.

Here's one poor attempt at doing this in plain VBA.

http://google.com/[email protected]
 
S

Sarah

Harlan Grove said:
This may be an ignorant question, but isn't this record ill-formed? There
doesn't appear to be a Person Key end tag. I wonder if that could be a
problem.

Are you using Excel 2003? Some subversions provide XML support. If you have
such a subversion, you may be better off using built-in XML import
facilities.

As a general matter, if you don't have a version of Excel that supports XML
import/export, you'd need to make use of Excel's inadequate text parsing
capabilities to do this in Excel. Spreadsheets are meant to crunch numbers,
not to parse text. You could do this in VBA, but it'd be MUCH easier if you
made use of VBScript regular expressions for parsing. It's almost certain
you have VBScript on your machine unless your IT department has been
particularly fastidious (and shortsighted).

However, there are Perl, Python, Ruby and Tcl modules freely available that
can convert record data in XML format into delimited or fixed width flat
text files. There are likely to be thousands of programmers and users world
wide that use these modules on a regular basis, so the quality of the code
is likely to be higher than most of the code you'd get here.

Here's one poor attempt at doing this in plain VBA.

http://google.com/[email protected]
Click to expand...


Thanks for your help.

I am developing the code in Excel 2000. The code above is actually
just a snippet of the code so it does contain all the correct tags
etc.
 
Top