Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Add colum to CSV through Batch or VB Script

Posted on 2014-01-08
14
Medium Priority
?
1,683 Views
Last Modified: 2014-01-08
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
Comment
Question by:sattermc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +1
14 Comments
 
LVL 18

Expert Comment

by:x-men
ID: 39765825
can we go PowerShell ?
0
 
LVL 85

Expert Comment

by:oBdA
ID: 39765826
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
 

Author Comment

by:sattermc
ID: 39765886
This worked perfectly, Thank you!
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:sattermc
ID: 39765910
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
 
LVL 71

Expert Comment

by:Qlemo
ID: 39765914
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
 
LVL 71

Expert Comment

by:Qlemo
ID: 39765935
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
 

Author Comment

by:sattermc
ID: 39765948
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
 
LVL 71

Expert Comment

by:Qlemo
ID: 39765963
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
 
LVL 85

Accepted Solution

by:
oBdA earned 2000 total points
ID: 39765985
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
 
LVL 71

Expert Comment

by:Qlemo
ID: 39766013
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
 
LVL 85

Expert Comment

by:oBdA
ID: 39766052
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
 
LVL 71

Expert Comment

by:Qlemo
ID: 39766089
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
 

Author Comment

by:sattermc
ID: 39766095
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

What’s Wrong with Your Cloud Strategy ?

Even as many CIOs are embracing a cloud-first strategy, the reality is that moving to the cloud is a lengthy process and the end-state is likely to be a blend of multiple clouds—public and private. Learn why multicloud solutions matter in this webinar by Nimble Storage.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
This article will show, step by step, how to integrate R code into a R Sweave document
Learn the basics of lists in Python. Lists, as their name suggests, are a means for ordering and storing values. : Lists are declared using brackets; for example: t = [1, 2, 3]: Lists may contain a mix of data types; for example: t = ['string', 1, T…
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
Suggested Courses

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question