Hi Laura,

Yes, labkey.insertRows is a good way to go. You do need to create the target table first, and that would be done separately through the main web UI. A list is probably the simplest way to get up and running.

Thanks,
Josh

Hi Josh,
Thanks! I tried this approach and it works fine, except that I'm having a problem with importing numeric columns with some NA values via labkey.importRows. If I convert this to a character and create the list w/ type=character, it's fine. But, I do need this as a numeric value. Is there a way to import numeric columns with NA values?

Error in labkey.importRows(baseUrl = "https://labkey-dev.roche.com:8443/labkey", :
  HTTP request was unsuccessful. Status code = 400, Error message = NaN is not a valid value for column 'cv'

Hi Laura,

What type of column were you trying? Integer or Double? Double might be able to accept the value.

Regardless, as an alternative approach, if you replace the NA values with NULL values prior to attempting to insert the data, it should proceed without problems. Perhaps we should be automatically doing this within the Rlabkey package.

Thanks,
Josh

Hi Josh,
I'm trying to import a numeric double. I think NULL is only for objects? Blank numeric values are NA in my data frame. I am able to get the import to work with NA values is to specify the column as text(string) in the List design, but I'm afraid of losing some precision and don't want to have to convert a character to numeric for any subsequent analysis on these results. The numeric values i'm trying to import are pvalues and CV. If I convert the NAs to a standard but nonsensical value like 9999999999, then it will import ok. For now, I'm using this workaround unless you happen to know a better way to get around it.

I'll have to test this out Laura, but my understanding is that you shouldn't have a problem with inserting a null value into anything provided that the data column doesn't have a "NOT NULL" constraint attached to it.

Let me do some quick testing and see if that's the case with the numeric double fields in a list. I'll check both R and also our Javascript API to see how the InsertRows works with both.

Regards,

Jon

Hi Laura,

I've opened a bug on this:

https://www.labkey.org/issues/home/Developer/issues/details.view?issueId=23996

Sorry for my confusion about NA vs NULL. You current workaround seems reasonable. As an alternative, I think this would work:

Split the data frame into two separate date frames. One will have all of the rows with non-NA values for the column in question, and the other will have all of the rows with NAs for that column. Delete the column in the data frame where all the values are NA. The insert them separately with two different calls to insertRows().

This should end up with all of the rows, and null values in the database for the rows that have NA, which is the desired end result.

Thanks,
Josh