43

I've imported a CSV file having the first column to be date-time values in ISO 8601 format like

2012-01-01T00:00:00.000Z

for the first moment of the year 2012.

Then, willing to make LibreOffice to recognize the format (as I was looking forward to plot a diagram), I've selected the column, chosen Format Cells... and entered the custom time format as

YYYY-MM-DDTHH:MM:SS.000Z

And this seems to work if... I edit a cell to remove a hidden single-quote from its beginning (which serves to protect a cell content from being interpreted) as all the newly formatted cells now store values like

'2012-01-01T00:00:00.000Z

(note the single quote - it is only visible when you edit a particular cell).

And I am to do so for all the cells in the column. How can I automate this?

UPDATE: I've already found a solution for the particular case of mine: it helps to set a column format to "time" in the CSV import dialogue. But I am still curious how could this be done in case I wouldn't have the original .csv data file to import but only the .ods file with the data already imported without the format specified at the import time.

wonea
  • 1,877
Ivan
  • 7,863

6 Answers6

64

You can remove the leading single quote (which actually isn't part of the string in the cell) using a regex-based search and replace:

  • Search for all characters between the start and end of the string ^.*$
  • replace with match &
user511
  • 3
tohuwawohu
  • 11,143
41

From the "Data" menu, choose "Text to columns".

pfrenssen
  • 601
0

This problem with the single quote mark still exists in LibreCalc. I tested both solutions and they work.

Text to Columns is probably the easiest to remember:

Data> Text to Columns> Make sure the settings do not actually split the data, then click OK. ...Done.

Search and Replace might be good if you need to get rid of the leading single quote indicator in the entire sheet:

Edit> Find and Replace> Make sure "regular expressions" is selected then search for.* and replace with & ...Done.

You do not need the ^ and $ characters to indicate the start and end of the string. The dot which means "any character" and the asterisk which means "zero or more times" does the trick.

But be careful; Removing the ' text flag opens up your data to auto-reinterpretation. In my experience, (at least with Excel) this leads to data corruption as it is often not correctly predicted which data should be reformatted and how.

os.tech
  • 1
0

To clarify and paraphrase what sxc731 has said:

When you download an XL file from the net and open it in Libre Calc, the numeric columns can have a single quote in front - so they are regarded as text. No formulas or numeric operations will work on the column. But Format menu /Cells still shows it as Number. This is misleading! To convert it to actual numbers:

  • (1) Select the column by clicking its header. This is important. Otherwise the menu item in the next step will be disabled.
  • (2) Click Data menu/ 'Text to columns' option.
  • (3) In the resulting dialog box, Fields/Column type drop down box will be initially empty. (This is probably a bug). To populate it: Click the header 'Standard' in the mini spreadsheet displayed below it. Now the Column type dropdown will be populated.
  • (4) Select 'Standard' as the column type.

Now the numbers in the column will be treated as numbers, and not as text.

Raja
  • 121
0

An attempt for regex replacing of ^.\*$ with & crashed LibreOffice 5. Replacing of .\* with & worked fine.

VMT
  • 1
-2

Actually, you need a to quote the dollar sign first.

Basically libreOffice is prepending a single quote in the field, for some unknown reason. So, you need to replace anything up to the dollar sign with nothing. So, use the regex of ^.*\$ and replace it with nothing. Worked for me.

Andrew B.
  • 1