How can I best handle an imported Excel column, which Glide designates to be a text column when it should be a number column?
The first few rows should be empty but on import I see a single space in each cell. The rest have numbers.
My issue is that when I add a roll up column - in which I want to calculate averages - the average option does not appear. It offers only count options.
Any tips? Perhaps to duplicate the column and force a text to number format or something?!
This is from a one time import of data from Excel to Glide? Can you not simply edit the column, and change it from Text to Number?
No I want to be able to import the data from time to time and have everything instantly work. I don’t want to keep having to go in and modify column definitions.
I also cannot change the definition in the source file since it starts out as a CSV export from a Tableau dashboard.
But thanks for the suggestion.
Are you saying that on subsequent imports, it actually reverts the column type? Seems to me that you would only have to set the type once and be done with it.
Also, if you have data in your CSV that is not numeric (ex. Blanks or Spaces) then those would most likely be considered text (string) type columns because they contain non-numeric data.
Thanks Jeff. I will try changing the column type to number and then re-importing the file. If this works it would be great. Will have to be tomorrow now.
It seems there should be a solution to get it to import, or stay, as a number one way or another.
Brainstorming possible workarounds if simply changing to a number doesn’t retain it’s type on subsequent imports…
On the excel side try to make the first few rows that you said are empty (or spaces) to be zeros. If they are already zero, maybe try adding a decimal place, or a .00001 (something smaller than what your number is reporting in the corresponding glide column.)
Add a fixed dummy row as the first row of your excel data, with a number in this column. Invalidate the row one way or another so it doesn’t mess with your data obviously.
Hopefully some of these thoughts can help you find a solution you like tomorrow
What Jeff said
I find that it’s good practice to always check all columns types after adding new columns/tables via an external data source or import, because Glide often gets the column types wrong. But it only needs to be done once. I’ve never seen a column type change once it’s been initially set.
Thanks so much guys- this community is so generous in sharing support.
I tried Jeff’s recommendation and it works perfectly. Editing the column, re-designating as number type and then re-importing the Excel file does not disrupt the column type. I can now calculate averages using the roll-up column.
I quite liked the workaround using maths but it seems it’s not necessary.
This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.