Merging 1600 XMLs Together to Query

I have 1200 XML files with an identical format but no schema. What's the best way to get that into an ACCESS files so I can start quering them.
FrylockAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
hieloConnect With a Mentor Commented:
Here is a complete working example:

/*
1. save this script as hielo.js under C:\Data
2. save the following two XML files under C:\Data
You can name them whatever you want. It makes no difference. The script will search
only for files ending in .xml
 
<?xml version="1.0"?>
<test>
 <username>Alpha</username>
 <password>a</password>
 <username>Beta</username>
 <password>b</password>
</test>
 
 
<?xml version="1.0"?>
<test>
 <username>Gamma</username>
 <password>g</password>
 <username>Delta</username>
 <password>d</password>
</test>
 
3. create a new database named Test.mdb under C:\Data
You do not need any tables.
 
4. Start > Run > cmd
 
5. At the command prompt type:
cscript c:\Data\hielo.js
 
6. When you see the access prompt window click "Open"
 
7. Done
 
*/
function GetFolderFileList(folderspec)
{
 
   var fso, f, f1, fc;
   var fileList = new Array();
   fso = new ActiveXObject("Scripting.FileSystemObject");
 
   f = fso.GetFolder(folderspec);
 
   fc = new Enumerator(f.files);
 
 
   while( !fc.atEnd() )
   {
   	if( /\.xml$/i.test(fc.item()) )
	{
		fileList[fileList.length] = fc.item();
	}
	fc.moveNext();
   }
 
return(fileList);
}
 
 
var XMLFilesFolder = "C:\\Data";
var DB_PATH="C:\\Data\\Test.mdb";
var objAccess = null;
var acAppendData = 2;
 
var files = GetFolderFileList(XMLFilesFolder);
if( files.length > 0 )
{
	var objAccess = new ActiveXObject("Access.Application");
	objAccess.OpenCurrentDatabase(DB_PATH)
	for( var i=0, limit=files.length; i < limit; ++i)
	{
		WScript.echo("\nimporting " + files[i]+" ...");
		objAccess.ImportXML(files[i], acAppendData);
		WScript.echo("Done");
	}
}

Open in new window

0
 
hieloCommented:
If you have a list of all the XML files OR if the files have a "specific pattern" (ex:
file1.xml, file2.xml, file3.xml, etc), then you can iterate over all the files. Example;
Const acAppendData = 2

Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase "C:\Scripts\Test.mdb"

Dim file
For i = 1 To 20
  file = "c:\scripts\file" & i & ".xml"
  objAccess.ImportXML file, acAppendData
Next

Refer to the following for more details.
http://www.microsoft.com/technet/scriptcenter/resources/officetips/oct05/tips1020.mspx
0
 
jerryb30Commented:
Can you post a sample?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
FrylockAuthor Commented:
Hielo - what is that - vbscript? Can I create a 'macro' and write that into the script?

Hielo - it's all the files in a directory. Is it possible to code it so that it appens all the files in a directory?

Jerryb30 - I can't. Proprietary data. But it's a simple XMLfile system.
0
 
jerryb30Connect With a Mentor Commented:
If you have spec that imports one xml, you can loop through all xml's in a folder, and run the import.  1600 times sounds like a lot, but if these are small xmls, it will not take long.

How do you currently import manually?
0
 
FrylockAuthor Commented:
Heilo - Compilation error:  told me 'expected hexadecimal digit'. Code with a minor edit below.

Jerry - I've never done it before. I don't have a spec.


/*
1. save this script as hielo.js under C:\Data
2. save the following two XML files under C:\Data
You can name them whatever you want. It makes no difference. The script will search
only for files ending in .xml
 
<?xml version="1.0"?>
<test>
 <username>Alpha</username>
 <password>a</password>
 <username>Beta</username>
 <password>b</password>
</test>
 
 
<?xml version="1.0"?>
<test>
 <username>Gamma</username>
 <password>g</password>
 <username>Delta</username>
 <password>d</password>
</test>
 
3. create a new database named Test.mdb under C:\Data
You do not need any tables.
 
4. Start > Run > cmd
 
5. At the command prompt type:
cscript c:\Data\hielo.js
 
6. When you see the access prompt window click "Open"
 
7. Done
 
*/
function GetFolderFileList(folderspec)
{
 
   var fso, f, f1, fc;
   var fileList = new Array();
   fso = new ActiveXObject("Scripting.FileSystemObject");
 
   f = fso.GetFolder(folderspec);
 
   fc = new Enumerator(f.files);
 
 
   while( !fc.atEnd() )
   {
   	if( /\.xml$/i.test(fc.item()) )
	{
		fileList[fileList.length] = fc.item();
	}
	fc.moveNext();
   }
 
return(fileList);
}
 
 
var XMLFilesFolder = "C:\temp\xml";
var DB_PATH="C:\\temp\\xml\\Test.mdb";
var objAccess = null;
var acAppendData = 2;
 
var files = GetFolderFileList(XMLFilesFolder);
if( files.length > 0 )
{
	var objAccess = new ActiveXObject("Access.Application");
	objAccess.OpenCurrentDatabase(DB_PATH)
	for( var i=0, limit=files.length; i < limit; ++i)
	{
		WScript.echo("\nimporting " + files[i]+" ...");
		objAccess.ImportXML(files[i], acAppendData);
		WScript.echo("Done");
	}
}

Open in new window

0
 
hieloCommented:
I tested what I gave you. If it is not working, then YOU did something wrong.
This:
var XMLFilesFolder = "C:\temp\xml";
should be:
var XMLFilesFolder = "C:\\temp\\xml";

Two slashes to separate the folders.
0
 
FrylockAuthor Commented:
Yeah, I kind figured that, which is why I posted my code back to you to see where I messed it up. Works great now, though a little slower than I'd prefer. No worries though. Thanks!
0
 
FrylockAuthor Commented:
Great guys. sorry I can only give you points. Keep up the good work.
0
All Courses

From novice to tech pro — start learning today.