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.
Go with your earlier suggest of converting the string to a number. I’d rather one of the previous solutions to not add another column (though the idea one above may need another column too, to invalidate the first row.) I haven’t looked into exact options of what method to use for this. Perhaps a math column, or if nothing else, JavaScript. Quick look, math seems it’ll do. Here I have the tags column as a text column, with the 3rd row having an entry of a space. I’m not getting a 0 for row three, but it is showing that the column type is number (based on it giving me option for decimal places and units.) Also in row 5 I showed how a small decimal place may be a fine substitute in your data for a 0… depending on what you plan to use with the data.
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.
Thank you.
This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.
