3 Replies Latest reply: May 15, 2014 9:54 AM by tcorley _ RSS

    Automation

    andyp _

      Hi

       

      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 ?

       

      thanks

        • Automation
          Mike Urbonas

          Andy, definitely check out Monarch Data Pump, Server or Personal edition:

           

          [url="http://www.datawatch.com/dataconversionsoftware/data_pump.htm"]http://www.datawatch.com/dataconversionsoftware/data_pump.htm[/url]

           

          Mike

          • Automation
            Grant Perkins

            Andy,

             

            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.

             

            Grant

             

            Originally posted by andyp:

            Hi

             

            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 ?

             

            thanks /b[/quote]

            • Automation
              tcorley _

              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."

                      Exit Sub

                  End If

                   

                  If Not FSO.FolderExists(txtoutput.Text) Then

                      FSO.CreateFolder (txtouput.Text)

                  End If

                   

                  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

                          Wend

                      End If

                       

                       

                      done = DoMonarch(strfile.Path, stroutputfld.Path & "" & stroutput, txtmodelname.Text)

                      If Not done Then

                          MsgBox "Unable to Process file " & strfile.Path & "."

                      End If

                  Next strfile

                   

                   

                  MsgBox "Done..."

                   

                       

                       

                   

                   

                   

                   

                   

                   

                   

                   

                   

              End Sub

               

              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

                          oMonarchObj.Setmodelfile (model)

                          oMonarchObj.Exporttable (outputfile)

                          oMonarchObj.Exit

                  Else

                           oMonarchObj.Exit

                  End If

                   

                  If FSO.FileExists(outputfile) Then

                      DoMonarch = True

                  Else

                      DoMonarch = False

                  End If

               

              End Function

               

               

              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]