Reading lists of files from the file system

One of our clients recently told us how ActiveDataTools helped them quickly solve an urgent issue. The business sends out approximately 30,000 invoices each month to its customers. The invoices get generated from their billing system as PDF files and then get placed in a folder on their server, ready to be printed.

This particular month their billing platform crashed in the middle of generating the bills. Unfortunately, it doesn’t generate bills sequentially, so there was no easy way of knowing which bills it had produced and which it hadn’t.

They needed to set a flag in their billing database to mark which bills hadn’t been generated, so that the bill run could be restarted. Then they realised that ActiveDataTools could help them, using its powerful “Open from File System” feature.

To start with, they used the File System Importer to create a list of all the PDFs that had been created in the output folder of the billing system.

The system retrieves the list, along with other information such as filesize, filetype, etc. and creates a new worksheet:

The filename is in the first column. You can clearly see that each file is named “Invoice_” and then a invoice number. Once we have the list, the next thing we do is remove “Invoice_” and “.PDF” from the data, using the Find and Replace feature.

We now have a list of all the invoices that were successfully generated. Next we need to create a SQL Script to mark these invoices as ‘complete’ in the billing system. To do this, we use the Output Template Generator.

We create a row iterator in the template, which generates output for each row in our worksheet. This script generates an SQL UPDATE statement for each invoice.
When this template is executed, the following output is produced (snippet):

-- Invoice number: 1974998

UPDATE [Invoice]
  SET [GenerationComplete] = 1,
    [GenerationDate] = '20 October 2010'
  WHERE [InvoiceNum] = '1974998';

-- Invoice number: 2443311

UPDATE [Invoice]
  SET [GenerationComplete] = 1,
    [GenerationDate] = '20 October 2010'
  WHERE [InvoiceNum] = '2443311';

-- Invoice number: 3218762

UPDATE [Invoice]
  SET [GenerationComplete] = 1,
    [GenerationDate] = '20 October 2010'
  WHERE [InvoiceNum] = '3218762';

-- Invoice number: 1912506

UPDATE [Invoice]
  SET [GenerationComplete] = 1,
    [GenerationDate] = '20 October 2010'
  WHERE [InvoiceNum] = '1912506';

All we need to do now is run the script in our SQL Server database and we’re done.
The process of identifying the 20,000 or so invoices that had successfully been generated out of the 30,000 would have been very difficult and time consuming to do manually. Using ActiveDataTools and the above technique, our client was able to rectify their billing problem in less than twenty minutes.

Find out more about ActiveDataTools by visiting: www.activedatatools.com.

Products