Andy, definitely check out Monarch Data Pump, Server or Personal edition:
Working with a colleague a couple of years ago we developed a program for a one-off data conversion exercise. It picked up text files (one at a time - the file name was a job reference and had to be retained) from a specific directory, ran Monarch, extracted the required data and then saved a new file in a 'raw data' format. These were then transferred to a Unix system where another program read the data and populated the data files for our target system.
We found we needed to build a delay loop into the process (but that maybe was simply due to the age of the PC's in use) since a new Monarch process was started for each file. (It was a one off exercise with changes right up to the last minute so we were not too concerned about making a long term production tool!)
The run was about 3000 files when we started out to develop but somewhat less for the real thing. Number of files would not be an issue EXCEPT some unmanagable (from our end) data variables meant that the process failed with a handful of records on each run (5 or 6). So we had a process (manual) in place to spot this and correct the anomalies.
A few errors seemed to be inexplicable - the records would process OK if run though the same model and batch file one at a time.
I have a copy of the .exe we used but not the source. (I did the Monarch bit, my colleague cut the process code). We have both moved since. I could try to contact him to see if he still has the original source somewhere if it might help.
However I think Mike's response is probably more appropriate bearing in mind you want a regular production system rather than a one-off conversion tool.
If you would like me to try to get a copy of the source send me a Private Message (I'll get the email) and I will see what can be done.
I have a customer who is e-mailed 1000+ pdf files every month who needs them all converted to excel files using a model file to extract specific data
so far we have created the model and used a 3rd party program to convert the files from pdf to txt all works great
we can do it file by file no problem using batch now we need to automate the process so has anybody written a vb program or anything else to pick up all these txt files in a directory parsing the model name input files and output file names automatically ??
can it be done as a script?
can it be done?
any ideas ?
Private Sub Command1_Click()
Dim FSO As New FileSystemObject
Dim strinputfld As Folder
Dim stroutputfld As Folder
Dim strfile As File
Dim stroutput As String
Dim done As Boolean
Dim i As Integer
If Not FSO.FolderExists(txtinput.Text) Then
MsgBox "Input Folder does not exist."
If Not FSO.FolderExists(txtoutput.Text) Then
Set strinputfld = FSO.GetFolder(txtinput.Text)
Set stroutputfld = FSO.GetFolder(txtoutput.Text)
For Each strfile In strinputfld.Files
stroutput = Left(strfile.Name, InStr(1, strfile.Name, ".")) & "xls"
If FSO.FileExists(stroutputfld.Path & "" & stroutput) Then
i = 2
While FSO.FileExists(stroutputfld.Path & "" & stroutput)
stroutput = Left(strfile.Name, InStr(1, strfile.Name, ".") - 1) & i & ".xls"
i = i + 1
done = DoMonarch(strfile.Path, stroutputfld.Path & "" & stroutput, txtmodelname.Text)
If Not done Then
MsgBox "Unable to Process file " & strfile.Path & "."
Public Function DoMonarch(inputfile, outputfile, model) As Boolean
Dim oMonarchObj As Object
Dim oinput As Boolean
Dim FSO As New FileSystemObject
Set oMonarchObj = CreateObject("Monarch32")
oinput = oMonarchObj.Setreportfile(inputfile, False)
If oinput Then
If FSO.FileExists(outputfile) Then
DoMonarch = True
DoMonarch = False
Make a form add 3 text boxes
name them txtinput, txtoutput, txtmodelname
add a button
call it command1 (this should be the default name)
copy and paste the code into the code view of the form.
Add the refrence for Microsoft Scripting Runtime
and then use as needed.
Send me a message if you have any questions.
[size="1"][ March 17, 2003, 01:51 PM: Message edited by: tcorley ][/size]