Commcare-export and data types

I have an issue with commcare-export, it always wants to alter the type back to text (unless of course I use strict-types) but when first creating the tables it will always use ‘text’, we have many fields and calculated fields that are ‘integer’ in commcare but I’ve noticed the forms api always returns these values as quoted strings, so it would return ‘43’ for age, and I understand numbers in JSON should not be quoted otherwise they are considered strings.

These quoted integers are then understood as a string by commcare export and best_type_for will always suggest text for postgresql.

I may be completely off and misunderstanding the issue, can anyone share their thoughts around the ‘everything is text’ issue with commcare-export?

Hi Javier

The best way to deal with this is to use one of the data type conversion functions. For fields that you are expecting to be numbers you can place str2num in the Map Via column.

There are more details on the help site: https://confluence.dimagi.com/display/commcarepublic/CommCare+Data+Export+Tool#CommCareDataExportTool-DataTypeConversion

Hi Simon! Thanks, I thought about it, but was wondering if quoting numbers on json was just a temporary ‘feature’.
Now that I got your attention, I am also working on a way to automatically create a query excel file that contains all the form questions and also create a query file for each repeated group, etc. But maybe this has already been done and you can point me in the right direction? If not… I was planning to get all the fields using the API and then just use openpyxl to create the query fields, do you think that would be a good way to go about it?

The quoted numbers come from the form because all form properties are untyped and treated as opaque strings. I don’t see that changing.

In terms of your second question we don’t currently have a way of doing that. I’ll ask around internally to see if anyone has done anything like that.