Automating process to download three files, merge 2 of then, then compare those files for differences

[paynegaz]

I have a process of comparing two .csv files to find where LastName, FirstName, and DOB do not match.  I receive two files from our client, I merge those files with no duplicates and then I compare them by pulling last name from our database file for the day to a find feature in the .csv merged file.  The goal is to find names on database not in two reports received and merged file (exception Report).  This is presently done daily as has had 30 to 300+ names to compare.  I would like to automate this through AutoIT.  Do you have suggestions?

Below is the steps in this manual process at present:

Automating the HINAZ Client Report Process

 

The present process involves many steps through several different software systems:

1. FileZilla-download 2 notepad .csv reports from client in AM and PM of previous day into merged file
2. Our Database-generate a report of names accessed by client employees from previous day, download as .csv or Excel report to client file
3. Merge 2 notepad Reports 01 and 02 into merged .csv file, save as merged file
4. Open Database report, add “Found” column, then copy last name of first patient name
   1. Open “Find” function in client Merged file
   2. Paste last name into “Find” box
   3. Review last names, manually scan for match to First name in merged file
   4. If match on Last and first name, the match to date of birth
   5. If all match, go to next name
   6. If no match, mark User Last Name, User First Name,  Last name, First name, Date of Birth in yellow marker to mark for client Merged File, then go to next name.
   7. Continue through names until come to end of database list
5. Open a client Exception Report for wexceptions.docx
   1. Change date to report date
   2. Review database report for IP Addresses, if match, then go to next IP Address, if not an IP Address match, then count, place total in IP Exceptions area of report.
   3. Count number of yellow marked names, place total in word document
   4. Copy yellow marked names into exception word report
   5. Change report completed date to today’s date
   6. File->Save as-> File Save As Type To: PDF->Archive->client Exception Report clientexceptionreportReportdate_exceptions.pdf
   7. Close client Exception Report for w/exceptions.docx->save
6. Send secure Exceptions Report to
   1. Open Cisco Secured E-mail- Res Login
   2. Input email address
   3. Input password
   4. Open client e-mail contacts.docx
   5. Copy e-addresses to Cisco Res To: and CC:
   6. Enter “Exception Report for report date” in Subject or “Exception Report for report date-No Exceptions” if no yellow marked names.
   7. Add Attached is the “Exception Report for Report Date” or No Exceptions above”
   8. Add Thanks
   9. Add HIE Name
   10. Click Attachments, Click Browse->Achieve->input select ClientExceptions ReportReport date->click add, click done,
   11. Check information, Click Send
   12. Close Cisco RES
   13. This process many different programs, can the processes be put into macros, then tied together with AUTOIT or is there a easier, better way to do it? 

Edited January 3, 2014 by paynegaz

[jchd]

Welcome to the forum!

This is a good job for AutoIt. I suggest loading all FTPed csv files into a local SQLite database, which will allow you to perform the merge and comparisons you need easily and reliably in one step. Keeping 100 years of history is no problem. UDFs exist to automate Office Word and Excel.

If you decide to get wet with this, you'll find solid help here.

[paynegaz]

Welcome to the forum!

This is a good job for AutoIt. I suggest loading all FTPed csv files into a local SQLite database, which will allow you to perform the merge and comparisons you need easily and reliably in one step. Keeping 100 years of history is no problem. UDFs exist to automate Office Word and Excel.

If you decide to get wet with this, you'll find solid help here.

Hi jchd:

Thanks for your help!  I am new to AutoIT, can you help me in getting started with this project.  Where should I start?  What variables to create?  Can I include Excel macros into AutoIT?

Thanks,

paynegaz

[jchd]

Sorry for delay in answering.

My advice for now is to start building the skeleton of your application to first learn how to use the main components you'll need. FTP, database access, generation of Word report based on a suitable template, send mail, ...

For a first step it isn't bad idea to simulate actual actions you don't master in detail, by means of dummy functions returning constant strings.

Once you get more proficient with the language and step by step more confident with how the building block will have to behave, then you can start replacing dummy functions by real-world ones.

If you try to write everything from scratch all at once I'm afraid you're going to spend much more time and overlook a required robustness of the code structure. This of course is only true if you expect long-term regular use (implying maintenance and flexibility for future changes/needs).

[paynegaz]

Sorry for delay in answering.

My advice for now is to start building the skeleton of your application to first learn how to use the main components you'll need. FTP, database access, generation of Word report based on a suitable template, send mail, ...

For a first step it isn't bad idea to simulate actual actions you don't master in detail, by means of dummy functions returning constant strings.

Once you get more proficient with the language and step by step more confident with how the building block will have to behave, then you can start replacing dummy functions by real-world ones.

If you try to write everything from scratch all at once I'm afraid you're going to spend much more time and overlook a required robustness of the code structure. This of course is only true if you expect long-term regular use (implying maintenance and flexibility for future changes/needs).

JCHD:

Thanks for helping. I have reviewed the material you recommended.  If I understand your answer, I should take a small portion of this project and do a dummy script.  For example, I could try downloading the .csv files into SQLite. Should I do that through a macro?  I attempted to run an Excel macro to record my keystrokes of downloading the files, but when I look in the Visual Basic Editor it has basic code, setting up the file, but no recorded keystrokes shown.  Am I doing this incorrectly?

Thanks again in advance for your help and advice.

Edited January 14, 2014 by paynegaz

[jchd]

Divide and conquer is always a pretty good strategy.

First use embedded FTP support to fetch the .csv data files.

Then use ADO (ODBC) support to grab data from your database, bypassing the creation and processing of yet another .csv.

Probably the easiest way to merge and filter the various datasets is to insert everything in a single local SQLite DB which you can query as needed.

The Word UDF will help you create the final report.

emailing that to the client will complete the job.

You should find all the building blocks in standard UDFs which come with AutoIt, and some other UDFs you can find in Example Scripts forum.

Remember that the less you depend on external programs to achieve your goal, the less maintainance you'll have to do. Recording keystrokes to have Excel massage your data will reveal more fragile than processing it yourself.