parse csv to make more readable

Question

I am using PsInfo from sysinternals to return drive information in the form of a csv.

When I run this command.

psinfo -c -d volume

I get the following output.

PCName,,C:,Fixed,NTFS,,930.97 GB,705.81 GB,75.8%,D:,CD-ROM,,,,,0.0%,G:,Fixed,NTFS,My Book,1862.98 GB,889.71 GB,47.8%

My goal here is to parse that output to achieve this format:

PCNAME,,
,,C:,Fixed,NTFS,,930.97 GB,705.81 GB,75.8%
,,G:,Fixed,NTFS,My Book,1862.98 GB,889.71 GB,47.8%

So when the output is read by a spreadsheet viewer it appears readable and organized.

I've tried using regex to match the drive letter bit I don't know how to correctly capture the output.

Edit: (forgot to actually post my code...) This is what I have so far.

ECHO OFF
For /f "tokens=1*" %%x in ('net view ^| find "\\"') do (psinfo %%x filter -d -c >> out.csv & echo. >> out.csv)

::used to remove blanmk lines from output
@jrepl "^[ \t]*(.*?)[ \t]*$" "$1?$1:false" /jmatch /f out.csv /o -
pause

csv should hold values in each rows, your goal is a broken one. You can use Excel's filters to get grouping of what you want. — YOU, Nov 03 '16 at 02:02
Powershell has many string manipulation and parsing functions, even `Impor-CSV` and `Export-CSV`. You don't have a CSV though. Why don't you simply use a powershell command to get what you want? Just call `Get-Volume` — Panagiotis Kanavos, Nov 03 '16 at 12:49
The idea was to use psinfo to gather volume information from the network. Is this possible with powershell? — Solaris765, Nov 03 '16 at 16:13
Do you really want your output lines to be preceded by `,,`? Do you want your output to be filtered for `Fixed` drives (your input line contains `CD-ROM` drives also)? — aschipfl, Nov 04 '16 at 11:37

Squashman · Answer 1 · 2016-11-03T13:16:42.007

0

This worked for me. Given this example you should be able to play around with the format of the output you desire.

@echo off

FOR /F "tokens=1* delims=," %%G IN ('psinfo -c -d volume') DO (
    SET "pcname=%%G"
    SET "driveinfo=%%H"
)
set driveinfo="%driveinfo:,=","%"

:LOOP
FOR /F "tokens=1-7* delims=," %%G IN ("%driveinfo%") DO (
    echo "%pcname%",%%G,%%H,%%I,%%J,%%K,%%L,%%M
    SET "driveinfo=%%N"
)
IF DEFINED driveinfo GOTO LOOP

Output

"COCO","C:","Fixed","NTFS","","930.97 GB","705.81 GB","75.8%"
"COCO","G:","Fixed","NTFS","My Book","1862.98 GB","889.71 GB","47.8%"

And here is the code changed to replicate your exact needed Output.

@echo off

FOR /F "tokens=1* delims=," %%G IN ('psinfo -c -d volume') DO (
    SET "pcname=%%G"
    SET "driveinfo=%%H"
)
set driveinfo="%driveinfo:,=","%"
echo %pcname%,,
:LOOP
FOR /F "tokens=1-7* delims=," %%G IN ("%driveinfo%") DO (
    echo ,,%%~G,%%~H,%%~I,%%~J,%%~K,%%~L,%%~M
    SET "driveinfo=%%N"
)
IF DEFINED driveinfo GOTO LOOP

And here is doing it in a couple of less lines.

@echo off
setlocal enabledelayedexpansion

FOR /F "tokens=1* delims=," %%G IN ('psinfo -c -d volume') DO (
    echo %%G,,
    SET "driveinfo=%%H"
)
set "drive2=echo ,,!driveinfo:%%=%%&echo,,!"
%drive2:~0,-1%

edited Nov 03 '16 at 13:16

answered Nov 03 '16 at 04:28

Squashman

13,649
5
27
36

This would work better for me if i was importing it into a relational database but its not quite what I was looking for. I think @Magoo's answer was more along the lines what I was looking for. Though I'll be keeping this solution in the back of my mind if I ever need to do something like that. – Solaris765 Nov 03 '16 at 05:03
@Solaris765, this just tells me you did not bother to understand how the code works. You could make TWO small changes and make it output the same as Magoo's output, just like your example in your question that I updated for you. Quite insulting when people ask for a solution and do not bother to learn how the code works especially after I stayed up late last night updating your question with the CORRECT RELEVANT INFORMATION your original question was completely missing. – Squashman Nov 03 '16 at 12:29
Added another set of code to show you how easy the changes were. I even stripped the quotes but I prefer to keep the quotes. Still imports just fine into a DB with the quotes as we do it here every where I work. – Squashman Nov 03 '16 at 12:41
sorry I insulted you, and I do appreciate your editing of my post. But as I am still trying to understand how 'For' loops work in batch I chose to pursue an answer that presented information as I expected. Then try to learn from there. And now after your updates your post has more educational value since I can see the same code presented in different ways. – Solaris765 Nov 03 '16 at 16:44

score 0 · Answer 2 · answered Nov 03 '16 at 04:51

0

@ECHO OFF
SETLOCAL enabledelayedexpansion
FOR /f "delims=" %%a IN ('psinfo -c -d volume') DO SET "var=%%a"
SET "var2=!var:%%=\!"
SET "retained="
:again

