how to parse a csv file using batch script?

Question

I'm pretty new with batch files. Can someone please help me to write a batch script that will parse a csv file that looks like this:

"Expert Info (Chat/Sequence): GET /?password=Katy HTTP/1.1\r\n","Feb 20, 2014 19:34:46.571807000","b5:54:f4:v7:xo:6l"

"Expert Info (Chat/Sequence): GET /?password=Cory HTTP/1.1\r\n","Feb 20, 2014 19:34:51.671167000","b5:54:f4:v7:xo:6l"

"Expert Info (Chat/Sequence): GET /?password=Mike HTTP/1.1\r\n","Feb 20, 2014 19:34:57.145898000","b5:54:f4:v7:xo:6l"

and turn it to a another csv file that looks like this:

"Katy", "2014-02-20", "19:34:46", "b5:54:f4:v7:xo:6l" 
"Cory", "2014-02-20", "19:34:51", "b5:54:f4:v7:xo:6l" 
"Mike", "2014-02-20", "19:34:57", "b5:54:f4:v7:xo:6l"

here is what I wrote:

@echo off
FOR /F "tokens=6,8,9,10,11* delims=,? " %%a in (file.csv) do (

set pass=%%a
set month=%%b
set day=%%c
set year=%%d
set sec=%%e
set mac=%%f
echo "%%a" %%b %%c %%d %%e %%f

if %month:~1,10%==Jan set month=01
if %month:~1,10%==Feb set month=02
if %month:~1,10%==Mar set month=03
if %month:~1,10%==Apr set month=04
if %month:~1,10%==May set month=05
if %month:~1,10%==Jun set month=06
if %month:~1,10%==Jul set month=07
if %month:~1,10%==Aug set month=08
if %month:~1,10%==Sep set month=09
if %month:~1,10%==Oct set month=10
if %month:~1,10%==Nov set month=11
if %month:~1,10%==Dec set month=12

echo "%pass:~9,4%", "%year%-%month:~1,10%-%day%", "%sec:~,8%", %mac% >> text.csv)

Take a look at the `for` command - specifically using the `/f` switch. You should post questions here with problems you encounter when trying to solve a problem, not with the problem itself. — unclemeat, Feb 20 '14 at 22:35

score 2 · Answer 1 · edited May 23 '17 at 12:00

The simplest way to convert one data into another, like a month name into a month number, is using an array:

@echo off
setlocal EnableDelayedExpansion

rem Create the conversion array of month names to month numbers
set m=100
for %%a in (Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec) do (
   set /A m+=1
   set month["%%a]=!m:~1!
)

(for /F "tokens=6,9,10,11,12,14 delims==,. " %%a in (file.csv) do (
   echo "%%a", "%%d-!month[%%b]!-%%c", "%%e", %%f
)) > text.csv

score 2 · Accepted Answer · answered Feb 21 '14 at 00:25

@ECHO OFF
SETLOCAL
(
FOR /F "tokens=6,8,9,10,11* delims=,? " %%a in (q21921051.txt) do (
 set pass=%%a
 set month=%%b
 set day=%%c
 set year=%%d
 set sec=%%e
 set mac=%%f
 CALL :CALC
)
)> text.csv
GOTO :EOF
:calc

if %month:~1,10%==Jan set month=01
if %month:~1,10%==Feb set month=02
if %month:~1,10%==Mar set month=03
if %month:~1,10%==Apr set month=04
if %month:~1,10%==May set month=05
if %month:~1,10%==Jun set month=06
if %month:~1,10%==Jul set month=07
if %month:~1,10%==Aug set month=08
if %month:~1,10%==Sep set month=09
if %month:~1,10%==Oct set month=10
if %month:~1,10%==Nov set month=11
if %month:~1,10%==Dec set month=12

echo "%pass:~9,4%", "%year%-%month%-%day%", "%sec:~,8%", %mac%

GOTO :eof

I used a file named q21921051.txt for my testing.

Nearly there. Your major problem is delayed expansion - within a block statement (a parenthesised series of statements), the entire block is parsed and then executed. Any %var% within the block will be replaced by that variable's value at the time the block is parsed - before the block is executed.

Hence, IF (something) else (somethingelse) will be executed using the values of %variables% at the time the IF is encountered - the same thing applies to a FOR ... DO (block)

Two common ways to overcome this are 1) to use setlocal enabledelayedexpansion and use !var! in place of %var% to access the changed value of var or 2) to call a subroutine to perform further processing using the changed values.

I've used the second method here

Note also that (parenthesising the entire routine) will redirect all of its output using the redirector - >text.csv in this case. The > should be >> if you wish to append rather than create the file anew.

I'm not sure what the obsession is with %month:~1,10%. Substrings in batch are obtained from %var:~m,n% where ,n is optional; m is count-of-chars-from-beginning-of-string, from end if negative. ,n positive = max length to return; negative = end-position in chars from end; missing=return all after m

Hence, since %month% will contain "Feb, all that is required is %month:~1% - but I left your original.

how to parse a csv file using batch script?

2 Answers2

Linked

Related