Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Question: how to re-arange CSV text file with batch?

288 views
Skip to first unread message

Tim Meddick

unread,
Jan 31, 2012, 5:47:34 AM1/31/12
to
I very rarely pose questions in any newsgroup - I just attempt to answer
ones I know that I am capable of. . .

However, having always believed that it's important to know the limits of
one's own levels of knowledge, I don't think anything's wrong in asking a
question, when you have to. . .

...And the thing is ; having a list in the form of comma-separated-values,
how would one use batch-file programming to re-arrange the list into order
of size of [a] value (one of the value-fields) ? Preferably, and / or /
also removing all other fields but the first (name) and just the one
numerical value (Mem Usage).

The list is the output generated by the TASKLIST.EXE utility as included in
the XP OS and a sample list is reproduced here (I have included the
Column-Headings here but will be absent in a working instance of the list)
;

"Image Name","PID","Session Name","Session#","Mem Usage"
"System Idle Process","0","Console","0","16 K"
"System","4","Console","0","32 K"
"SMSS.EXE","572","Console","0","44 K"
"CSRSS.EXE","636","Console","0","2,436 K"
"WINLOGON.EXE","660","Console","0","3,700 K"
"SERVICES.EXE","704","Console","0","2,424 K"
"LSASS.EXE","716","Console","0","1,560 K"
"svchost.exe","876","Console","0","1,636 K"
"svchost.exe","1000","Console","0","1,696 K"
"MsMpEng.exe","1092","Console","0","16,152 K"
"RapportMgmtService.exe","1128","Console","0","35,520 K"
"svchost.exe","1268","Console","0","15,672 K"
"SPOOLSV.EXE","1504","Console","0","1,552 K"
"GoogleCrashHandler.exe","176","Console","0","756 K"
"jqs.exe","260","Console","0","1,404 K"
"TIMESERV.EXE","416","Console","0","436 K"
"ALG.EXE","2888","Console","0","600 K"
"RapportService.exe","3932","Console","0","20,176 K"
"CTFMON.EXE","588","Console","0","2,216 K"


. . . I would be grateful for any assistance anyone can provide me with,
and thanks, in advance of any such help.

==

Cheers, Tim Meddick, Peckham, London. :-)

Tom Lavedas

unread,
Jan 31, 2012, 1:46:49 PM1/31/12
to
Here's my put ...

@%debug%echo off
setlocal&set "string="
if [%1]==[] %0 2 default sort by SessionID (column 2)
if %1==1 set string=yes
for %%N in (1 2) do if exist temp%%N.txt del temp%%N.txt
for /f "skip=1 delims=" %%a in (input.csv) do call :sub1 %1 %%a
sort temp1.txt > temp2.txt
for /f "tokens=2-6 delims=;" %%a in (temp2.txt) do echo %%a,%%b,%%c,%
%d,%%e
goto :EOF

:sub1
set data=%2;%3;%4;%5;%6
for /l %%n in (1,1,%1) do shift
if defined string (
echo %1;%data% >>temp1.txt
) else (
call set col=00000000%~1
echo %col:~-8%;%data% >>temp1.txt
)

It intended for a simple quoted CSV and does not attempt to deal with
an potential 'poison character' problems. If you take the header line
out of the input.csv file, remove the SKIP=1 form the first FOR
statement.
_____________________________________
Tom Lavedas

Timo Salmi

unread,
Jan 31, 2012, 1:56:19 PM1/31/12
to
On 31.01.2012 12:47 Tim Meddick wrote:
> ...And the thing is ; having a list in the form of
> comma-separated-values, how would one use batch-file programming to
> re-arrange the list into order of size of [a] value (one of the
> value-fields) ? Preferably, and / or / also removing all other fields
> but the first (name) and just the one numerical value (Mem Usage).
:
> "CSRSS.EXE","636","Console","0","2,436 K"

Without going to details (the others might do it better) I think the
essential key to this is in utilizing FOR /f "tokens=... delims=," ... a
bit like
http://www.netikka.net/tsneti/info/tscmd118.htm and maybe even
http://www.netikka.net/tsneti/info/tscmd047.htm#abook

All the best, Timo

