...
\uD83D\uDCD8 Instructions
Section 1 - Preparing Audit Database for testing
Backup up the MSERV database on the ASWBSQL2\ASWBSQL2 SQL instance
Restore backup of MSERV database as MSERVTEST on the ASWBSQL2\ASWBSQL2 SQL instance
Section 2 - Cleaning Social Worker
Open the audit file in Notepad++
Using find and replace:
Replace all Tabs with nothing
Replace double spaces with single space (you may need to run this multiple time to correct all instances
Save file with a new name
Close the file in NotePad++
Open the social workers to audit file in Excel
Excel should automatically display the “Text Import Wizard”
Select “Delimited” and press [Next]
In the delimiters group
Uncheck the “Tab” checkbox
Check the “Semicolon” checkbox
Ensure the “Text Qualifier” is set to double-quotes
Click the [Finish Button]
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.
In Excel, save the file as a Tab-Delimited file
Close file in Excel
Open the file in NotePad++
Using find and replace, replace all tabs with Semicolons
If there was a header column present, delete it.
Save and close the file
Section 3 - Testing Social Worker File
Open the MDCE.exe application located in the DBapps folder
Log into the MDCE program with the Test credentials
From the menu ribbon click on “Import New List”
Click the “Import Licensees” button
Navigate to and open the corrected social worker file completed in Section 2
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.
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
Open the audit file in Notepad++
Using find and replace:
Replace all Tabs with a space
Replace double spaces with single space (you may need to run this multiple time to correct all instances
Save file with a new name
Close the file in NotePad++
Open the Course file in Excel
Excel should automatically display the “Text Import Wizard”
Select “Delimited” and press [Next]
In the delimiters group
Uncheck the “Tab” checkbox
Check the “Semicolon” checkbox
Ensure the “Text Qualifier” is set to double-quotes
Click the [Finish Button]
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.
Make sure that the course name and sponsor values do not have a semicolon
Replace the semicolon with spaces
(OPTIONAL) ensure that the the values do not start or end with spaces (you can remove the spaces if you want)
Verify that all License numbers in the Course file are found in the Social Worker file
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
Verify that the Board Code for the License in the Course file matches the code in the Social Worker file record
If not, update the Course file value with the Social Worker license file
In Excel, save the file as a Tab-Delimited file
Close file in Excel
Open the file in NotePad++
Using find and replace, replace all tabs with Semicolons
If there was a header column present, delete it.
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.
Open the MDCE.exe application located in the DBapps folder
Log into the MDCE program with the Test credentials
From the menu ribbon click on “Import New List”
Click on the “Import courses (slow)” button
Navigate to, locate the file to import and open it
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.
\uD83D\uDCCB Related articles
...