Solved

Writing DIR output to CSV in consistant format

Posted on 2011-02-21
12
1,315 Views
Last Modified: 2012-05-11
Hey guys,

I have been assigned a very tedious task of comparing the directories of multiple workstations. The output needs to identify all the files in a directory and subdirectories and conduct a comparison of those files based on filename and modified date.

Essentially the plan is to produce a pivot table in Excel to list how many terminals have file A with this modified date, fle B, file C and so on.

The issue is getting the raw data into a format that I can easily manipulate into a pivot table. All I want is a file produced that lists each filepath omitting "\\%computername%\c$\%programfiles%" (Column A) and its modified date (Column B) with no other information output.

Can someone lend me a hand.... all I have so far is:

 
for /f %%a in (C:\Temp\PCList.txt) do (
dir /n /s /tw /od "\\%%a\c$\program files\adobe"
) >>dir.csv

Open in new window


Cheers
G_M
0
Comment
Question by:G_M
  • 5
  • 4
  • 3
12 Comments
 
LVL 52

Expert Comment

by:Bill Prew
ID: 34948072
Give this a try:

for /f %%A in (C:\Temp\PCList.txt) do (
  for /R "\\%%A\c$\program files\adobe" %%B in (*.*) do (
    echo "%%~B",%%~tB
  )
) >>dir.csv

Open in new window

~bp
0
 

Author Comment

by:G_M
ID: 34948264
What output did you get for that? I am not getting anything...
0
 
LVL 52

Expert Comment

by:Bill Prew
ID: 34948475
Okay, sorry, a little trickier than I thought, but this seems to work.

@echo off
for /f %%A in (x:\_xfer\ee\ee26837658\PCList.txt) do call :DoDir "\\%%A\c$\program files\adobe"
exit /b
:DoDir [full-path]
  for /R "%~1" %%B in (*.*) do echo "%%~B",%%~tB >>x:\_xfer\ee\ee26837658\dir.csv
  exit /b

Open in new window

~bp
0
 
LVL 52

Expert Comment

by:Bill Prew
ID: 34948480
(adjust file names for the txt and csv to your needs)
0
 

Author Comment

by:G_M
ID: 34948585
Good stuff... we're making progress... but how do I get it to ignore "\\%computername%\c$\program files" when it is writing to the CSV?

If each terminal has their UNC path at the start of the filepath then a pivot table will interpret them all as different files.

Cheers
G_M
0
 
LVL 38

Expert Comment

by:BillDL
ID: 34952385
I haven't had a chance to study this in any detail to consider this problem from scratch, and I can't test it on networked workstations from where I am, but the following variation of Bill Prew's batch file strips off the surrounding double-quotes in the path and strips off the:
\\%computername%\c$
part of the path.  It still, however, leaves:
\program files\
at the start.

I'm sure that routed through an external "change string" type command would reduce the results to the way you want, or else a simple Find > Replace in Notepad after the event could be used to strip off the \Program Files\ part.

I'll look at this later with a fresh mind, but Bill Prew will probably have come up with a working revision by then.

 
@echo off

for /f %%A in (C:\PCList.txt) do call :DoDir "\\%%A\c$\program files\adobe"
exit /b
:DoDir [full-path]
  for /R "%~1" %%B in (*.*) do (
     for /f "tokens=2 delims=^$" %%C in ("%%B") do (
        echo %%C,%%~tB >>C:\dir.csv
     )
  )
  exit /b

Open in new window

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 38

Expert Comment

by:BillDL
ID: 34952988
Try this instead. Not sure if it will work for you, but worth testing anyway.  I sorted results by name, so change it to /od or /o-d if you wish to sort by date.

I tried this with "tokens=4*"
on the paths that are like this:
\\%computername%\c$\Program Files\Adobe\Acrobat.com\Acrobat.com.exe

My expectation was that the 4th \ delimited token would be "Adobe" and that the * would then just treat the rest of the string after that literally and add it to the redirected output.  Perhaps Bill Prew can explain why the leading \\ and %computername% are ignored and "Adobe" is captured as the 3rd token in my final batch file.  There is obviously some "escaping" going on there with the leading \\

Anyway, it seems to work for me.  If you want the results double-quoted, just change to this:

echo "!TruncPath!","!TimeDate!">>dir.csv

I am not sure if the Times and dates reflect the "Last Modified" ones, but I hope so and I hope it works for your needs.

 
@echo off
SetLocal EnableDelayedExpansion

if exist dir.csv del dir.csv > nul

for /f %%A in (C:\PCList.txt) do (
    for /f "tokens=3* delims=^\" %%B in ('dir /a-d /on /b /-c /s "\\%%A\c$\Program Files\Adobe\*.*"') do (
    set TruncPath=%%C
    set TimeDate=%%~tB
    echo !TruncPath!,!TimeDate!>>dir.csv
    )
)

Open in new window

0
 
LVL 52

Accepted Solution

by:
Bill Prew earned 500 total points
ID: 34954339
Okay, give this a try.

@echo off
setlocal EnableDelayedExpansion

for /f %%A in (x:\_xfer\ee\ee26837658\PCList.txt) do call :DoDir "\\%%A\c$\program files\adobe" "\\%%A\c$\program files\"
exit /b

:DoDir [full-path] [remove-string]
  for /R "%~1" %%B in (*.*) do (
    call :DoTrim "%%~B" "%~2"
    echo "!Trim!",%%~tB >>x:\_xfer\ee\ee26837658\dir.csv
  )
  exit /b

:DoTrim [full-path] [remove-string]
  set In=%~1
  set Trim=!In:%~2=!
  exit /b

Open in new window

~bp
0
 

Author Comment

by:G_M
ID: 34956033
BillDL,

The first of you two scripts works, however, as you said does have "program files" in the filepath. The second of the two runs significantly faster and would be fantastic if it displayed the modified date. Unfortunately when I ran it it seems to write the same date for all the files (in my case 21/02/2011 03:01pm). I've had a look at the files and can pinpoint where it is pulling that information from.

If you could get that script working it would save significant time when processing a high quantity of PC's.

Billprew,

You script is a little slower than BillDL's second script, however, does everything I need it to. Thank you.
0
 

Author Comment

by:G_M
ID: 34956056
*** My last post without all the errors***

BillDL,

The first of your two scripts works, however, as you said does have "program files" in the filepath. The second of the two runs significantly faster and would be fantastic if it displayed the modified date. Unfortunately when I ran it it seems to write the same date for all the files (in my case 21/02/2011 03:01pm). I've had a look at the files and cannot pinpoint where it is pulling that information from.

If you could get that script working it would save significant time when processing a high quantity of PC's.

Billprew,

Your script is a little slower than BillDL's second script, however, does everything I need it to. Thank you.    
0
 
LVL 38

Expert Comment

by:BillDL
ID: 34959947
Hi G_M

Disregard that 2nd batch file that runs fast.  It is totally wrong and couldn't possibly gat the date.  It took me about 3 seconds with a fresh mind after a good sleep to see the glaring errors.  It seemed too good to be true, and Bill Prew is too much of a gentleman to shoot it down because he would rather I spot my own mistake.

The date that it was finding was the creation date of the "Program Files" folder, though I don't quite know why.
0
 

Author Comment

by:G_M
ID: 34974711
No worries... thank you for the feedback.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Update As Well As Add 6 39
macro for closing opened workbook 6 21
IF ELSE Statement in Excel Macro VBA 16 34
the code is not looping through 11 35
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

895 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

15 Experts available now in Live!

Get 1:1 Help Now