File Names and Location

Discuss the spreadsheet application

File Names and Location

Postby quarkrad » Tue Feb 11, 2020 7:03 pm

I am trying to get a list of files names and their locations in a spreadsheet. I have seen how this can be done in excel (https://www.youtube.com/watch?v=OSCPVBWOqwc) using Power Query. Is there such a thing in Calc? Attached shows the types of things I'm looking for. (There are a number of sub folders involved). Thanks.
Attachments
example.ods
(13.94 KiB) Downloaded 23 times
libreoffice 5.4.1.2 on ubuntu 16.04
quarkrad
 
Posts: 67
Joined: Mon Nov 03, 2008 10:01 am

Re: File Names and Location

Postby RusselB » Tue Feb 11, 2020 8:27 pm

While I can't think of a way of using Calc as they did in the Excel video (the link helped a lot, thank you), I can generate something similar using the Windows Command Prompt.
You may (probably do) have something similar in Ubuntu.
In Windows, I would call up the Windows menu system, then in the Search box enter "cmd" (without the quotes).
Then in the command prompt dialogue, I would enter something like dir c:\users\Russel /a-d /s /b > Russel.txt
This will make a file, Russel.txt in the directory that the command is being issued from , that contains a list of all of the files in the directory C:\users\Russel and any files that are in sub-directories of that location.
Once that file is created, you can close the command prompt window, then import the file Russel.txt, which is just plain text.
If you want to do multiple directories and have the information go into the same file, issue the same command in the command prompt dialogue, changing the c:\users\Russel to the appropriate drive and directory, but also change the > to >>
If you don't make that last change, the information from the second issuance of the dir command will overwrite the information in Russel.txt, rather than appending it.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 6117
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: File Names and Location

Postby RoryOF » Tue Feb 11, 2020 8:36 pm

In linux one could redirect the file listing to a text file, then import the text file into Calc, much as RusselB has outlined for Windows.
Apache OpenOffice 4.1.7 on Xubuntu 20.04.1 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 31446
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: File Names and Location

Postby Villeroy » Tue Feb 11, 2020 10:42 pm

Code: Select all   Expand viewCollapse view
Function FILES(sPattern$)
REM Array function to return file names
a() = Array()
s = Dir(sPattern)
do while len(s)>0
   i = ubound(a) +1
   if i = 0 then
      redim a(i)
   else
      redim preserve a(i)
   endif
   a(i) = s
   s = dir()
loop
FILES = a()
End Function

Usage:
=FILES("/tmp/test/*") entered as an array formula returns all file names in folder /tmp/test as a horizontal array
=TRANSPOSE(FILES("/tmp/test/*")) returns the same list as vertical array
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28559
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: File Names and Location

Postby jrkrideau » Wed Feb 12, 2020 4:06 am

Open a terminal, go to the directory of interest and use:
find -printf "%P\n" > file

https://askubuntu.com/questions/549800/ ... -text-file

I have a directory called Food so I did
find -printf "%P\n" > eat.txt
and Ubuntu created a file eat.txt in the Food directory. I should have done eat.csv :( but I got the list.
LibreOffice 6.4.3.2; Ubuntu 20.04
jrkrideau
Volunteer
 
Posts: 3788
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada


Return to Calc

Who is online

Users browsing this forum: No registered users and 19 guests