Many practices and hospitals maintain referring or receiving attending lists to properly send results to the MD or practice who ordered a procedure or is involved in the patient's care. This article discusses how to import an existing referring list into EncaptureMD.
Note: This requires Administrator Privileges to perform.
Note: You must have Microsoft Excel on your computer and be facile with its use to perform this function.
From the (1) Admin menu, choose (2) Referring MD
Click the (1) Export button which will download an XL spreadsheet you can use as a pattern
Open the EncaptureMD sample XL spreadsheet
Then open the customer XL spreadsheet and modify that XL to match the EncaptureMD requirements. Once the transformation has been done, reimport the corrected spreadsheet.
Reimport the corrected XL spreadsheet
Click the (1) Import button, navigate to the file and import it.
After successfully opening the transformed file, all the referring names will be available within EncaptureMD.
How do I get the spreadsheet in the correct format?
There are many ways to do this and these suggestions may not work in all cases, but this may help.
Note: any transformation of data like this must be thoroughly evaluated by you to ensure accuracy
Customer's Original Spreadsheet
Spreadsheets can be kept manually or exported from a HIS/EMR into a similar format. In this example, the most important thing we have to do is separate the name into LN, FN and MI fields. In order to do that, we will
- Highlight Column A
- Copy Column A to the clipboard
- Open a new spreadsheet
- Paste Column A into the new spreadsheet
- Save As - see next step
Save as a text file
The goal is to save the column as text. On reimport, the Text Import Wizard will allow us to divide the columns based on commas and spaces
1) Rename the file something like "names.xls" and save to the desktop
2) Click Format and choose Tab Delimited Text (.txt).
The file should now be saved on the desktop.
Open the EMD Sample Spreadsheet
You can download the EncaptureMD XL spreadsheet from the Import tab (see above). Now, open that spreadsheet and navigate to
File->Import. Navigate to the "names.xls" file and open that file. You will see a window similar to the one above. Set the file type to Delimited, Start Import Row to 2 and click Next>.
Step 2, Split the columns
In this case, the delimiters are comma and space. The goal is to set the delimiters so that the data is divided into 3 columns. Note the "" marks. They will need to be removed in a successive step. Note that Treat consecutive delimiters as one is also selected and text qualifier is set to {none}. Again, the goal is to use the settings to create 3 columns.
Step 3, Split the Columns
The last steps allows you to pick the column data format. Default is General, which is correct, so just click Finish.
Import Results
Now the results have been imported. Note that most of the coumns are correct, but occasionally the middle initial column will contain a second name. For these variants, they will have to be manually corrected. Another way to import this data is to use only the comma as a delimiter and bring all the other names into the first name and leave the MI column blank. The actual steps here will vary based on the original format.
Remove the quotes
Each line begins and ends with a quote as a result of the text import. In order to remove the quotes, click Edit->Replace and find the ", replace with a blank (don't enter anything) and click Replace All.
Final Result
The names are now in their final columns, Some may have to be manually edited, but if done right, that should be <10% of the cases.
Other tips to help reformat spreadsheets
Remove Extra characters from a column
1) Note that the last name column, MD and M.D. is inconsistently used. The easiest thing is to remove. Use Edit->Replace to remove the extra characters. Set it to remove ",M.D." then click Remove All. Since it may be spelled differently, run remove again, this time with ",MD".
Combine 2 Columns
2) In order to combine Address1 and Address2 columns or any two columns, create a 3rd column to the immediate right (will become column G in this case) and for the second row, enter the formula into the second row of column G
=E2&" "&F2
Then grab the bottom right corner of that cell and drag to copy your formula to the cells below. The new column will contain the concatenation of columns E and F with a space between.
Two columns combined
Split city, state and zip into 3 columns
Need to divide city, state and zip code. Zip may be 5 or 9 numbers and may have a hyphen.
Use the strategy describe above for separating first name, last name and middle initial by copying the column into a new spreadsheet, saving the spreadsheet as text, opening the EncaptureMD spreadsheet and using the import command to pull the information back in through the text import tool. Set the delimiters to "," and " ".
If all else fails
Note the list of fax numbers. There are two major problems: Some of the numbers have a leading 1 and no hyphens and one of the numbers is too short. It may be possible to reformat the 1XXXXXXXXX numbers using an equation, but the number that is too short must be manually changed.
The point is that even with careful transforming, a manual review of the output data is necessary to ensure a quality result.