Difference between revisions of "Office Task Automation"

Latest revision as of 16:24, 20 February 2017

vEdit is much more than just an "editor", It is a flexible tool that can assist with many office tasks that have nothing to do with "editing".

• Assistant in transferring data between databases and accounting programs.
• Clean up databases, extract data from databases, perform search & replace operations on a database.
• Extract all valid email addresses from log files. Sort the email addresses and remove all duplicates.
• Extract the text (e.g. form data) from a web page, removing all HTML codes.

The typical approach to automation is to write complex programs in Visual Basic or C++. With vEdit's powerful search and replace capabilities, columnar block operations, many ready-to-use macros, and automation features can perform many tasks without any programming. Some tasks may require custom "macros"; the advantage with vEdit is that these macros often consist of only a few search and replace commands.

vEdit's search & replace is exceptionally powerful and fast. It supports both "pattern matching" and "regular expressions". A few search & replace commands can often perform the equivalent of a 100+ line Visual Basic or C++ program.

The following is an overview of how vEdit can assist with the example tasks mentioned above. It is not intended to be a step-by-step guide. Hopefully you will see how vEdit can help with your specific needs.

Transfer Data Between Databases and Accounting Programs

Most databases and accounting programs have "export" and "import" functions, which "in theory" makes it possible to transfer data between them. However, in practice the "export" format and the "import" format are often incompatible.

For example, the export and import formats might be:

Export: James Phillips    201 Fairview    Miami  FL  33120  110301
Import: "110301","James","Phillips","201 Fairview","Miami","FL","33120"

A single search & replace in vEdit using "regular expressions" can convert the "export" format to the required "import" format. You could easily create a batch file or Windows icon to fully automate the conversion.

For more complex conversions, you might use vEdit's extensive "columnar" block operations. You can delete, insert, copy, move, fill and sort columns of data.

Export Databases to be Manipulated

Although vEdit cannot readily work with raw database files (other than xBASE), most database programs let you export a table as a text file which vEdit can then manipulate. Here are some examples:

You may find that some phone numbers in your database are entered as (555) 555-5555 while others are entered as 555-555-5555 or 1-555-555-555. Lets say you need them all in one format.

1. Export the table to a text file and open it in vEdit.
2. Two search & replace commands will correct the telephone numbers.
3. Save the file.
4. Import the text file and use it to replace the existing table.

You may find that your database has duplicate records that you want to delete.

1. Export the table to a text file and open it in vEdit.
2. ort the file based on the field which indicates the duplicate records.
3. A search & replace with "regular expressions" can delete the duplicated records (after sorting).
4. Save the file.
5. Import the text file and use it to replace the existing table.

This "export", "fix with vEdit" and "import" technique can be used with any database manipulations that are difficult or impossible to perform with the database program.

Working with Email Addresses Macros

vEdit includes several ready-to-run macros that can really help when working with e-mail addresses.

E-EMAIL.VDM

Extract all "From:" and "Reply-to:" e-mail addresses from an e-mail archive file, sort them and remove duplicates. Works well with Eudora .MBX files.

E-XTRACT.VDM

Extract all e-mail addresses from a data or log file, sortthem and remove duplicates.

EXTRACT.VDM

Extract all records (lines) from a file which contain a specified search string. You can even automatically extract records (lines) from entire groups of files. For example, you could extract all "@aol.com" addresses. This macro also has many database uses and is available from the menu function MISC > More macros > Extract.

REM-LINE.VDM

Remove all lines in a file which contain a string listed in a file of "remove strings". Works well for comparing two lists of items (e.g. email addresses) - creates a file of the different items and a file of the common items. The next example uses this flexible macro.

Export Prospect Addresses from Outlook Removing Customer Addresses from Access

Consider creating an email list of customers:

You want an email list of prospective customers that have inquired about a product, their email addresses are in Thunderbird. However, you must exclude customers that have already purchased the product, Those customer email addresses are in Outlook.

Here is an overview of the steps needed to perform this task:

1. Inside Thunderbird, create a temporary mail folder, e.g "Prospects". Copy all prospective customer email messages to "Prospects".
2. In vEdit, run the E-EMAIL.VDM macro. At the prompt for the filename, enter the full pathname to the file "prospects.mbx" in the Thunderbird folder. At the prompt for the destination filename, enter e.g. "prospects.txt". The macro will extract all the email address, remove duplicates, sort them, and save them in prospects.txt.
3. From Outlook, export the table containing the customer email addresses. Export only the email address field. Save the email addresses in a file, e.g. "customer.txt". If you have trouble exporting only the email addresses, use the vEdit macro E-XTRACT.VDM to extract the email addresses from the export file.
4. From vEdit, run the supplied REM-LINE.VDM macro. For the source filename, enter the pathname to "prospects.txt". For the remove list filename, enter "customer.txt". For the destination filename, enter e.g. "mailing.txt". For the matching filename, enter e.g. "match.txt". The macro will remove from prospects.txt any email addresses which are also in customer.txt. It saves the resulting file as mailing.txt. The file match.txt will contain the email addresses of (recent) customers that are on the prospects list. In other words: mailing.txt + match.txt = prospects.txt

When completed, you will have a list of email addresses in the file "mailing.txt" which consists of everyone that has inquired about a product, but hasn't purchased it.

Other Resources

• Automation Section
• Automation Overview
• Wiki Home