SQL export routine doesn't handle NaN, Inf values


#1

The SQL export routine in CP 5122 XP32 passes values NaN and Inf into the output .CSV file, which are not recognized by MySQL in strict/traditional mode and cause import errors. It sounds like the SQL standard does not permit these values in double typed fields (per bugs.mysql.com/bug.php?id=5717). Perhaps they should be forced to zero or NULL (which would require changing the db schema).

thanks,
tim


#2

Thanks for this, Tim. I believe this has been addressed for the next release, but I will forward it to the member of our group that has most recently dealt with exporting. We will post back here soon.

David


#3

Tim,

We are aware of this issue, though a fix has not been implemented yet. A fix may get into the next release, but we cannot guarantee this.

Until then, the best course of action is to examine your CellProfiler settings and modify them to avoid divide-by-zeros, cycles without objects, etc., that might cause these values in the first place. Otherwise, if unavoidable, you can always edit your output files manually, though obviously this is not optimal.

Sorry for the inconvenience,
David


#4

A fix has been implemented for handling Inf values and will be in included in the next release. However, it appears that CPconvertsql.m should have been handling NaNs alright. Are you sure NaNs were a problem and not just Inf values?

David


#5

Hi David. Thanks for your response. I’m pretty sure I remember a NaN or few but in any case I’ve lost track of the materials to duplicate the output – sorry! I’ll try to be less lazy about including those next time.

In case there’s a difference, the output was produced with the (non-functional?) ExportDatabase tool and not the ExportToDatabase module. (Reading documentation is evidently not my forte…)

thanks,
tim