Best practices in dealing with importing lengthy Kobo field column headers (>63char) into Postgres or other relational databases

User Support Data Management
1

Hi all,

In one of our use cases where we are importing form submissions from the KoboToolbox API into a PostgreSQL database, we are running into an issue with lengthy column headers. PostgreSQL imposes a maximum identifier length of 63 characters for column names, and sometimes the Kobo form question names can be much longer than that when they are found in a group (or several).

For example, this question column header is located within two groups, and consists of 95 characters in total:
group_wx66l18/Omcirkel_achter_elke_of_altijd_5_7dagen/_19_Had_ik_het_gevoe_e_niet_aardig_vonden

This came from a form that was designed using the KoboToolbox formbuilder, and the user may or may not have adapted the XML headers, and could have shortened them. But we would like to impose a column header rewrite to ensure that it can be imported as a last-ditch effort.

Our current thinking is to reverse the order and make it question/group/group, and truncate anything >63 by stripping the last 4 characters and adding _001 and so forth. But this could get tricky if the receiving database (and the code used to handle imports) has no knowledge of the entire Kobo form, and only whatever submissions are sent in, meaning we currently have no way to consistently assign the same numeric suffix to the same question repeatedly. (Although there are workarounds like creating a table in the database that keeps track of the question renames as they come in.)

I’m curious if others have had this same issue, and what kinds of solutions folks may have implemented.

2

@rudo, maybe you could follow two simple workarounds for this.

1. Keep the variable name as short as possible. Maybe name it Q1, Q2, … and so on.
2. You could uncheck the Include groups in header as shown in the image below:

Image 1
Image 11172×895 39.9 KB

Let’s wait to see if the community should have better solutions to your issue.

1 Like
3

Hint: Do complete pretests, please, including data export/import to your other tools, before starting data collection. Pay attention to naming rules etc. of external tools during Kobo form design already.

1 Like
4

Thanks both,

To clarify a few things, we are talking about gathering data from the KoboToolbox API, and I’m not sure there is an option to exclude groups via an API endpoint (I didn’t see anything like that in the documentation).

Secondly, we are building a system where many users are using KoboToolbox on their own and while we can encourage best practices in naming, we do not have control over what they name their column headers and we still need to make sure that any question naming convention (however suboptimal) can be imported. So we need to build in some last-ditch conversion (involving truncation etc.) to ensure their data can be imported within the range of the char limit regardless of whether they decided to follow these best practices or not.