Stripping characters for a Download Report

This month, we will show you an easy way to filter output in a download report to remove characters which will cause problems for the parsing of fields and records in the receiving system. A simple example would be when the other party asks for a comma delimited file without “quote qualifiers.”

In such a file, you need to remove any commas in the data or the comma in the field will be interpreted as a record delimiter. A more complex example is XML output, where you have to filter for certain reserved characters and replace them with some alternate text. Additionally, we will show how you can suppress the “Record Limit Reached” message from the end of your file that will otherwise be included. This can be inconvenient if you are using the record limit feature to make a sample file for an outside party because they will typically object to the extra data at the end of the file, and it can be tedious to remove it by hand.

Let’s look at the simple case where the other party wants a comma delimited file without quotes, making it impossible to send any data with an embedded comma. Since a MEDITECH patient name field always includes an imbedded comma, we will need to remove it, and it is probably a good idea to screen all fields that might include a comma to prevent the stray name or comment field from causing problems.

One way to remove all commas in a field would be to check each character in the string by position and remove the character if it is a comma. We use the “Not #” syntax to strip the offending character: Notice that the DAT attribute of all the fields on the report are set to INT. This will suppress any quotes around non-numeric fields (by treating them as numeric). You can do this to a standard field also, but if you do it to a date, you will change the format to YYYYMMDD, unless you use a computed field and %Z.date.out.

You can also change the data type of a standard field by adding a DAT= attribute:

This code puts the data from the field into a variable “STRING”, takes the length of the string, and then loops from position zero to the last position in the string, stripping out any commas found.

This will work fine, but there is an easier way, using the L() locate function in a DO loop:

An advantage of this approach is that if you had multiple characters to strip, you can just string them together in the L() function arguments, like so:

Here we are stripping the commas, hyphens, and periods from the string.

If you want to replace the comma with a space, we need to use $ (to the left) and % (to the right) with a space concatenated (_) in the middle:

If you have a lot of fields in your download, you can create a macro as a program and use it from your computed field. Assuming you write a utility report called MIS.USER.zcus.is.strip and call the macro “comma”: (I have placed a report with this name in our library).

The code in the macro looks like this (we replace the comma with a space in this utility):

Here is the output of a report that uses all these methods to strip the comma from the name field:

Let’s look at a more complicated example, such as an XML formatted export where we need to look for certain reserved characters when we are putting data between tags, and substitute a string for the reserved character as follows:

We can write a utility macro called “xml” that strips them as follows:

Notice that we have a two-step process to fix “&” and turn it into “&amp”. That is because if we directly searched for & and replaced it with &amp in a DO loop, the loop would be infinite as it would find the & in the “&amp” and be stuck until the string exceeds 255 and the program crashes. To solve this, we replace with “*amp;” first and then change the “*amp;” with “&”.

C/S and MAGIC versions of these example reports ADM.PAT.zcus.is.eupdate.strip and MIS.USER.zcus.is.strip has been uploaded to our report library.