Saturday, February 22, 2014

eClinicalWorks (ECW) FTP File Data

So, we recently ran into a problem on our eClinicalWorks (ECW) server with a disk that is failing and/or a corrupt file system. Specifically the disk used to store all of the FTP data for patient documents. The possibly failing/corrupt disk (and/or file system) was only partially available -- the data seemed to be accessible, however, you could not get a complete directory listing (we're assuming due to file system corruption). We could copy data off if we knew the file name that we needed.

All of these patient documents are stored on the server and accessible via FTP (service running on server). The client application then looks up the file names associated with a patient, and that's what you see listed in the client application window. We figured this data (the file names) must be stored in the ECW MySQL database. We approached eClinicalWorks tech. support about getting a full listing (query) for the database, however, they were unsure of how to do this.

I didn't know the credentials for the ECW MySQL database, but they were pretty easy to find on our system. I looked in "Scheduled Tasks" applet in the Control Panel and found a job called "mysql_optimize". I found the batch script the job used and the username/password for the DB was listed.

I then connected to the DB with the mysql application and poked around a bit. I ended up doing a complete dump of the database (SQL) to a flat file, then looked for a file name that we already knew (some were able to be listed on the file system). This led me to the correct table and column needed: document (table name), fileName (column containing what we need)

Then you can simply extract the data to a flat file:
mysql -uecwDbUser -pPASSWORD -P4928
use mobiledoc;
select fileName into 'c:/file_names.txt' from document;

That gave me a flat text file for all the patient files that belong in the FTP root; I could then loop over and run robocopy for each use a batch script. I hope this tip helps some else with eClinicalWorks!

20 comments:

  1. Hey Marc, I have some questions about querying an eCW db. I think there is a lot of misinformation out there spread maybe purposefully by eCW to get people to buy their paid reporting modules. Is it possible for me to e-mail you a few questions about this? Thanks

    ReplyDelete
    Replies
    1. Yeah I wouldn't doubt it... I've worked in IT for quite a while now, and I have to yet to come across another company quite like eClinicalWorks... the company, support, and product are well, special. Definitely not an ECW DB expert but I'd be happy to help: msmith626 at gmail dot com

      --Marc

      Delete
    2. Thanks for the that information. Its nice to know. I might have a similar situation coming up, and might need some assistance then. Would it be ok to reach out to you ? I do see you email listed on this thread, let me know if that would still be valid

      Delete
    3. Hi, yes, that is fine: msmith626 at gmail dot com

      Delete
  2. This comment has been removed by the author.

    ReplyDelete
  3. Hi Marc, we're looking to pay a consultant to extract data from eCW. Let me know if you can help or know of anyone. Thanks, Jeff Robbins, Dynamic Health IT jrobbins@dynamichealthit.com

    ReplyDelete
  4. Hi Jeff,

    Were you able to find an ECW consultant to perform data extraction? If so, can you share the contact?

    Thx

    ReplyDelete
    Replies
    1. I'd love to help you out if you still have a need! Let me know if interested!

      Delete
    2. I can do eCW data extract for you. contact me on - bhavinrbhavsar@gmail.com

      Delete
  5. Marc - Currently working with a client with ECW. Special is a mild way to put it.

    ReplyDelete
    Replies
    1. You'd be amazed at how many private inquiries I get from individuals or firms working with ECW, all just from this single blog post... we should start our own firm and offer ECW consulting services!

      Delete
  6. hey Marc, developer here for a company that uses doctor patient info for practice building. I have an appointment on Friday to see if I can export patient data from ECW to a format that I can import into my proprietary software. Can you give me some guidance since there seems to be none out there? Will ECW let me export to excel,pdf, CCDA or will I have to query the mysql directly? Any help would be very much appreciated.

    ReplyDelete
    Replies
    1. To clarify, I really only need metadata about the patients not medical data.

      Delete
    2. If they are not locally hosted then the best way to access the data will be via eBO (Cognos). You can write SQL from with in eBO or use the prebuilt metadata if available to pull any data you need and use the output options to format however you needed (csv, excel, etc)

      Hope this helps!

      PS: patient demographics are in the users table (filter where usertype = 3)

      Delete
    3. Excellent info that definitely helped us with integration with eClincialWorks. Do have any feedback on requesting CCD's through the web version, we tried that feature with multiple versions of eclinical works and it takes a very long time between the request and the availability of the CCD for download through the portal. in many cases the request fails after we wait days and weeks. Are there any tricks or thresholds you are aware of to request the CCD of a list of patients and download them? ….thanks, Hadia

      Delete
  7. I have established a business in data extraction and reporting from eCW! Odds are if it is in your database I can get it out for you! Check us out at www.z4healthintel.com or email to info@z4healthintel.com if you want more info!

    ReplyDelete

  8. Very nice posting. Your article us quite informative. Thanks for the same. Our service also helps you to market your products with various marketing strategies, right from emails to social media. Whether you seek to increase ROI or drive higher efficiencies at lower costs, Pegasi Media Group is your committed partner will provide b2bleads.
    E Clinical Works

    ReplyDelete
  9. The article is well-written. It is quite informative and attractive. We are the world's leading email database vendor. We at Pegasi Media Group deal with all types of companies and industries irrespective of their sizes and the type of their businesses. Our customized email database contains the customer's geographical location, their interests and other details. Our Contact Discovery Services successfully finds out the customer information and their product or service requirements, which allows you to know their requirements.eClinicalWorks Email List

    ReplyDelete
  10. Hello everyone!! I am wondering if anyone here knows how to run reports from the registry. Our clinic is having issues pulling data. I log tickets but it hasn't gone anywhere yet.

    ReplyDelete
  11. Hello! Am I able to export all patient data to pdf files?

    ReplyDelete