[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1764
  • Last Modified:

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
0
sattermc
Asked:
sattermc
  • 5
  • 4
  • 3
  • +1
1 Solution
 
x-menIT super heroCommented:
can we go PowerShell ?
0
 
oBdACommented:
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

0
 
sattermcAuthor Commented:
This worked perfectly, Thank you!
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
sattermcAuthor Commented:
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 ?
0
 
QlemoC++ DeveloperCommented:
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

0
 
QlemoC++ DeveloperCommented:
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).
0
 
sattermcAuthor Commented:
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.
0
 
QlemoC++ DeveloperCommented:
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.
0
 
oBdACommented:
Works fine here with a trailing zero as well. In the meantime, here's a new version that can handle empty fields as well:
@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 (
	set Line=%%C
	set Line="!Line:,=","!"
	for /f "tokens=1,2,5 delims=," %%a in ("!Line!") 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

0
 
QlemoC++ DeveloperCommented:
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.
0
 
oBdACommented:
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

0
 
QlemoC++ DeveloperCommented:
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 .
0
 
sattermcAuthor Commented:
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.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now