REM FOR /f "tokens=delims==:" %%a IN () DO 
FOR /f "tokens=1*delims=:" %%a IN ("%var2%") DO (
 CALL :FORMAT "%%a"
 SET "var2=%%b"
 IF NOT "%%b"=="" GOTO again
)

GOTO :EOF

:FORMAT
SET "data=%~1"
IF "%data:~-1%"=="\" (SET "data=%retained%:%data%") ELSE (SET "data=%retained%:%data:~,-2%")
IF DEFINED retained (
 ECHO ,,!data:\=%%!
) ELSE (
 ECHO %data:~1%,
)
SET "retained=%~1"
SET retained=%retained:~-1%
GOTO :eof

substitutes \ for % for ease of processing.

answered Nov 03 '16 at 04:51

Magoo

77,302
8
62
84

Oh wow thank you. this is far mre complete then what I came up with on my own. I was using jrepl.bat to help remove spaces before. – Solaris765 Nov 03 '16 at 04:58
`Get-Volume` will return this information in row format directly. Additional formatting is very easy using simple Powershell commands – Panagiotis Kanavos Nov 03 '16 at 12:50

Panagiotis Kanavos · Answer 3 · 2016-11-03T13:43:55.043

You can get the same information with Powershell, eg with the Get-Volume command. This returns, everything except the ration eg:

DriveLetter FileSystemLabel FileSystem DriveType HealthStatus OperationalStatus SizeRemaining      Size
----------- --------------- ---------- --------- ------------ ----------------- -------------      ----
D                                      CD-ROM    Healthy      Unknown                     0 B       0 B
C           Win7            NTFS       Fixed     Healthy      OK                     43.59 GB 238.03 GB
H           Projects        NTFS       Fixed     Healthy      OK                     96.73 GB 375.24 GB

Get-Volume returns volume information objects. The shell just displays the most common ones.

You can select only the properties you want, and add more columns with the select command:

 Get-Volume | where {$_.DriveType -ne 'CD-ROM'} 
            | select DriveLetter, FileSystemLabel,FileSystem,SizeRemaining, Size, 
                     @{Name="Ratio";Expression={"{0:P}" -f ($_.SizeRemaining/$_.Size)}} 
            |Format-Table

DriveLetter FileSystemLabel FileSystem SizeRemaining         Size Ratio
----------- --------------- ---------- -------------         ---- -----
C           Win7            NTFS         46793633792 255584587776 18,31%
H           Projects        NTFS        103853056000 402911129600 25,78%

I'm cheating a bit here, by filtering out the CD ROM with where

If the results are too long to fit in the window, Powershell displays them as a list of name/values. The final Format-Table is used to force Powershell to format the results as a table

I can store the table to a file with a normal redirection :

 Get-Volume | where {$_.DriveType -ne 'CD-ROM'} 
            | select DriveLetter, FileSystemLabel,FileSystem,SizeRemaining, Size, 
                     @{Name="Rate";Expression={"{0:P}" -f ($_.SizeRemaining/$_.Size)}} 
            |Format-Table > somefile.txt

or I can export the results to a real CSV with Export-CSV

 Get-Volume | where {$_.DriveType -ne 'CD-ROM'} 
            | select DriveLetter, FileSystemLabel,FileSystem,SizeRemaining, Size, 
                     @{Name="Rate";Expression={"{0:P}" -f ($_.SizeRemaining/$_.Size)}} 
            | Export-CSV somefile.csv

Hmm, perhaps powershell might have been a better choice to pursue this goal. Especially since it seems like I can acheive the same gaol without PsInfo. As I'm unfamiliar with powershell, would it be possible to have it iterate through all the computers on a network? This si the primary reason I decided to try PsInfo. — Solaris765, Nov 03 '16 at 16:34
Yes, through the `-CIMSession` property. This allows you to query multiple machines at once. Or you can use a WMI query [as shown here](http://stackoverflow.com/questions/12159341/how-to-get-disk-capacity-and-free-space-of-remote-computer) — Panagiotis Kanavos, Nov 03 '16 at 16:40
@Solaris765, you could use WMIC as well, which also also you to output as a CSV and it will do it line by line instead of all on one line. — Squashman, Nov 03 '16 at 20:26

aschipfl · Answer 4 · 2016-11-04T12:22:38.550

The following code snippet reformats the output of psinfo -c -d volume as you wish. Since it seems that you want it filtered by drive type (Fixed) according to your sample data, I implemented such a feature; to not filter anything, simply change line set "FILTER=Fixed" to set "FILTER=":

@echo off
setlocal EnableExtensions DisableDelayedExpansion

rem // Define constants here:
set "FILTER=Fixed"
(set LF=^
%= empty line =%
)

for /F "tokens=1,* delims=, eol=," %%K in ('psinfo -c -d volume') do (
    echo(%%K,,
    set "LINE=%%L"
    setlocal EnableDelayedExpansion
    set ^"LINE=!LINE:%%,=%%^%LF%%LF%!^"
    for /F "delims= eol=," %%E in ("!LINE!") do (
        endlocal
        if defined FILTER (
            for /F "tokens=2 delims=, eol=," %%F in ("%%E") do (
                if "%%F"=="%FILTER%" (
                    echo(,,%%E
                )
            )
        ) else (
            echo(,,%%E
        )
        setlocal EnableDelayedExpansion
    )
    endlocal
)

endlocal
exit /B

parse csv to make more readable

4 Answers4