Converting Date text to Date
Converting Date text to Date
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.
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.
Open Office 3.3 Win7 Home Premium
Re: Converting Date text to Date
Import the date column as date. Mark the column and choose a date format MDY or DMY.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Converting Date text to Date
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!).
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!).
Open Office 3.3 Win7 Home Premium
Re: Converting Date text to Date
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 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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Converting Date text to Date
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.
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.
AOO4/LO5 • Linux • Fedora 23
Re: Converting Date text to Date
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!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.
ApacheOffice 4.2 Win 7 64
Re: Converting Date text to Date
Nothing magic. It's just a matter of tricking Calc into re-scanning the text values and doing the normal date conversion.Brian_M wrote:... I've no idea how the conversion works ...
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.
AOO4/LO5 • Linux • Fedora 23
Re: Converting Date text to Date
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.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!
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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Volunteer
- Posts: 96
- Joined: Wed Nov 28, 2007 7:52 pm
- Location: Moray
Re: Converting Date text to Date
Perhaps I've missed something in this post but should this not be solved with the operation "Data>text to columns"?
Rip Van Apache Open Office 4.1.3 / Libre office 5.3.5.2 on Ms Windows 10
Re: Converting Date text to Date
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.Martin.Flynn wrote:Perhaps I've missed something in this post but should this not be solved with the operation "Data>text to columns"?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Converting Date text to Date
Opening a CSV is an import of sorts, and will convert numbers to numerical cell values where possible.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.
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:The file made by Excel precedes each cell with the " ' " (Hex 27) as text designator (I believe it is like"@" in Calc).
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.tadin wrote:But the question is more general: How can one convert text columns into values?
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.
The apostrophe is not part of the cell content as such, but rather a "type modifier" for the content.tadin wrote:And, why am i unable to remove the ' by replacing it? (The Calc does not find it!).
Apache OO 4.1.12 and LibreOffice 7.5, mostly on Ms Windows 10