calc - importing dates from an ls of directory

[BabaG]

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

[pluby]

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

[BabaG]

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

[narf]

[narf]

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

[sardisson]

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

[BabaG]

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

BabaG

[yoxi]

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).