CSV file, row combining based on column data

January 22, 2010 at 14:22:05
Specs: Windows XP, 2ghzcore2duo / 2gigs
Im having some real trouble trying to find a freeware program to do this simple function.

I know nothing about running batch files or anything like that so even if you can help me, you'd have to pretend you're giving the instructions to a 5 year old.

I need to be able to process .csv files in a special way.

Column 1 | Column 2,
Row 1 John Doe | A1
Row 2 John Doe | B1
Row 3 John Doe | C1
Row 4 Jane Doe | A1
Row 5 Jane Doe | B1
Row 6 Jane Doe | C1

I need to be able to make the CSV file look like this...

John Doe | A1 B1 C1
Jane Doe | A1 B1 C1

Smart folks please help LoL Ill love you forever from afar in a non stalker way. ^_^


See More: CSV file, row combining based on column data

Report •

#1
January 22, 2010 at 16:38:33
@echo off>xxx & setlocal enabledelayedexpansion
set prev=
for /f "tokens=1,2 delims=|" %%a in (xx) do (
if "%%a" equ "!prev!" (
set out=!out! %%b
) else (
call :uu
set prev=%%a
set out=%%a ^| %%b
)
)
:uu
if "%prev%" neq "" >> xxx echo.!out!

>> "Ill love you forever from afar in a non stalker way"

sorry, i'll have to pass, wrong gender i believe...
a female substitute (foxy proxy) would be accepted however


Report •

#2
January 23, 2010 at 05:41:25
Nbrane, this'll not work, since "Row 1 John Doe" is not equal to "Row 2 John Doe"

Also, question to Jordan, do the similar lines follow ? In other words, is this possible ?

Row 1 John Doe | A1
Row 2 John Doe | B1
Row 3 Jane Doe | A1
Row 4 John Doe | C1
Row 5 Jane Doe | B1
Row 6 Jane Doe | C1


Report •

#3
January 23, 2010 at 10:00:44
the similar lines always follow yes so you will not see a

John A1
Jane A1
John B1
Jane B1

always grouped togeather

thanks for the replies


Report •

Related Solutions

#4
January 24, 2010 at 05:58:48
Tested code below ... please note:
- assuming the name is always 2 words only !!
- assuming you are using whitespaces as in the example
- assuming the word "Row" is EXACTLY as written in example


@echo off
setlocal enabledelayedexpansion

set infile=xx
set outfile=xxx
set prevname=

if exist %outfile% del %outfile%

for /f "tokens=1,2 delims=|" %%a in ('type %infile% ^| findstr /C:"Row " ') do (
for /F "tokens=3* delims= " %%x in ("%%a") do (
set currname=%%x %%y
)
if "!currname!"=="!prevname!" (
set col2=!col2!%%b
) ELSE (
if NOT "!prevname!"=="" (
echo !prevname!^|!col2!>> %outfile%
)
set prevname=!currname!
set col2=%%b
)
)

:uu
echo !prevname!^|!col2!>> %outfile%

echo.
echo Result:
type %outfile%


Report •

#5
January 24, 2010 at 12:13:44
i went with the assumption that ROW 1, ROW2 etc. were not part of the textfile, but labels for illustrating the file's format. (same with the 1st line, "COLUMN1 COLUMN2")
that's why i didn't code for it...

Report •

#6
January 24, 2010 at 14:01:40
I was thinking the same, but then I assumed that input and ouput file were exactly as shown initially ....

As somebody once said; if you assume, it may make an A55 of U & ME


Report •

#7
January 24, 2010 at 15:09:27
oh well, either way i guess the bases are covered now!

Report •

#8
January 24, 2010 at 22:06:03
hey thanks alot folks!

Report •

Ask Question