Merge Excel file with contents of XML file - Part 2

Hi guys

Thanx to Geert yesterday via my previous question (https://www.experts-exchange.com/questions/28691313/Merge-Excel-file-with-contents-of-XML-file.html#a40843318) I was able to merge the contents of an XLSX file and an XML file to provide me with a completed XML file.

We now move onto another step that I need to work upon and hopefully it's something we can accomplish. I'll walk through the scenario we need to perform:


So Step 1 is that we receive the XLSX files and we would store them like this:


Step 2 would be to perform the scanning and the output files would be added to the directory like this:


Step 3 is after we've performed the merge of each file and the directory would look like this:


Step 4 - The newly created file appears in the sub-folder


From my earlier question (https://www.experts-exchange.com/questions/28691313/Merge-Excel-file-with-contents-of-XML-file.html#a40843318) I was able to determine we can merge the files together but we need to convert the XLSX files to XML before we can perform the merge work that Geert was so kind to walk me through.

In order to achieve this yesterday we created a .XSD file that we manually attached to the Excel file to allow us to do the mapping and the extract. I've attached an example of the Box 1A.XLSX file and the Box 1A-converted.XML file for comparison. The issue we have with the XLSX file is it comes to us with some fields merged together (so we have to remove the merge from these fields) then remove the blank columns. Doing this manually is easy enough - open the file; highlight the worksheet; right-click and choose to remove the merge; using Find & Select to remove the blank columns is quite simple but I'm looking to automate the create of the XML file from the XLSX if I can.

So I'm looking for advice on how we automate portions of this. We have the ability to do the work on a file by file basis but I'm now looking to perform this work on all files in a directory (we can output the files to a different directory if it helps matters). The idea would be that the job would be run once a day and everything contained in the folder would be used - Box 1A files joined together and output to the Box 1A directory; Box 2A files joined together and output to the Box 2A directory and so on...every night the folder area would be cleared out and all files/sub-directories would be removed and a fresh batch of data would be added the next day.

Hope this makes sense...I know it looks a little complicated but hopefully my directory snapshots and the files (plus the original question) make things a little easier to follow. If this has to be broken down into multiple questions (Excel conversion and then the automation process) then no problems and I'm happy to do this as well.

Here is a copy of the FileCreate batch file we have that pulls the 2 files and outputs them to a 3rd file. We need to automate this to pick up each file in the directory, with the corresponding ""-converted.XML file (so Box 1A.XML would be matched to Box 1A-converted.XML and so on) and we also need to create a directory based upon the name of the original XML file (the one without -converted at the end):

set root=C:\ProjectFolder\Project002\

set xslt=%root%ProjectFiles\merge-files.xsl

set saxon=%root%ProjectFiles\saxon9he.jar

set file1=%root%DataFiles\Box 1A-converted.xml

set file2=%root%DataFiles\Box 1A.xml

set file3=%root%DataFiles\Box 1A\Box 1A.xml

java -Xmx1200M -Xss3000k -cp %saxon% net.sf.saxon.Transform -xsl:%xslt% -

s:"%file2%" -o:"%file3%" initial-file-name="%file1%"

Select all Open in new window

Hope this all makes sense and thanx for looking.
Box-1A.xlsx
Box-1A-converted.xml
Import-mapping.xsd
merge-files.xsl
Box-1A.xml