Bug: CSV import results in wrong column type

J. Craig Williams

 

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

Shaine Greenwood

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?

J. Craig Williams

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

Wei Wang

I am having the same problem.

Is there a fix or workaround for this issue?

J. Craig Williams

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

 

Michelle Maier

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"

Kelley B

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?

AaronO

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

AaronO

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