Solved

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

Posted on 2013-11-05
21
231 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 42

Expert Comment

by:Chris Stanyon
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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
 
LVL 2

Author Comment

by:detox1978
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 2

Author Comment

by:detox1978
Comment Utility
Almost finished, any ideas?
0
 
LVL 26

Expert Comment

by:pony10us
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I get the same results....
0
 
LVL 26

Expert Comment

by:pony10us
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
many thanks
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

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…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

743 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now