Solved

Merge 2 XML Files together

Posted on 2016-08-24
5
73 Views
Last Modified: 2016-08-25
Quite some time ago I received a huge amount of help from Geert Bormans on merging an XML and XLS(X) file together...now I'm back with some alterations to the XML file which hopefully someone - Geert :) - is able to help with.

The original questions can be found here:

https://www.experts-exchange.com/questions/28691313/Merge-Excel-file-with-contents-of-XML-file.html
https://www.experts-exchange.com/questions/28691770/Merge-Excel-file-with-contents-of-XML-file-Part-2.html

Everything has been working great but the scanner being used to create the XML file isn't working now and the new machine cannot output in the same way unfortunately so the XML file is completely different.

Essentially the job is still the same - take 2 files, convert them to XML and merge them together. The conversion to XML is working perfectly fine but it's the last part I'm having issues with as the structure of the XML file is different from what we had originally.

The files attached should hopefully give enough of an idea what I need but basically we need to group sections of the C225-BOX-235A.XML file together (we did this with a field called CORRESPONDENCE in the previous files). This is still in the C225-BOX-235A.XML file and we need to group them together again but this is now held as IndexValue with the Label GJH - this is the part which I'm confused with as I'm not sure how to alter the XSL file to handle this as when I attempt to merge them together I don't get the results I expect unfortunately.

I'm assuming I'm not creating the xsl:for-each-group area correctly and the DESCRIPTION and FILE fields are incorrect as well (again these are in the XML file as:

DESCRIPTION should be <IndexValue><Label>GJH</Label<Value>CORRESPONDENCE</Value> - I need the value element of this as the DESCRIPTION
FILE should be the contents of FileName but I need the location stripped out and only need the filename which is after the last \ - so I'd be looking for 001 CORRESPONDENCE.PDF

Hope that all makes sense (no doubt it won't) but any help/assistance is always appreciated

Thanx as always
merge-files.xsl
C225-BOX-235A-Converted.xml
C225-BOX-235A.XML
0
Comment
Question by:Steven O'Neill
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 41768667
Hi, I just checked both the old project and the new files.
It is all a bit vague and combersom

could you please simply post

A snippet of file 1
A snippet of file 2
and the expected result?

Thanks
0
 
LVL 2

Author Comment

by:Steven O'Neill
ID: 41768826
Hi Geert

Thanx for getting back so fast :)

I've attached a few files that hopefully give you an idea what it is we're attempting to accomplish.

  • File 1 (C225-BOX-235A.XML) is output from the scanner when the documents are scanned in;
  • File 2 (C225-BOX 235A-Converted.XML) is a converted XLS(X) file which we have run through a conversion process to make it XML (using Total Excel Converter);
  • File 3 (C225-BOX-235A.xml) is what should be output when both files are merged using the XSLT file (File 4 - merge-files.xsl)

We know the scanned documentation file will always be in an order (this is File 1 - C225-BOX-235A.XML). So we need to group File 1 (C225-BOX-235A.XML) using the CORRESPONDENCE field (although it's now shown as IndexValue in the XML file and we are looking to group these on the IndexValue with the Label = GJH and Value = CORRESPONDENCE). I tried to amend the XSLT file (File 4) that you provided by looking at <Documents> and grouping the <Document> together but this is where I've lost it as there are no fields like there was previously in the scanner XML file and it's using these IndexValue; Label and Value elements now.

Hopefully that makes a little more sense but if you need anything else then please let me know.

Thanx
C225-BOX-235A.XML
C225-BOX-235A-Converted.xml
C225-BOX-235A.xml
merge-files.xsl
0
 
LVL 60

Accepted Solution

by:
Geert Bormans earned 500 total points
ID: 41769244
   <xsl:template match="/">
        <xsl:apply-templates select=".//Documents"/>
    </xsl:template>
    
    <xsl:template match="Documents">
        <data-set>
            <xsl:for-each-group select="Document" group-starting-with="Document[IndexValues[IndexValue[Label ='GJH'][Value = 'CORRESPONDENCE']]]" >
                <xsl:variable name="pos" select="position()"/>
                <xsl:variable name="this-initial" select="$initial-file-data/record[position() = $pos]"/>
                <xsl:for-each select="current-group()">
                    <record>
                        <FORENAME><xsl:value-of select="$this-initial/Forename"/></FORENAME>
                        <SURNAME><xsl:value-of select="$this-initial/Surname"/></SURNAME>
                        <DOB><xsl:value-of select="$this-initial/DoB"/></DOB>
                        <CHI><xsl:value-of select="substring($this-initial/CHI, string-length($this-initial/CHI) - 9 +1)"/></CHI>
                        <COUNT><xsl:value-of select="PageCount"/></COUNT>
                        <FILE_DATE><xsl:value-of select="format-dateTime(current-dateTime(),'[Y0001].[M01].[D01] [H01]:[m01]:[s]')"/></FILE_DATE>
                        <EVENT_DATE><xsl:value-of select="format-dateTime(current-dateTime(),'[Y0001].[M01].[D01] [H01]:[m01]:[s]')"/></EVENT_DATE>
                        <FOLDER_ID>1</FOLDER_ID>
                        <DESCRIPTION><xsl:value-of select="IndexValues/IndexValue[Label ='GJH']/Value"/></DESCRIPTION>
                        <FILE><xsl:value-of select="tokenize(FileName, '[\\/]')[last()]"/></FILE>
                         </record>
                </xsl:for-each>
            </xsl:for-each-group>
        </data-set>
     </xsl:template>

Open in new window


will do most of it right
1
 
LVL 2

Author Closing Comment

by:Steven O'Neill
ID: 41769512
Absolutely sweet as always Geert :)
0
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 41769862
welcome
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Create a Windows 10 custom Image with custom task bar and custom start menu using XML for deployment.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question