Notifications
Q&A

Controlling format when importing CSV

+1
−3

In an as yet unanswered Stack Overflow question about Prevent(ing) LibreOffice Calc from changing “E1” to “0” when reading CSV from early this year a user of Stack Overflow had issues with importing CSV format into LibreOffice Calc 6.3.4.2. Examples were that E1 was showing as 0 and E2 as 00. Thus, specifically, 144E1 was appearing as 1440.

In that user's words:

Why is this and how can I prevent it?

The user showed the following import settings:

Import settings

Why should this post be closed?

1 comment

Isn't this question about how to use a piece of software, rather than about software engineering? Downvoted for out of scope, but will reverse if determined to be in scope. Adding as an example question on https://software.codidact.com/questions/277235 ‭Sigma‭ 12 days ago

1 answer

+4
−0

This is happening because of assumptions made by the software. "E notation" is a standard convention popular with scientists, mathematicians and engineers as a convenient way to represent and process very large or very small numbers. Such scientific notation is so popular that without context the chances are high that, say, 144E1 is intended to represent a number, rather than being, as in your requirement, just a text string. Left to its own devices, the software automatically converts such strings to a format that makes processing them as numbers relatively easy, and then displays them in standard Number format. After all, Calc is designed for processing numbers whereas it is Writer that is tailored for processing text (and does not automatically assume 144E1 represents a number). Calc is trying to be helpful.

Prevention is very simple, though perhaps less intuitive than it might be. The user needs to specify the format in order to override the automatic reformatting. This can be done one field (column) or more at a time but the options only become evident once a field has been selected. The import screen displayed in the question shows the dropdown in Column Type: is empty. To access the options one field (or more) needs to be selected. This is done in the sample displayed at the bottom of the import settings screen (not shown in the image in the Q here). Click on Standard above the relevant column and the dropdown should be populated, allowing selection from various formats. To override the software's automatic default, just choose Text and proceed as before.

This can be a very useful feature when importing US dates to UK format, or vice versa.

0 comments

Sign up to answer this question »