Cleaning MD Audit Files

Owned by Robert Adach, created
Last updated: Dec 30, 2022
3 min read

This article will outline how audit files from MD, LA, NC and MA are to be prepared for loading into the production system. These files often contain formatting errors which we need to address before loading into the system

Prerequisites

  • Access to the MDCE.exe program in the DBApps folder

  • A text file from containing the list of Social workers to audit

  • A text file from containing the Courses to be verified

  • Microsoft Excel

 Instructions

Section 1 - Preparing Audit Database for testing

  1. Backup up the MSERV database on the ASWBSQL2\ASWBSQL2 SQL instance

  2. Restore backup of MSERV database as MSERVTEST on the ASWBSQL2\ASWBSQL2 SQL instance

Section 2 - Cleaning Social Worker

  1. Open the audit file in Notepad++

  2. Using find and replace:

    1. Replace all Tabs with nothing

    2. Replace double spaces with single space (you may need to run this multiple time to correct all instances

  3. Save file with a new name

  4. Close the file in NotePad++

  5. Open the social workers to audit file in Excel

  6. Excel should automatically display the “Text Import Wizard”

  7. Select “Delimited” and press [Next]

  8. In the delimiters group

    1. Uncheck the “Tab” checkbox

    2. Check the “Semicolon” checkbox

  9. Ensure the “Text Qualifier” is set to double-quotes

  10. Click the [Finish Button]

  11. The file should contain 12 columns, and you will need to review each line to make sure all of the data is correctly formatted for each column. Refer to the Social Worker File Format table below for details.

  12. In Excel, save the file as a Tab-Delimited file

  13. Close file in Excel

  14. Open the file in NotePad++

  15. Using find and replace, replace all tabs with Semicolons

  16. If there was a header column present, delete it.

  17. Save and close the file

Section 3 - Testing Social Worker File

  1. Open the MDCE.exe application located in the DBapps folder

  2. Log into the MDCE program with the Test credentials

  3. From the menu ribbon click on “Import New List”

  4. Click the “Import Licensees” button

  5. Navigate to and open the corrected social worker file completed in Section 2

    1. The process to import the records may take a while depending on how many records are in the file, and the speed of your connection.

  6. The system will report any errors it finds with the data in the file. No records will be saved if there are ANY errors

Section 4 - Cleaning Course File

  1. Open the audit file in Notepad++

  2. Using find and replace:

    1. Replace all Tabs with a space

    2. Replace double spaces with single space (you may need to run this multiple time to correct all instances

  3. Save file with a new name

  4. Close the file in NotePad++

  5. Open the Course file in Excel

  6. Excel should automatically display the “Text Import Wizard”

  7. Select “Delimited” and press [Next]

  8. In the delimiters group

    1. Uncheck the “Tab” checkbox

    2. Check the “Semicolon” checkbox

  9. Ensure the “Text Qualifier” is set to double-quotes

  10. Click the [Finish Button]

  11. The file should contain 7 columns and you will need to review each line to make sure all of the data is correctly formatted for each column. Refer to the Couse Audit file format table below for details.

  12. Make sure that the course name and sponsor values do not have a semicolon

    1. Replace the semicolon with spaces

    2. (OPTIONAL) ensure that the the values do not start or end with spaces (you can remove the spaces if you want)

  13. Verify that all License numbers in the Course file are found in the Social Worker file

    1. Often times the Social Worker file will have a Alphanumeric license number, but the CEU file will not. Update the value in the CEU file

  14. Verify that the Board Code for the License in the Course file matches the code in the Social Worker file record

    1. If not, update the Course file value with the Social Worker license file

  15. In Excel, save the file as a Tab-Delimited file

  16. Close file in Excel

  17. Open the file in NotePad++

  18. Using find and replace, replace all tabs with Semicolons

  19. If there was a header column present, delete it.

  20. Save and close the file

Section 5 - Testing the course file

While the system will only all errors the processing of the records take a very long time. It is suggested that you break the file down into smaller files of 150-200 records and try importing them as this is often much faster than 1 large file.

  1. Open the MDCE.exe application located in the DBapps folder

  2. Log into the MDCE program with the Test credentials

  3. From the menu ribbon click on “Import New List”

  4. Click on the “Import courses (slow)” button

  5. Navigate to, locate the file to import and open it

  6. The system will report any errors it finds with the data in the file. No records will be saved if there are ANY errors

Section 6 - Final actions

To ensure that the process will work without issues when the updated information is provided to MSERV, repeat sections 1, 3 & 5.

 Related articles