Define unit types or ignore quotes in csv file

[aks]

I have a CSV log file that puts quotes around every value :

Code: Select all

Timestamp,Temperature_CelsiusC,Relative_Humidity,Absolute_Humidity
"Nov 10, 2023 15:56:25","20.3","71","12.5"

I tried adding a second row with the unit types (string,float,float,float), but unfortunately the next row with real values has quotes and this appears to force Flow CSV Viewer to automatically switch the variable back to string type.

If I remove the quotes from the data, I get the result I want, but that's a real pain to do with all the datasets.

As a workaround I created new a variable using a formula, but I wondered if it was possible to achieve the result directly from the csv file contents, or have an option to ignore quotes with number data?

Thanks
Tony

[Waveworks]

Hello Tony,

I'm afraid there is no way to force Flow CSV Viewer to take these values as floats. Flow has a number of heuristics to determine the datatype for each column, and one of them is that if only one value in the column has quotes (or any character that wouldn't appear in a number for that matter), the whole column is taken as a string column. There are some options in the Preferences dialog under CSV Import to control these heuristics, but unfortunately, this one can't be disabled. I'll note the need for better control in future versions.

As you mention, one workaround is to use formulas like

Code: Select all

temp = float(Temperature_CelsiusC)

. The paid version Flow CSV Editor allows you to make these changes in-place, but you would still have to do it manually for each variable in each file. If you're familiar with Python or some other scripting language, I guess the easiest would be to write a script that runs through all your files and removes the quotes.

Best regards,
Gustaf

[aks]

Thank you Gustaf, appreciate the quick response. I can get the quotes out with scripting.

As a matter of learning/documentation, how does the unit definitions of the "2nd row" work, and what is the precise syntax?

[Waveworks]

Glad you solved it! With regard to your question, I assume you're asking about the "Second row is units" in Preferences under CSV Import. "Units" here refers to physical units like seconds, degrees, m/s2 etc. It's quite common in CSV files with measurements to put these units on the second row, with the same separator as the other rows, and then the actual data starts on the third row. So in your case it would be something like:

Code: Select all

Timestamp,Temperature_CelsiusC,Relative_Humidity,Absolute_Humidity
,degrees (C),%,g/m3
"Nov 10, 2023 15:56:25",20.3,71,12.5

If you open a file like this in Flow, you will se the physical units as an additional column in the Variable view.

The "Second row is units" option allows you to disable (select "No") or enforce (select "Yes") taking the elements of the second row as physical units. With "Auto", which is the default, there is a heuristic to determine it.

Hope this makes it clearer!

Best regards,
Gustaf

[aks]

Yes that's very clear now, thanks for explaining!