Converting Date text to Date

Discuss the spreadsheet application

Converting Date text to Date

Postby tadin » Sun Mar 17, 2013 11:54 am

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.
Open Office 3.3 Win7 Home Premium
tadin
 
Posts: 7
Joined: Sat Feb 16, 2013 3:34 pm

Re: Converting Date text to Date

Postby Villeroy » Sun Mar 17, 2013 12:56 pm

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 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17282
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Converting Date text to Date

Postby tadin » Mon Mar 18, 2013 10:59 am

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!).
Open Office 3.3 Win7 Home Premium
tadin
 
Posts: 7
Joined: Sat Feb 16, 2013 3:34 pm

Re: Converting Date text to Date

Postby Villeroy » Mon Mar 18, 2013 1:25 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17282
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Converting Date text to Date

Postby acknak » Mon Mar 18, 2013 3:58 pm

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.
AOO 4 • Linux • Fedora 17
User avatar
acknak
Moderator
 
Posts: 17399
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3


Return to Calc

Who is online

Users browsing this forum: No registered users and 10 guests