Any Data Magicians?

[LaRetta]

Can anyone help me

We need to verify the Medicaid coverage on 600-700 clients daily. We upload an FM text file on the State website. The results are displayed as a text report. I can copy only the text portion and paste it into Excel (see CopyPaste.xls with dummy data) or I can print it to file (attached PrintToFile with dummy data). I'd like to give the website, but it's a secure site. I have requested that they provide us with an option to download a delimited or fixed file but, of course, they just laughed (they are, after all, the State)

I

WebReport.zip

[RodinBangkok]

LaRetta, I've found a good flexible text editor such as BBEdit for Mac Users works well in these cases. Assuming the format of the file does not change from day to day, essentially you can create macro's to find/replace special characters, etc with the end objective to end up with a tab delimeted file ready for import. Takes some time at first, but once in place works quite well.

Rod

[LaRetta]

Hi Rod!

Well, I'm designing for x-plat but I currently don't have access to a Mac. Is there a Windows equivalent? Otherwise, I'll need to schedule time on one of our Macs in the middle of the night when it's not being used But at least I know I now have options. It sounds like it would certainly be worth it. Thanks so much for your help!

LaRetta

[BobWeaver]

See the attached file. Note the presence of a big yellow button. Hopefully this button will do what you want.

It should import and process the PrintToText file. Be sure to select tab delimited if it isn't the default.

Verify.fp5.zip

[LaRetta]

OH MY GOD BOB!!! You are brilliant!! This imports perfectly! I'm going to have to peek under the hood on this

Oh, you have no idea of the time this will save us. BLESS YOU BOB How in the world did you strip that silly heading? Oh, I'll figure it out alright ... dealing with the State ... I'm sure there will be similar such issues. You are so very much appreciated, kind sir! YOU DA DATA MAGICIAN all right!! Thank you! This absolutely tickles me!

LaRetta

[BobWeaver]

Sorry for lack of comments in the script; it was a quickie. Basically it imports everything into a raw text field, creating one record per line of text, and then skips any records that don't begin with yes or no. When it finds a yes or no, it looks at the next 1 or 2 records collecting the info into globals, and then creates a new record with that info. At the end, it deletes all the raw data records.

I didn't spend much time on this so I should mention one thing to look out for. I've seen similar print to text type reports that occasionally break a record across a page (eg, the line with eligibility and ID number at the bottom of one page and the next line with name, DOB, etc. at the top of the next page). It didn't occur in your short sample file, but there may be a possibility of it happening for a larger one. If that happens, it will require a few extra steps in the script to skip over the page break.

[LaRetta]

Oh Bob! Sorry for lack of comments in the script? You've got to be kidding

You're a life-saver!! Hmmm, I imported the original 40-page report and it works fine and the first page shows an overlap on the last client. "YES" or "NO" is on the first line and date is on the next page. The dummy data I sent you may not have overlapped but the real one does. Upon reviewing the import, it looks right! Several clients overlap pages all the way through the report. Would you want to look at the file first to be sure it's okay?

Personally, I think you nailed it!

LaRetta

[BobWeaver]

Well, I guess if it ain't broke, don't fix it. But, I would keep an eye on things for the first little while that you use it.

Maybe after doing the import just search for invalid dates in the verification date field. As long as these are okay the rest of the data should be fine.

Also make sure that the number of records you end up with, match the number in the import file.