Example 1: Read Provider Data from a Microsoft Excel Spreadsheet
The Excel spreadsheet:
EMUE script:
'Create a list to hold data from the Excel file
CreateList ProviderList
'Build the connection string to configure the connection to the spreadsheet. HDR=Yes indicates that there is a header. This record will not be retrieved.
CONCATENATE ConnectionString "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" &FullInputFileName ";Extended Properties='Excel 12.0 Xml;HDR=YES';"
'Retrieve the data from the spreadsheet and store it in a list called ProviderList
GetOLEDBData ProviderList ConnectionString "SELECT * FROM [Sheet1$]"
:ReadRecord
If EndOfList ProviderList GoTo Finish
'Retrieve one record from the ProviderList, which is equivalent to one row in the spreadsheet
GetListMember ProviderList ProviderID ProviderName Primary_Taxonomy
'Write information to the Log File. Other processing could be added here.
LogLine "Provider ID = " ProviderID " Provider Name = " ProviderName " Specialty = " Primary_Taxonomy
GoTo ReadRecord
:Finish
'Count the total number of Providers and write it to the end of the Log File
GetListCount Count ProviderList
LogLine "Total Number of Providers = " Count
Exit
The contents of the log after running the script:
Example 2: Read Patient and Doctor Records From a Microsoft Access Database
Patients and Providers database tables
EMUE script:
'For each patient record in the Patients table, this script reads through the Providers table
'and finds the name of that patient’s provider. Then it writes that information to the Log file.
'Provide the information EMUE needs to read data from the database
'And create two Lists to hold patient and doctor database records
Copy ConnectionString "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\EMUE\\Providers\\InputFiles\\ProviderDB.mdb;"
CreateList PatientList
CreateList ProviderList
Copy QueryP "SELECT [PatientID], [ProviderID], [BillingAddress] FROM [Patients];"
Copy QueryD "SELECT [ProviderID], [Name] FROM [Providers];"
GetOLEDBData PatientList ConnectionString QueryP
GetOLEDBData ProviderList ConnectionString QueryD
'Read the next patient record and find his or her provider's name
:GetAnotherPatient
'If we have processed all the patients, we're done
If EndOfList PatientList THEN
GoTo Finish
Else
'Retrieve the next patient on the patient list
GetListMember PatientList PatientID ProviderID_Patient BillingAddress
'Find the Provider ID for this patient in the list of providers
FindListPosition ProviderList ProviderID_Patient 1 START
'Do not keep searching if the last provider list record has been reached.
If EndOfList ProviderList GoTo GetAnotherPatient
'Retrieve the matching record
GetListMember ProviderList ProviderID_Doctor Name
'If the ID numbers match, write the provider's name for this patient to the log
LogLine "Patient ID Number = " PatientID " Provider's Name = " Name
EndIf
GoTo GetAnotherPatient
:Finish
'Get counts of the number of records in both lists
GetListCount CountP PatientList
GetListCount CountD ProviderList
LogLine "Patient Count = " CountP
LogLine "Provider Count = " CountD
Exit
Contents of the log after processing has finished:
Example 3: Update Billing Records in a Microsoft Access Database
Billing database table:
EMUE script:
'Provide the information EMUE needs to read data from the database
Copy ConnectionString "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\EMUE\\\Databases\\ProviderDB.mdb;"
'And create a new LIST to hold database records
CreateList BillingList
Copy QueryB "SELECT [BillingID], [Invoice], [DateofService], [DateProcessed], [ProcessedFlag] FROM [Billing] WHERE ProcessedFlag = False;"
GetOLEDBData BillingList ConnectionString QueryB
:UpdateBillingRecords
If EndOfList BillingList GoTo Finish
'Retrieve the next record in the list
GetListMember BillingList BillingID Invoice DateofService DateProcessed ProcessedFlag
If DateProcessed NE "" THEN
'Build the statement that updates the ProcessedFlag and DateProcessed fields in the database
Copy QueryU "UPDATE Billing SET ProcessedFlag = True, DateProcessed = #" &today "# WHERE BillingID = " BillingID
'Perform the update
ExecuteOLEDB ConnectionString QueryU "Yes"
'Keep track of what has been done in the log file
LogLine "Billing ID Number = " BillingID " has been processed."
EndIf
GoTo UpdateBillingRecords
:Finish
GetListCount Count BillingList
LogLine "Total Number of Billing Records = " Count
Exit
Contents of log after script completion:
