Apache OpenOffice Community Forum

Hello,

I get some Excel files saved as csv files with date and time columns .
Naturally, in csv the dates are interpreted as text, thus when saved as csv, the Excel adds a " ' " before the text ('01-01-2013), which is OK.
The problem is that when I set the column in OO Calc to Date, it does not remove the text designator, and I can not even remove it by find-replace. (can erase one by one, but I have thousands...)

Any idea on how I can make a date text into relevant Calc date format?
How to change date format into date number?
How to remove the text designator from any cell?

Thanks.

Import the date column as date. Mark the column and choose a date format MDY or DMY.

Thanks Villeroy, but there is no 'import' operation.
There is a csv (comma-separated-values) file that by definition has no formatting, and thus when saved treats all as text.
The file made by Excel precedes each cell with the " ' " (Hex 27) as text designator (I believe it is like"@" in Calc).
But the question is more general: How can one convert text columns into values?
And, why am i unable to remove the ' by replacing it? (The Calc does not find it!).

Of course you import text data when you load csv into a spreadsheet program. This is why you get the text import dialog where you can specify the assumed locale, column separators, encoding and more details for specific columns.

If you do not see any text import dialog, then the whole file is not a text file and therefore it should not carry the .csv ending in the file name. Of course you can tell Excel to treat dates correctly which would eliminate the problem at the data source.

Calc can convert text to numbers (and vice versa) with a simple replacement.
1) Select the column(s) in question.
2) Edit>Find>Replace...
[More Options...]
[X] Current selection only
[X] Regular expressions
Search: .+
Replace: &
[Replace All]

This replaces any content with the same effectively re-typing all data.
The very procedure converts data into text if you apply number format "@" (text) before the replacement.

If you follow Villeroy's suggestions and still aren't getting what you want, please attach a sample data file here so we can see exactly what you're dealing with.

The forum will require you to put the csv file in a zip archive, then you can attach it, as long as the zip is less than 128k.

Villeroy wrote: Calc can convert text to numbers (and vice versa) with a simple replacement.
1) Select the column(s) in question.
2) Edit>Find>Replace...
[More Options...]
[X] Current selection only
[X] Regular expressions
Search: .+
Replace: &
[Replace All]

This replaces any content with the same effectively re-typing all data.
The very procedure converts data into text if you apply number format "@" (text) before the replacement.

This worked for me (although, as primarily an Excel user, I've no idea how the conversion works) - just blindly followed the instructions and it sorted me out perfectly, thanks!

Brian_M wrote:... I've no idea how the conversion works ...

Nothing magic. It's just a matter of tricking Calc into re-scanning the text values and doing the normal date conversion.

Calc normally converts any entry that looks like a date into a date value. This conversion can get bypassed, leaving as plain text an entry that Calc would normally recognize as a date. If we can arrange for Calc to re-scan the text value, as if it were entered by the user, Calc will then make the conversion. One way to force this "re-entry" is by find/replace. The extra stuff there is just a search that matches anything and replaces it with what was already there. This forces Calc to re-scan the text and do the date conversion.

Brian_M wrote:This worked for me (although, as primarily an Excel user, I've no idea how the conversion works) - just blindly followed the instructions and it sorted me out perfectly, thanks!

In Excel or any other spreadsheet program enter a valid date with a preceeding apostrophe into some blank unformatted cell. This is one way how to enter numeric expressions as literal text without any evaluation taking place. The text will be aligned to the left cell border.
Now re-enter the same text into the same but this time without the leading apostrophe. Your input will be recognized as a numeric value and the number will be formatted in some default date format (usually with 2 digit day, 2 digit month and 2 digit year). Like any other number, the value will be aligned to the right border. When you hit Ctrl+F8 in Calc (or menu:View>Highlight Values), the value will appear in blue font which indicates that this value is a constant number.
The replacement does nothing but re-entering whatever cell text.
This method may fail:
Text "12/01/2015" converted to number may give 1st of December in US context or 12th of January for the rest of the planet.
Text "13/01/2015" may remain a text value in US context because there is no 13th month or yield 13th of January.
Before you start the replacement you should ensure that the number format language provides the right context for the conversion.

Perhaps I've missed something in this post but should this not be solved with the operation "Data>text to columns"?

Martin.Flynn wrote:Perhaps I've missed something in this post but should this not be solved with the operation "Data>text to columns"?

Text-to-columns works just as well. If the locale needs to be adjusted you have to change the global locale, do the conversion and then change back to your preferred locale.

tadin wrote:Thanks Villeroy, but there is no 'import' operation.
There is a csv (comma-separated-values) file that by definition has no formatting, and thus when saved treats all as text.

Opening a CSV is an import of sorts, and will convert numbers to numerical cell values where possible.

tadin wrote:The file made by Excel precedes each cell with the " ' " (Hex 27) as text designator (I believe it is like"@" in Calc).

That apostrophe is the spreadsheet software's convention for indicating that content which looks like a number is in fact text. Same for Excel and Calc.

tadin wrote:But the question is more general: How can one convert text columns into values?

Already mentioned: Select the dates column and menu item data - text to columns. Select the columnn in the conversion dialog, an then pick the column type corresponding to the date format in use.
If you want to keep the original text column, the VALUE() function is another approach, not yet mentioned as far as I can tell. Note that with VALUE(), Calc will evaluate the string based on OpenOffice locale settings (disregarding language settings for cells), and display according to selected date format. IOW, in many cases not as useful/reliable as text to columns.

tadin wrote:And, why am i unable to remove the ' by replacing it? (The Calc does not find it!).

The apostrophe is not part of the cell content as such, but rather a "type modifier" for the content.