Bug: CSV import results in wrong column type
When I import a csv file with a numeric column but more than half of the data is 0 or 1's, the column becomes a CheckBox column.
Less than half and it is (accurately) a Text/Number column.
Either the threshold should be much lower, or any non-Boolean number should force the column type to be Text/Number, or the user should be able to select the column type during import
I have submitted to support with three csv files.
Craig Williams
Comments
-
Hi Craig—
Not necessarily sure if this is a bug or not. It could be automatic logic that we perform on purpose to try and guess at the column types that you need without causing data corruption or misinterpretation on import.
Question: what are the drawbacks to changing the column type after import vs having an option to select the column type during import?
-
Changing column type from CheckBox to Text/Number results in
true, false
Not 1,0
If there are 49% numbers that are > 1 and 51% that are 0,1 that's pretty clear to me. 50% is arbitrary.
Craig
-
I am having the same problem.
Is there a fix or workaround for this issue?
-
Work-around #1:
In my specific case, I opened the csv file in an editor and replaced
,1^p
with
,ONE^p
and then imported. I then searched and replaced ONE with 1
(you need an editor the understands regex - my offending column happened to be in the last column)
Work-around #2:
If it hadn't been, I would have written a quick awk script (a one liner) to replace 1's in the offending column.
As in most things of this nature, it depends on the data.
Craig
-
My workaround:
Import .csv as is
Change column type from checkbox to text/number
Highlight the column
Ctrl+ F to replace "false" with "0", "", or null (your preference)
Repeat to replace "true" with "1"
-
I am having a similar problem except that I want for the column to input as text rather than a number. when I use Vlookup and the cell is a number (ie. 40144821), the Vlookup doesn't work. It does work if it reads as text however (ie. 40144821-01). The only work around I have found is to mannually re-enter the numbers. However, this applies to more than 50% of my cell values so is not a great option. I have tried importin csv and xcel files to the same end. I have also tried making the column a number field in the spreadsheet before importing. also does not work.
Any suggestions?
-
Wow, a five-year-old thread on this topic. This is terrible behavior. Maybe it's not a "bug" but the logic used to interpret column types is inscrutable.
Consider this csv file:
t1,t2,t3,t4 1,1,0,0.0 1,0,0,0.0 0,0,0,0.0 0,"hi",0,0.0
Importing this results in:
Column t1 is a text/number column
Column t2 is a checkbox column except it has some text in it
Column t3 is a checkbox column
Column t4 is a DROPDOWN column with only one option: 0
There is not a world in which this is a useful interpretation. Column t1 is numbers but column t3 is checkboxes? Column t2 is checkboxes+other but Column t4 is a dropdown? Column t3 and t4 are both effectively all zeros but have different types?
Almost everything I imported was a number but I can't add them together? If I add another column and put in a formula on the first row, this works: "=SUM([t1]@row:[t4]@row)" but this gives an error: "=[t3]@row + [t4]@row". (That's especially weird - SUM and + work differently?)
And this is what bit me: if I import two sheets, one of which has all "0" in a certain column and the other has other values in that column, they can't be brought into a report together - the two columns are interpreted as being of different types and so can't be combined in the report. Forcing me to go in and fiddle with column types when all I interpreted was a lot of numbers is extremely confusing.
It seems like it would be much better to just interpret everything as text/number - if I want to get fancy and turn things into other types, I can do that later. Or I could select the column type in the import dialog, with text/number as the default - that seems like a much better fit than (what seem like) arbitrary interpretations. If I'm importing data, it's almost certainly from a program that doesn't know about checkboxes, making a checkbox column is more likely to hurt than help!
Aaron
-
I should also note Shaine's question above: what are the drawbacks to changing the column type after import vs having an option to select the column type during import? It's a pain and it's confusing. I get that it's clever to do this interpretation, but the assumptions the tool is making (if a column is all 0, it must be checkboxes; if it's all 0.0 it must be a dropdown of 0s) don't make sense. Better to import it "vanilla" and let me decide where I want to get fancy.
As pointed out, when I change a checkbox column to a number column, I get text "true" and "false" instead of the numbers I actually imported. In my example above, if I change column t3 to "text/number" it fills with "false" and then "=[t3]@row + [t4]@row" gives "false0" - clearly not useful!
A
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives