asked on

Add colum to CSV through Batch or VB Script

I have a CSV with 16 columns in it. I am trying to find the most efficient way to add a 17th column that contains the first 4 characters from the first column, the first 3 characters from the second column and the last two characters from the fifth column. (please see attached file, column Q is what i am trying to add on to it.)

I am thinking I would use vbscript, or just plain batch scripting to get this done. However Scripting is not really my strength and time is a factor. I was hoping someone could steer me towards a solution i can call from a batch file that is being run by a scheduled task.

Thank you,
example2.csv

x-men

can we go PowerShell ?

oBdA

Try this for plain batch; just configure the file names in CsvFile and OutFile:

@echo off
setlocal enabledelayedexpansion
set CsvFile=example2.csv
set OutFile=example2-new.csv
if exist "%OutFile%" del "%OutFile%"
for /f "delims=" %%C in ('type "%CsvFile%"') do (
	for /f "tokens=1,2,5 delims=," %%a in ("%%C") do (
		set Col1=%%a
		set Col2=%%b
		set Col5=%%c
		echo Processing !Col1!, !Col2! ...
		set NewCol=!Col1:~0,4!!Col2:~0,3!!Col5:~-2!
		>>"%OutFile%" echo %%C,!NewCol!
	)
)
echo Done.

Open in new window

Mike Satterfield

ASKER

This worked perfectly, Thank you!

Mike Satterfield

ASKER

Actually, I spoke a bit too soon, If the last character in the fifth column is a 0 it ignores it, also if there is nothing in the third column, it considers the fourth column the thrird column..

Is there a way to fix that ?

Qlemo

That's clever, oBdA ... Though I would write the file only at the very end, to speed up processing.

In PowerShell it wouldn't look much different:

get-content example2.csv | % {
  $cols = $_.Split(',')
  ($cols + -join ($cols[0][0..3] + $cols[1][0..2] + $cols[4][-2, -1])) -join ','
} | Out-File example2-new.csv

Open in new window

Qlemo

The "empty column" issue is caused by the FOR /F not considering two delimiters as such, instead it ignores any delimiter until a non-delimiter character is following ... There is nothing easy to get that work.
The "trailing zero" issue I can't explain (yet).

Mike Satterfield

ASKER

I can live with the empty column issue, ill just populate it with a period or something. however the trailing zero.. is kind of a big deal.

Thanks for all your help.

Qlemo

Tested, and the trailing zero works for me. I've changed the 5th column to 703410, and the result is SimpHom10 . Please provide an example line not working.

ASKER CERTIFIED SOLUTION

oBdA

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Qlemo

Not exactly. You didn't count in the double quotes you introduced ;-). Positions need to be increased by one, with exception of the first and last columns.

Another point is that all processed columns (1,2,5) need to be at least of the lengths we cut out chars for, else the result will include double quotes now.

oBdA

When setting Col1, the "~" in %%~a strips the quotes right off again (accordingly for the others). My results look like this:

Simpson,Homer,J,M,703486,2,2024,6/7/2006,40,0,Doe; John,742 Evergreen Terrace,Springfiled,KY,53051,555-555-5555,SimpHom86
Himpson,Somer,,M,7034860,2,2024,6/7/2006,40,0,Doe; John,742 Evergreen Terrace,Springfiled,KY,53051,555-555-5555,HimpSom60

Open in new window

Qlemo

Oh, indeed, and very smart. You are adding another pair of double quotes to the whole line. That, of course, works, and my comment http:#a39766013 is void.

However, and of course, my PowerShell script looks better :D .

Mike Satterfield

ASKER

ODBA,

your new script works great. I tested it on my list of 4000 students, had no problems with trailing zero this time either, not sure what was going on before.

Thank you guys for your help.