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

Find more posts tagged with

Importing and Exporting

Comments

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!

Sign in to join the conversation!

Quick Links

Trending discussions

Document filtered by form responses
I've created a Smartsheets form. When someone submits the form, I want them to automatically be emailed a document that has resources and links, but the document is filtered to only show the content that corresponds with what they selected in the form. Is something like this possible? What types of documents or automations…
Form stopped working after upgrade
I have a sheet that I created in a previous version, and it had a form. I was recently upgraded to a newer version and now my form is not working. I created a new form for the sheet, and it is giving me an error also. Does anyone know what I can do to fix this? The error says Form Unavailable. Sorry, the form you're trying…
Getting Error About Emulated Environments
Hi, I have read the post from 8/21/25. I am still getting the same error. I am setting up a new device and just download the latest version as of 9/5/25. I am using a Eunivon Smart White Board for touchscreen capability. It is running Android 14. Any help would be appreciated.