Solved

Script help:  List files in sub folders (1 level)

Posted on 2013-11-05
21
235 Views
Last Modified: 2013-12-12
Hi All,

I have a folder called P:\Training, which contains around 20 employee folders.

e.g.

P:\Training\John Smith
P:\Training\Paul Parker

inside each of the employee folders are training records.  PDF's, .doc etc...

They all have the following naming convention;

Certificate Name.DDMMYYYY.nn.pdf

where Certificate Name is the name of the qualification.   DDMMYYYY is date it was achieved nn is the number of months until the certificate expires

I need help writing a script that will run every night reading the folder content and creating a CSV summarising the content.

ideally,

Employee Name, Certificate Name, Date, Expiry


I've included this in Batch Script, VBScript and PHP as the server can run any of these.


Many thanks in advance
D
0
Comment
Question by:detox1978
21 Comments
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 39625562
If the folder isn't part of the webserver's file system, then you won't be able to do this with PHP. PHP runs on a webserver and only has access to it's own files and folders. If it is under the webserver structure, then I can help you using PHP
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 39625585
You might want to rethink that date format.  If you use the ISO-8601 standard you pick up a lot of advantages, including sortability.  This article explains why we use YYYYMMDD instead of the other way around.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html

Have you started writing any of the code yet?
0
 
LVL 26

Expert Comment

by:pony10us
ID: 39625736
Here is a rough batch file that you can clean up but will do basically what you want.

@echo off
:: setLocal EnableDelayedExpansion
set targetdir=p:\training

FOR /R "%targetdir%" %%G in (.) DO (
  Pushd %%G
 	Echo now in %%G
	FOR %%x IN (.) DO set y=%%~nx
	for /f "tokens=1,2,3 delims=." %%a in ('dir /b .\*.*') do echo %y%, %%a, %%b, %%c  >> %targetdir%\sample.csv
  Popd )

Open in new window


It needs a little work as it currently also reads the p:\training folder into the sample.csv file.
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 2

Author Comment

by:detox1978
ID: 39625973
Thanks for the PHP suggestions, the folder is not part in the website path, so I may have to do it using VBScript or Batch Script.

pony10us, I run the script, but it didnt create sample.csv, any ideas?
0
 
LVL 26

Expert Comment

by:pony10us
ID: 39625983
rem the @echo off (first line) and see if you get any errors.  It should create the file in the p:\training folder.  That's the way I had it working on my system.
0
 
LVL 2

Author Comment

by:detox1978
ID: 39626024
Ok, I created the batch file again and it now works.

A couple of tweaks needed.

All the employee names are the
can it create a single CSV file?


Many thanks
0
 
LVL 26

Expert Comment

by:pony10us
ID: 39627578
Did you not finish the first bullet:  "All the employee names are the"


What did you change?  The ">>" should append the echo command to one single file.  (sample.csv)
0
 
LVL 2

Author Comment

by:detox1978
ID: 39628108
Not sure what happened there.  it should have read;

All the employee names are the last persons name.
0
 
LVL 26

Expert Comment

by:pony10us
ID: 39629074
I got a few minutes to play around with my original script.  Since you made some modifications to that you will have to make the same to this one but try:

@echo off
setLocal EnableDelayedExpansion
set targetdir=p:\training

FOR /R "%targetdir%" %%G in (.) DO (
  Pushd %%G
     Echo now in %%G
for /f "tokens=1,2 delims=/ " %%a in ("%%G") do (
set fname=%%a&set lname=%%b
set lname2=%lname:~0,-2%
          for /f "tokens=1,2,3,4 delims=." %%a in ('dir /b .\*.*') do echo %lname2%, %%a, %%b, %%c, %%d >> %targetdir%\sample.csv
)
  Popd 
)

Open in new window

0
 
LVL 2

Author Comment

by:detox1978
ID: 39631861
Thanks for the update.  It's almost perfect.

The employee names (folder) are all at the top of the CSV and the content of the folders are listed below.

Is it possible to have the folder name on each line?


many thanks again
0
 
LVL 2

Author Comment

by:detox1978
ID: 39681795
Almost finished, any ideas?
0
 
LVL 26

Expert Comment

by:pony10us
ID: 39690166
Can you provide a sample of what your results are so I can compare to what I get?
0
 
LVL 2

Author Comment

by:detox1978
ID: 39690895
Here's a sample output.

, FolderName, , ,  
, FolderName, , ,  
, FolderName, , ,  
, FolderName, , ,  
, FolderName, , ,  
, FolderName, , ,  
, FolderName, , ,  
, FolderName, , ,  
, FileName, 19082011, 60, pdf 
, FileName, 19082011, 60, pdf 
, FileName, 19082011, 60, pdf 
, FileName, 19082011, 60, pdf 
, FileName, 19082011, 60, pdf 
, FileName, 19082011, 60, pdf 
, FileName, 19082011, 60, pdf 
, FileName, 19082011, 60, pdf 
, FileName, 19082011, 60, pdf 
, FileName, 19082011, 60, pdf 
, FileName, 19082011, 60, pdf 
, FileName, 19082011, 60, pdf 
, FileName, 19082011, 60, pdf 
, FileName, 19082011, 60, pdf 
, FileName, 19082011, 60, pdf 
, FileName, 19082011, 60, pdf 
, FileName, 19082011, 60, pdf 
, FileName, 19082011, 60, pdf 
, FileName, 19082011, 60, pdf 
, FileName, 19082011, 60, pdf 
, FileName, 19082011, 60, pdf 
, FileName, 19082011, 60, pdf 
, FileName, 19082011, 60, pdf 
, FileName, 19082011, 60, pdf 
, FileName, 19082011, 60, pdf 
, FileName, 19082011, 60, pdf 

