Become a Site Supporter and Never see Ads again!

Author Topic: Setting Up Excel Spread Sheet From MS File Exporer  (Read 3062 times)

0 Members and 1 Guest are viewing this topic.

Offline Scooter123

  • "I am not an alcoholic. I am a drunk. Drunks don't go to meetings."
  • Trade Count: (9)
  • Needs to get out more...
  • *****
  • Posts: 3774
Setting Up Excel Spread Sheet From MS File Exporer
« on: April 23, 2020, 09:15:10 PM »
I've been taping since early 1971.  Yeah, I've got a bunch of shows, not to mention old fashioned trades and downloads.  The root file is Live Concerts and is organized first by artist, then by the show.  Collections are under Various. 

What I want to do is run a script and export those down two levels, the artist and show.  I do not need to export the individual songs, filesets, etc.  Just two pieces of information.

I can run Powershell and run basic script get-childitem within that directory, and behold it gives me the artist folders.  What I want to do is add to that script and include the subfolders one level down.  I've tried running get-childitem -recurse, but it gives me every subfolder and every file--too much information.  It also doesn't display the whole log, so I need to export that information to a text file.

The other way is to write a formula for Excel, but I am not too adept at Excel. 

Any suggestions out there?
Regards,
Scooter123

mk41 > N Box  > Sony M-10
mk4 > N Box > Sony M-10

Offline if_then_else

  • Trade Count: (0)
  • Taperssection Member
  • ***
  • Posts: 428

Offline Scooter123

  • "I am not an alcoholic. I am a drunk. Drunks don't go to meetings."
  • Trade Count: (9)
  • Needs to get out more...
  • *****
  • Posts: 3774
Re: Setting Up Excel Spread Sheet From MS File Exporer
« Reply #2 on: April 24, 2020, 12:53:40 AM »
Not bad, but ...

Powershell has a text or page limitation, so my entire collection, while processed, was not displayed.  Here is the script I used while in the folder

get-childitem -recurse -directory -depth 1

However if I made a transcript before running the command

start-transcript

The results were sent to my computer in the form of a text file, which captured everything A to Z, so far so good.  It is a little goofy looking but the results can be read and understood.

Next question, how can this command output to .csv file so the data will populate an Excel spread sheet? 

Thanks for your help.  It really did help me get over the edge on Powershell. 
Regards,
Scooter123

mk41 > N Box  > Sony M-10
mk4 > N Box > Sony M-10

Offline if_then_else

  • Trade Count: (0)
  • Taperssection Member
  • ***
  • Posts: 428
Re: Setting Up Excel Spread Sheet From MS File Exporer
« Reply #3 on: April 24, 2020, 01:02:51 AM »
Form my point of view, the easiest to get it into a format readable by Excel would be to format the output on STDOUT to CSV (character or comma separated values) and to redirect the output to a new file (via '>').
Then open the file in Excel, modify it as required and save it as an *.xlsx file.

Offline lerond

  • Trade Count: (5)
  • Taperssection Regular
  • **
  • Posts: 62
  • Gender: Male
Re: Setting Up Excel Spread Sheet From MS File Exporer
« Reply #4 on: April 24, 2020, 03:14:07 AM »
Another notion, check out "cathy" a freeware cataloging program posted at <http://rva.mtg.sk//>
ain't no party like a katydid party
jumpin' and a hoppin' and singin' them songs!

Offline kuba e

  • Trade Count: (1)
  • Taperssection Member
  • ***
  • Posts: 492
  • Gender: Male
Re: Setting Up Excel Spread Sheet From MS File Exporer
« Reply #5 on: April 24, 2020, 07:43:50 AM »
Unfortunately I don't know Powershell. But you can also try windows command. If you have a directory structure "c:\music\artist\show", this command will print all directories in the format "artist ; show" to the file music.txt.
for /f "tokens=*" %G in ('dir /b /a:d "c:\music\*"') do for /f "tokens=*" %H in ('dir /b /a:d "c:\music\%G\*"') do echo "%G ; %H" >>music.txt

But if the artist directory has no subdirectory, this command will print nothing. You would have to list these directories with another command. Also don't forget to delete music.txt before each run.
« Last Edit: April 24, 2020, 07:45:23 AM by kuba e »

Offline morst

  • I think I found an error on the internet; #UnionStrong
  • Trade Count: (2)
  • Needs to get out more...
  • *****
  • Posts: 5950
Re: Setting Up Excel Spread Sheet From MS File Exporer
« Reply #6 on: April 24, 2020, 05:14:48 PM »
What I want to do is run a script and export those down two levels, the artist and show.  I do not need to export the individual songs, filesets, etc.  Just two pieces of information.
Are all the text files named correctly? Just get the list of those names and you're done, if I understand the question.
https://toad.social/@morst spoutible.com/morst post.news/@acffhmorst

Offline Scooter123

  • "I am not an alcoholic. I am a drunk. Drunks don't go to meetings."
  • Trade Count: (9)
  • Needs to get out more...
  • *****
  • Posts: 3774
Re: Setting Up Excel Spread Sheet From MS File Exporer
« Reply #7 on: April 25, 2020, 12:07:33 AM »
Yeah, I just want the names of the folders, two deep.  So my "S" Drive looks like this as one example reprinted from Powershell printout

