Windows Batch Scripting: Parse CSV file and extract data

Question

I am new to windows batch scripting so pardon my ignorance. I have a CSV file that looks like this:

ColumnA,ColumnB,ColumnC
01/02/2015,ABC,111
01/03/2015,DEF,222
01/03/2015,HHH,333
01/05/2015,XYZ,767

The number of rows in this file will vary but the columns will remain the same. I need to extract the date column A, row 1 and date from column A, last row.

In this case I need to extract 01/02/2015 and 01/05/2015. Next I want to store both these dates in separate variable.

How can I achieve this? I have no idea where to begin. The only thing I have is this:

for /F "tokens=1 delims=," %%s in (IntFlow.csv) do @echo %%s

This one line of code cuts the first column and echo's it. I don't know what do next. Please advice.

The first tip is that Powershell or any scripting engine other than batch is much better suited and easier to learn for things like this than batch scripting. — Tony Hinkle
– Tony Hinkle, Commented May 11, 2015 at 19:44
@rojo You raise a valid point. I have edited the example file to comma delimited. — St5987
– St5987, Commented May 11, 2015 at 20:09
@TonyHinkle Point taken. I have switched to PowerShell ISE. Thanks :) — St5987
– St5987, Commented May 11, 2015 at 20:11

rojo · Accepted Answer · 2015-05-11 20:40:50Z

3

Use a for /f loop to parse the csv file line-by-line. Use skip=1 to skip the header row. Only set the first variable if it isn't already defined. Then just set the last variable on every loop iteration. It's bound to be correct sooner or later, right? :)

@echo off
setlocal

set "first="
set "last="

for /f "skip=1 usebackq delims=," %%I in ("test.csv") do (
    if not defined first set "first=%%~I"
    set "last=%%~I"
)

echo %first%
echo %last%

tokens=1 is already the default behavior. usebackq allows you to enclose the filename in quotes, so it should work if your csv filename includes a space or some other unsavory character in the name.

I see in your comment you've decided to use PowerShell instead. Here's a PowerShell script that'll let you populate variables with the dates.

$csv = ipcsv test.csv
$first = ($csv | select -first 1 | %{ $_.ColumnA })
$last = ($csv | select -last 1 | %{ $_.ColumnA })

$first
$last

edited May 11, 2015 at 20:40

answered May 11, 2015 at 19:56

rojo

24.5k5 gold badges61 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

St5987 Over a year ago

your code works but it extracts the entire row. What I want to do is just take the date part from column A. Given the sample file above, the output should like this: 01/02/2015 01/05/2015

St5987 Over a year ago

I decided to switch back to batch. I am using APEX data loader and it can only ready bat files. Sorry about the confusion.

rojo Over a year ago

Does the entire row shown include commas? Or are the values space-delimited?

St5987 Over a year ago

Yes. It does include commas.

rojo Over a year ago

Unable to recreate. The code as I have it in my answer parses the csv as you have in your question and outputs only the first and last ColumnA values.

|

Collectives™ on Stack Overflow

Windows Batch Scripting: Parse CSV file and extract data

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related