Date not formatting correctly on import !

General discussion on OpenOffice Calc and its spreadsheets.

Date not formatting correctly on import !

Postby Elwood M. Walker » Sat Sep 20, 2014 5:17 am

Hello everyone,

I have some php code creating a CSV file. When I open the file in LibreOffice, the date has not been formatted correctly. I have tried to right and left align the date field, and changing the format to display correctly, but still problems. Here is a sample of the data ..

2014-09-04,"Descr 1",63.13
2014-09-10,"Descr2",109.12
etc,etc

and I want it formatted to 4 Sep 14, 10 Sep 14 etc,etc. Can't get it to format correctly. If I enter the date in manually and then format as date, it appears okay. Although I have noticed it does display as 9 Apr 2014 ??

Thanks in advance,
Elwood M. Walker
 
Posts: 33
Joined: Mon Mar 24, 2014 6:09 am

Re: Date not formatting correctly on import !

Postby Keya_LOT » Sat Sep 20, 2014 10:04 am

Hi,

When you import from a CSV file, you get a Text Import dialog.
Ensure that Detect Special Numbers is checked.
Under the Fields section, set the column type for one of the date formats for the column that the dates are being imported to.
The specific format that you are asking for isn't listed as an option on my system.
You may, and I suspect will, need to reformat the column after the dates have been imported to get the format you're wanting.
Keya_LOT
 
Posts: 24
Joined: Tue Mar 25, 2014 6:35 am

Re: Date not formatting correctly on import !

Postby Elwood M. Walker » Sat Sep 20, 2014 11:29 am

Thanks for your help. I can check special numbers, however the dropdown for the Fields section is disabled, it won't display the field names. :(

That said, after import, I noticed that now the date field is right aligned (it was left aligned before). So I can simply highlight the date column and change it to dd Mmm yy format, which is what I wanted.

Also, now dates tha were previously going from "2014-09-04" to the "9th Apr 14" are going to ..

4 Sep 14, and the other dates all look correct. So, despite the problem that I can't view the Field names, by making sure that the "Detect Special Numbers is checked", has fixed the problem.

Thanks.
Elwood M. Walker
 
Posts: 33
Joined: Mon Mar 24, 2014 6:09 am

Re: Date not formatting correctly on import !

Postby Keya_LOT » Sat Sep 20, 2014 12:14 pm

In order to change the column type in the Fields section, you have to select the column that you want to change before going to the dropdown box in order to select/change the column type.
Keya_LOT
 
Posts: 24
Joined: Tue Mar 25, 2014 6:35 am


Return to OpenOffice.org Calc

Who is online

Users browsing this forum: No registered users and 0 guests

cron