How to force Text format when importing CSV

jimav · February 11, 2021, 7:23pm

When importing from a .csv file, I set “Column type” to “Text” for a column, yet the resulting cells have General Number format, not Text, unless the data in the particular cell looked like a number. I do not have “Detect special numbers” checked.

Isn’t this a bug?

I want all cells in a particular column have “Text” format regardless of their content, so that if the user later changes a cell to something like 01234 it will not be corrupted by being treated as a number and loosing the leading zero.

Here is an example. Create a .csv file:

(echo "foo,bar"; echo "0011,88/12/1") >demo.csv

Then run localc demo.csv.

In the import dialog, un-check all options except “Separated by Comma”.

Select each column in the “Fields” and set the drop-down to Text.

Click OK.

In the resulting spreadsheet, rightclick->Format Cells… (Numbers tab) to see the format assigned. Only cell A2 has “Text” format, the other three have “General Number” format.

Is there a way to force them all to be “Text” in the import dialog?

(I can’t manually fix the cell formats after importing because in the real application I’m doing batch imports using unoconv with the appropriate FilterOptions)

Zizi64 · February 12, 2021, 6:29am

Please upload your real sample files here. please upload one .csv, and one .ods file.

mikekaganski · February 12, 2021, 7:27am

No this is not a bug. What you select in CSV import dialog is not about cell formatting, it’s about treatment of content. When you set some column to be text, it only means that LibreOffice will not try to convert the field textual value into a number before writing it to a cell; instead, it will put the original textual value into the cell’s text.

If you want to format your data, you need to use some post-processing, e.g. macros or controlling LibreOffice from external scripts using UNO.

mikekaganski · February 12, 2021, 7:31am

Note that in some cases, LO does set the cell formatting. This is when there’s no data type for the data: e.g., there’s no “Date” or “Time” data type, they are just plain numbers, so LO has to also set cell format to display detected dates as dates. This is different in case of text: there’s a distinct data type for it, and so no need to set format.

jimav · February 13, 2021, 6:24pm

@mikekaganski - How is behavior different when “Detect special numbers” is on?

Also, what is the intended purpose (with Detect Special Numbers off) of only setting Cell Format when the current value would be corrupted otherwise, and not for already-acceptable values? What use case is facilitated by leaving cells with different CFs in the same column?

mikekaganski · February 14, 2021, 6:30am

I didn’t understand your question. “Detect special numbers” is described in help. Not setting cell formats (note that CF usually stands for conditional format, and is confusing when used for other purposes) has purpose to not set format as much as possible for data type (CSV) that has no formatting information.