Opening T4, T4A, and Releve XML files in Excel for balancing purposes.
Issue: A client asked for assistance in balancing their T4, T4A, Releve XML file.
Solution: Naturally balancing can and should be performed using the delivered emPath reports. However, in addition clients should consider opening their T4, T4A, Releve XML files in Excel and producing column totals for balancing.
The steps to open the XML files in Excel are:
- Copy the XML file to a PC with Excel on it. Note that Excel 2003 or 2007 is required.
- Right click on the XML file then select "open with" and "Excel" from the menus
- Select "as an XML list"
- Click OK if you receive an error indicating "there is a problem with the specified XML schema source". The message will most likely also say "Click OK to have Excel create a schema based on the XML source data".
- Create Excel totals for each column and compare to the emPath reports
Note that French accent marks can cause Excel a problem when opening the Releve XML file. To resolve this I recommend:
- Make a copy of the R1.xml file called R1_without_accent_marks.xml
- Right click on the R1_without_accent_marks.xml file and select open with then Excel
- Click the radio box "As an XML list" then click OK
- Click details on any XML import error message box which appears. Note the line number and column
- Open the R1_without_accent_marks.xml file in Textpad or Notepad.
- Using the line number and column noted, locate the accent mark which caused the import error.
- Replace all occurrences of the accent marked letter with the same letter without an accent mark
- Repeat steps 2 through 7 until the file opens in Excel without an import error