Welcome to NeoOffice developer notes and announcements
NeoOffice
Developer notes and announcements
 
 

This website is an archive and is no longer active
NeoOffice announcements have moved to the NeoOffice News website


Support
· Forums
· NeoOffice Support
· NeoWiki


Announcements
· Twitter @NeoOffice


Downloads
· Download NeoOffice


  
NeoOffice :: View topic - calc - importing dates from an ls of directory
calc - importing dates from an ls of directory
 
   NeoOffice Forum Index -> NeoOffice Releases
View previous topic :: View next topic  
Author Message
BabaG
Agent


Joined: Feb 06, 2009
Posts: 13

PostPosted: Fri Feb 06, 2009 5:35 pm    Post subject: calc - importing dates from an ls of directory

i'm trying to make a spreadsheet using a listing of many thousands of files in a particular directory. they are movie files. i want the spreadsheet to have columns for the various bits of file info like filename, size, date modified.

i used:

ls -la /path/to/directory > dirlist.txt

to create a text file which i'm trying to import into neooffice via csv settings. it generally works, however, the date columns have given me a problem.

ls output the date as:

Aug 15 2003

for example. the text file is not really tab delimited so i'm using <space> as the delimiter. that works for everything but the date, which now breaks into three columns.

how can i get the date consolidated into a single column, either on import, or after.

here's an example of a couple of lines of text output from ls:

-rwxr-xr-x 29422316 Jul 15 2001 001____'20010715_07.00.17.avi
-rwxr-xr-x 23937830 Jul 15 2001 001____'20010715_07.00.41.avi
-rwxr-xr-x 31042738 Jul 15 2001 001____'20010715_07.00.54.avi
-rwxr-xr-x 38023038 Jul 15 2001 001____'20010715_07.02.29.avi
-rwxr-xr-x 40889628 Jul 15 2001 001____'20010715_07.03.49.avi


thanks,
BabaG
Back to top
pluby
The Architect
The Architect


Joined: Jun 16, 2003
Posts: 11949

PostPosted: Fri Feb 06, 2009 5:42 pm    Post subject:

You can do it afterward by inserting a new column and putting the following formula into the new column's cells:

=CONCATENATE(A1;" ";B1;" ";C1)

In the above example, cell A1 contains the month, cell B1 contains the day, and cell C1 contains the year.

Does that work for you?

Patrick
Back to top
BabaG
Agent


Joined: Feb 06, 2009
Posts: 13

PostPosted: Fri Feb 06, 2009 7:27 pm    Post subject:

yes! that does work. thanks for that, pluby!

two issues i can see. any ideas on these?

first, i bet i can't delete the original A1, B1, C1 columns now that i have what i want displaying via your formula. i suppose i could hide the A-C columns, but is there a way i can create the date as a standalone value, independent of the formula once it's been created?

secondly. some dates are in another format. this is really another question but i'll go ahead with it here since we're already into it.

some dates are diplayed thusly:

Feb 1 16:09

in other words, some are displayed as month/day/time. this occurs for very recent listings. calc, using the suggested formula is producing values like this when confronted with this format:

Feb 1 0.672916666666667

how do i get calc to revert to the original time value?

thanks,
BabaG
Back to top
narf
The Anomaly


Joined: Jan 21, 2007
Posts: 1075

PostPosted: Fri Feb 06, 2009 8:56 pm    Post subject:

BabaG wrote:

first, i bet i can't delete the original A1, B1, C1 columns now that i have what i want displaying via your formula. i suppose i could hide the A-C columns, but is there a way i can create the date as a standalone value, independent of the formula once it's been created?


I have an idea for your first question. You will need a new column. Select the column with the formula in it and select the menu Edit :: Copy (or another copy method if you prefer.) Place your cursor at the top of the new column and select the menu Edit :: Paste Special. In the Paste Special dialog that opens make sure the Strings box is checked in the Selection section on the left hand side. Uncheck all the other check boxes. Press the OK button to paste a text string of the date. You can now delete the other 4 columns (A, B, C and the one containing the formula.)

I do not have an answer for the second question. There are two different types of data and I do not know how to handle the time. Maybe someone else here on the forums may have an idea.

Does Paste Special solve your first problem?

--fran
Back to top
narf
The Anomaly


Joined: Jan 21, 2007
Posts: 1075

PostPosted: Fri Feb 06, 2009 9:12 pm    Post subject:

After a quick search I have an idea for your second question.

BabaG wrote:

secondly. some dates are in another format. this is really another question but i'll go ahead with it here since we're already into it.

some dates are diplayed thusly:

Feb 1 16:09

in other words, some are displayed as month/day/time. this occurs for very recent listings. calc, using the suggested formula is producing values like this when confronted with this format:

Feb 1 0.672916666666667

how do i get calc to revert to the original time value?


Try this concatenation formula:

= CONCATENATE(A1;" ";B1;" ";TEXT(C1;"H:MM AM/PM"))

This will only work with time, not year so you will need to use the appropriate formula for the data.

Does this formula work for you?

--fran
Back to top
sardisson
Town Crier
Town Crier


Joined: Feb 01, 2004
Posts: 4588

PostPosted: Fri Feb 06, 2009 10:07 pm    Post subject:

You could probably use ls -lT to force all the files to output in the same date format (though you'd have to change your concatenate format to match the new output format).

Otherwise, there's probably a way to toss an IF statement in for the third term (though I can't get it working right now) to return just the year if it's a year or Fran's TEXT(C1;"H:MM AM/PM") otherwise.

Smokey

_________________
"[...] whether the duck drinks hot chocolate or coffee is irrelevant." -- ovvldc and sardisson in the NeoWiki
Back to top
BabaG
Agent


Joined: Feb 06, 2009
Posts: 13

PostPosted: Fri Feb 06, 2009 11:37 pm    Post subject:

thanks, will look into these when i get back in front of the mac tomorrow.

BabaG
Back to top
yoxi
Cipher


Joined: Sep 07, 2004
Posts: 1799
Location: Dawlish, Devon

PostPosted: Sat Feb 07, 2009 6:05 am    Post subject:

For your first problem, on a one-row basis you can achieve the same thing by selecting the 3 cells, doing Merge Cells on them opting to put all the contents in the first cell - and then Split Cells again will leave you with dd/mm/yyyy in the first cell.

I know this doesn't help you unles you write it into a macro that runs down the column, but it's a neat little trick for one-offs so I thought I'd post it here.

For what it's worth, there are two Merge Cells items in the Format::Merge Cells submenu with exactly the same name - they differ in how the resulting cell contents are aligned by default. The first one aligns centred/centred - the second aligns default (which for cells merged vertically means left/bottom).
Back to top
Display posts from previous:   
   NeoOffice Forum Index -> NeoOffice Releases All times are GMT - 7 Hours
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You cannot attach files in this forum
You cannot download files in this forum

Powered by phpBB © 2001, 2005 phpBB Group

All logos and trademarks in this site are property of their respective owner. The comments are property of their posters, all the rest © Planamesa Inc.
NeoOffice is a registered trademark of Planamesa Inc. and may not be used without permission.
PHP-Nuke Copyright © 2005 by Francisco Burzi. This is free software, and you may redistribute it under the GPL. PHP-Nuke comes with absolutely no warranty, for details, see the license.