Exporting Relational/Hierarchical Data to Excel

We recently released support for exporting form entries to Excel. Yeah, we can fill out spreadsheets! However, in this post I will explain why Cognito Forms really is not a spreadsheet-filler, and the challenges we faced to make this feature available and meaningful for our customers. Before delving into the technical details of exporting to Excel, I will start by explaining how Cognito is designed to help you capture really useful data—data actually worth exporting!

Form Entries Are Real Data

One of our core goals with Cognito Forms is to allow our customers to create rich forms that capture real data. Real data is my term, but is sometimes referred to as “structured data” or “discrete data”—the complete opposite of “unstructured data” or “continuous data”. Real data to us also means good data, so not only broken down appropriately, but also reasonably valid. The best way to describe this, and make it real, is to start will a real-world example!

Medical history forms are a sore spot for both medical professionals and patients. They are tedious to fill out, but if not accurately filled out they can undermine the medical care provided to patients. For example, here is the current medications section on a Johns Hopkins medical history paper form:

Most web-based form builders would represent this section of the form as just a single big box of text:

With Cognito Forms, we wanted to ensure that data collected electronically is easy to enter while also being accurate and useful as it is reviewed by people and as it flows from one electronic system to the next. Here is how the Cognito version of this form might look:

In this case, the form uses repeating sections to allow users to enter each medication separately, provides suggestions for drug names to improve accuracy, and captures dosage information as discrete numerical data. The form provides useful guidance to the patient or responsible party filling out the form and makes their job easier and less error prone.

Eventually this higher-quality, discrete data may flow into an Electronic Health Record system, and after review by medical professionals, go into the patient’s medical record. These EHR systems, in turn, are then able to perform analysis and immediately warn physicians about possible dangerous drug interactions with a drug they are considering prescribing, while they are still in the room with the patient! I am not saying that Cognito Forms makes people healthier, but real data, captured the right way, direct from the source, can improve almost any situation.

Exporting Real Data Is Hard

So, what does all of this have to do with exporting to Excel? Why is this important? In this simple example above, patients are filling out lists of medications on one form. In a traditional export, each medical history form would be exported to one row on a spreadsheet—a proverbial spreadsheet filler. However, with Cognito Forms, this is real data that does not fit the mold. With Cognito, we can export the data to Excel while maintaining the richness of the data collected. The exported data is easy to review and read, but at the same time, we understand that exports usually translate into imports into other systems, and the export format must facilitate this. Here are some of the problems and solutions we worked through as part of our export feature:

Column names

Okay, this seems simple, but we had to decide what the names of the columns should be in the export. We could have taken the easy route and just used the field labels, but this has numerous issues. “Name of drug” is pretty straight forward, but it contains spaces and might mess up imports into other systems. What if the label contained a funny punctuation mark? What if two fields have the same label? What if there is no label (for some design reason)? What about sections?

Taking all of this into consideration, we decided to use the unique names for each field that Cognito maintains automatically for you. These names are always unique, do not contain funny spaces or punctuation, and are still generally easy to read. For example, “Name of drug” would translate to “NameOfDrug.” Two fields with the same name of “Description” would be “Description” and “Description1” so you would always be able to tell them apart. Also, for sections (not repeating) we chose to prefix the field names with the section names, so an “Email Address” field in a “Demographics” section would be “Demographics_EmailAddress.”

Data types & formatting

In our medical history example, the drug start date is a date and the dosage is a number. In keeping with our policy, the export must maintain the integrity of the data. So we worked hard to export dates, numbers, currency, percentages, etc. as appropriately formatted dates and numbers in Excel. Anyone that has dealt with an Excel file exported from another system will tell you how important this is. In turn, the start date column in the spreadsheet can be immediately used in Excel Date functions. Currency values look all nice and pretty with dollar signs but also work correctly when you go to sum them up!

Repeating data

Now it is time to cover the most challenging issue we faced with export. Cognito Forms supports repeating sections, enabling forms like medical history that capture repeating data, sometimes called relational data or hierarchical data. We had already decided not to flatten this data into a single worksheet, at least for our default export format, because we wanted an export that maintained the integrity of the data captured. In our view, whatever we export should be able to immediately import back into Cognito without affecting the form entry data in any way.

We eventually decided to keep fields and non-repeating sections on a single sheet—because this is easy to understand—and create new sheets for each repeating section. Here is an example of such an export for our medical history form:

As you can see, our simple medical history form, containing basic demographics (patient name) and current medications, is exported into two sheets. Each additional repeating section added to the form (including nested repeating sections) would appear in additional sheets. We include information on these sheets to link the data back to the parent data. The names of the sheets match the names of the repeating sections, which again are ensured to be unique (because this is a requirement in Excel). Also, the names of the ID columns correspond directly to the names of the sheets, so no confusion there.

With the data broken out and related, it is easily imported into other systems. Also, through tools like Excel PowerPivot and PowerView, the data can be easily related, aggregated, and visualized through pivot tables, charts, and dashboard reports. Finally, we are now positioned to support importing this data right back into Cognito, enabling bulk import of new entries or bulk updating of existing entries.

This is not the only way to export hierarchical data, and we may support other approaches or custom exports in the future to meet different needs. However, we feel this is a great start towards achieving our goal of capturing real data and making this data available to you in the most useful format possible.

Disclaimer

All patient medical data in this post is fictitious. If your organization is interested in creating HIPAA compliant medical forms, please visit our help topic to learn more about entering into a business associate agreement with us.

Also, my wife is a pediatrician, so I must add that EHR systems, while helpful, are not a panacea—but tools to assist caring qualified medical professionals.