--
Prof. (emer.) Timo Salmi, Vaasa, Finland
https://twitter.com/TimoSalmi
http://www.netikka.net/tsneti/homepage.php
Useful CMD script tricks http://www.netikka.net/tsneti/info/tscmd.php

Tom Lavedas

unread,
Jan 31, 2012, 2:18:08 PM1/31/12
to
> Tom Lavedas- Hide quoted text -
>
> - Show quoted text -

Opps, made an on-the-fly modification and introduced a 'delayed
expansion' problem that screwed up the sort. Here is the repaired
version that also keeps the header row in place ...

@%debug%echo off
setlocal&set "string="
if [%1]==[] %0 2 default sort by SessionID (column 2)
if %1==1 set string=yes
set /p line1=<input.csv
for %%N in (1 2) do if exist temp%%N.txt del temp%%N.txt
for /f "skip=1 delims=" %%a in (input.csv) do call :sub1 %1 %%a
sort temp1.txt > temp2.txt
echo.%line1%
for /f "tokens=2-6 delims=;" %%a in (temp2.txt) do echo %%a,%%b,%%c,%
%d,%%e
goto :EOF

:sub1
set data=%2;%3;%4;%5;%6
for /l %%n in (1,1,%1) do shift
if defined string (
echo %1;%data% >>temp1.txt
goto :EOF
)
set col=00000000%~1
echo %col:~-8%;%data% >>temp1.txt
___________________________________
Tom Lavedas

Tim Meddick

unread,
Jan 31, 2012, 4:55:57 PM1/31/12
to
Yup, that did it and did exactly what I wanted - except for two minor
irritations (I hesitate to state as one shouldn't really be looking a gift
horse in the mouth!) ....

1). Wanted the list from greatest at the top.

2). At the end of the screen output got the following :

'e' is not recognized as an internal or external command,
operable program or batch file.

...and couldn't really find a reason for this (the usual one being a rogue
"ampersand" in the CSV file - but no)....

But, on the whole, am very grateful for all your help. Thanks.

==

Cheers, Tim Meddick, Peckham, London. :-)




"Tom Lavedas" <tglb...@verizon.net> wrote in message
news:b43c55e5-1697-4621...@v14g2000vbc.googlegroups.com...

Tom Lavedas

unread,
Jan 31, 2012, 5:15:29 PM1/31/12
to
On Jan 31, 4:55 pm, "Tim Meddick" <timmedd...@o2.co.uk> wrote:
> Yup, that did it and did exactly what I wanted - except for two minor
> irritations (I hesitate to state as one shouldn't really be looking a gift
> horse in the mouth!) ....
>
> 1). Wanted the list from greatest at the top.
>
> 2). At the end of the screen output got the following :
>
> 'e' is not recognized as an internal or external command,
> operable program or batch file.
>
> ...and couldn't really find a reason for this (the usual one being a rogue
> "ampersand" in the CSV file - but no)....
>
> But, on the whole, am very grateful for all your help.  Thanks.
>
> ==
>
> Cheers,    Tim Meddick,    Peckham, London.    :-)
>
> "Tom Lavedas" <tglba...@verizon.net> wrote in message
> Tom Lavedas- Hide quoted text -
>
> - Show quoted text -

For the first problem, add a hard coded /R switch to the SORT line (or
provide it as an optional second command line argument ...

sort %2 temp1.txt > temp2.txt

Where the sort column must be provided as the first argument and /R
(not case sensitive) is the second, when you want to reverse the sort.

The second problem is probably due to a line ending in a carriage
return without a linefeed or the other way around. This can probably
be fixed by changing the first FOR statement to TYPE the file instead
of just reading it, as in ...

for /f "skip=1 delims=" %%a in (
'type input.csv') do call :sub1 %1 %%a

If that doesn't fix it, it may be caused by a word wrap problem with
one of the long lines in the posting. If a line starts with any
character in the first column, it is the result of an improperly
wrapped code line. Restore the character(s) on that line to the end
of the previous line. Note that I have purposely written the line of
code above so it is wrapped at a place that works correctly in two
lines. It does not need to be 'fixed'.
_______________________________
Tom Lavedas
0 new messages