S:\My Music\Live Concerts\Kinks
Mode                LastWriteTime         Length Name
----                -------------         ------ ----
d-----        2/22/2018   8:47 AM                Kinks 1962-1972 All Aboard Video Compilation DVD
d-----        2/22/2018   8:47 AM                Kinks 1965 07-07 L'Olympia, Paris France
d-----        2/22/2018   8:48 AM                Kinks 1974 07-14 Hippodrome, London, UK
d-----        2/22/2018   8:48 AM                Kinks 1974-04-04 Providence, RI
d-----        1/30/2020  10:13 PM               Kinks 1977 12-24 Rainbow Theater, London, UK
d-----        2/22/2018   8:48 AM                Kinks 1988 04-14 Fox Theater, St. Louis, Mo

Ideally, I would like to move this to an Excel Spread Sheet which under column A would have only the name of the artist folder

Kinks

Under Column B only the show, which I have named under Dime format


Kinks 1962-1972 All Aboard Video Compilation DVD
Kinks 1965 07-07 L'Olympia, Paris France
Kinks 1974 07-14 Hippodrome, London, UK
Kinks 1974-04-04 Providence, RI
Kinks 1977 12-24 Rainbow Theater, London, UK
Kinks 1988 04-14 Fox Theater, St. Louis, Mo

I suppose in a perfect world, column B would have track names (songs) but honestly, that is not necessary.

I looked at the Cathy media cataloging software and have only one question--has anyone here actually used it?  I hesitate using strange software.  Ideally, I suspect that Excel has a macro or add-on for just this purpose, as it would be perfect for cataloging files in an office environment, but I have not run across the macro yet. 

In a pinch, thanks to if then else, I can play with powershell and output this to an Excel format.  Powershell is pretty easy to learn, just noun-dash-verb, easier than the old DOS command lines.  There are some good YouTubes out there

Thanks for everyone's responses, and if you have any other ideas, I'll check back in.  Cataloging the music is my current pandemic project. 
Regards,
Scooter123

mk41 > N Box  > Sony M-10
mk4 > N Box > Sony M-10

Offline rhinowing

  • Trade Count: (0)
  • Needs to get out more...
  • *****
  • Posts: 4238
  • Gender: Male
    • SPLRA - Smashing Pumpkins Live Recording Association
Re: Setting Up Excel Spread Sheet From MS File Exporer
« Reply #8 on: April 25, 2020, 11:41:34 AM »
kind of off topic, but I've been wanting to learn excel lately. can anyone recommend a decent resource? it's hard to parse through the thousands of websites dedicated to it that a google search for "learn excel" returns
Please contact me if you've ever taped the Smashing Pumpkins or a related group!

Offline if_then_else

  • Trade Count: (0)
  • Taperssection Member
  • ***
  • Posts: 428
Re: Setting Up Excel Spread Sheet From MS File Exporer
« Reply #9 on: April 25, 2020, 01:38:04 PM »
Something along those lines...

Code: [Select]
$Logfile = "output.csv"

$items = $(get-childitem -recurse -directory -depth 1 -Name)

Function LogWrite{
   Param ([string]$logstring)
   Add-content $Logfile -value $logstring
}

Foreach ($i in $items){ 
    $album = ($i -split "\\")[1]
    if ( $i -like "*\*" ){
       
        if ($i -match '[\d]+([\s-]*[\d]*)*'){
            $timestamp = $($matches[0])
            $artist = $album.Substring(0,$album.IndexOf($timestamp))
            $location = $album.Substring($album.IndexOf($timestamp)+$timestamp.Length)
            $timestamp = $timestamp.Trim();
            $artist = $artist.Trim();
            $location = $location.Trim();
            Write-Host "album is $album"
            Write-Host "timestamp is $timestamp"
            Write-Host "artist is $artist"
            Write-Host "location is $location"
        }
        LogWrite "$artist;$timestamp;$location"
        Write-Host "$artist;$timestamp;$location"
    }
}

I prefer Perl or Python for this kind of tasks. Feel free to adapt as needed.
It would have been easier, if there was some kind of a consistent naming convention (re. the timestamps).
« Last Edit: April 25, 2020, 01:39:44 PM by if_then_else »

Offline daspyknows

  • Complaint Dept.
  • Trade Count: (1)
  • Needs to get out more...
  • *****
  • Posts: 9636
  • Gender: Male
  • Don't ask, don't tell, don't get get caught
Re: Setting Up Excel Spread Sheet From MS File Exporer
« Reply #10 on: April 26, 2020, 02:58:33 PM »
kind of off topic, but I've been wanting to learn excel lately. can anyone recommend a decent resource? it's hard to parse through the thousands of websites dedicated to it that a google search for "learn excel" returns

It really depends what you want to do with Excel.  As part of my work I have extensive experience in Visual Basic programming within Excel and can direct you to resources on that but there are so many parts of the program I never have had to use.  Its almost like asking I want to learn how to paint.  Next question is what kind of painting?  If it is programming, reach out.

On topic there was a VB macro I grabbed years ago that would select a directory and build out all the folders below and create as Excel list.  I found it on Google.

 

RSS | Mobile
Page created in 0.104 seconds with 36 queries.
© 2002-2024 Taperssection.com
Powered by SMF