Open in new window



As you can see it collects all the information, however the folder names are all at the top on their own row and file details are later on their own row
0
 
LVL 26

Expert Comment

by:pony10us
ID: 39691132
I am having trouble with the first few lines that I can't seem to do away with but try this and see if it is closer:

@echo off
setLocal EnableExtensions EnableDelayedExpansion
set targetdir=p:\training

FOR /R "%targetdir%" %%G in (.) DO (
  Pushd %%G
     Echo now in %%G
for /f "tokens=1,2,3,4 delims=\" %%w in ("%%G:~0,-2%") do (
          for /f "tokens=1,2 delims= " %%l in ("%%y") do (
          	for /f "tokens=1,2,3,4 delims=." %%a in ('dir /b .\*.*') do echo %%m, %%a, %%b, %%c, %%d >> %targetdir%\sample.csv
	)
        )
Popd 
)

Open in new window

0
 
LVL 2

Author Comment

by:detox1978
ID: 39712140
I get the same results....
0
 
LVL 26

Expert Comment

by:pony10us
ID: 39712238
Step 1:

C:\test>eeproject2.bat
now in c:\training\.
now in c:\training\data sample\.
now in c:\training\first last\.
now in c:\training\my files\.


Step 2:

C:\test>cd \training


Step 3:

C:\training>dir
 Volume in drive C has no label.
 Volume Serial Number is 5E33-3CE5

 Directory of C:\training

C:\training>dir
 Volume in drive C has no label.
 Volume Serial Number is 5E33-3CE5

 Directory of C:\training

12/11/2013  11:26 AM    <DIR>          .
12/11/2013  11:26 AM    <DIR>          ..
11/05/2013  04:05 PM    <DIR>          data sample
12/11/2013  11:20 AM    <DIR>          first last
12/11/2013  11:21 AM    <DIR>          my files
12/11/2013  11:26 AM               269 sample.csv
               1 File(s)            269 bytes
               5 Dir(s)  37,405,016,064 bytes free

C:\training>

Step 4: open sample.csv

The first 3 lines are the full names of folders it checked.
sample.csv
0
 
LVL 2

Author Comment

by:detox1978
ID: 39712594
The results should be;

data sample,test,1,2,3
data sample,test,1,2,4
data sample,test,1,2,5
first last,test,2,3,4
first last,test,2,3,5
first last,test,2,3,6
my files,test,3,1,2
my files,test,3,1,3
my files,test,3,1,4



Mine are
data sample
first last
my files
,test,1,2,3
,test,1,2,4
,test,1,2,5
,test,2,3,4
,test,2,3,5
,test,2,3,6
,test,3,1,2
,test,3,1,3
,test,3,1,4
0
 
LVL 26

Expert Comment

by:pony10us
ID: 39714221
Try it this way:
@echo off
setLocal EnableExtensions EnableDelayedExpansion
set targetdir=p:\training

FOR /D /r "%targetdir%" %%G in (.) DO (
  Pushd %%G
     Echo now in %%G
for /f "tokens=1,2,3,4 delims=\" %%w in ("%%G:~0,-2%") do (
          	for /f "tokens=1,2,3,4 delims=." %%a in ('dir /b .\*.*') do echo %%y, %%a, %%b, %%c, %%d >> %targetdir%\sample.csv
	)
Popd 
)

Open in new window


** edit - changed the target to be on P instead of C ***
0
 
LVL 2

Author Comment

by:detox1978
ID: 39714346
I think I know where the issue is.  My path is c:\data\training

very sorry for the error.
0
 
LVL 26

Accepted Solution

by:
pony10us earned 500 total points
ID: 39714359
Okay, I was writing it for P:\training since that is what the original post said.  

Try this:

@echo off
setLocal EnableExtensions EnableDelayedExpansion
set targetdir=c:\data\training

FOR /D /r "%targetdir%" %%G in (.) DO (
  Pushd %%G
     Echo now in %%G
for /f "tokens=1,2,3,4,5 delims=\" %%w in ("%%G:~0,-2%") do (
          	for /f "tokens=1,2,3,4 delims=." %%a in ('dir /b .\*.*') do echo %%z, %%a, %%b, %%c, %%d >> %targetdir%\sample.csv
	)
Popd 
)

Open in new window

0
 
LVL 2

Author Closing Comment

by:detox1978
ID: 39714437
many thanks
0

Featured Post

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have published numerous articles here at Experts Exchange that present programs/scripts written in a language called AutoHotkey. Each of those articles has a brief paragraph describing where to download the product and how to install it. I have al…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn how to dynamically set the form action using jQuery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question