Hi,
I require some help in modifying a csv file using a batch script.

I have a csv file that contains details obtained from a database. So an example construct is as follows: -

id, first_name, second_name, location
1, john, locke, 123.someroad
2, jack, shepard, 555.island
3, abc, khan, 888.house
4, popeye, sailor, 143.boathouse

What i need the batch script to do is to create another column in the csv file (housen in this example) and to move the digits in the location before the period character into that column. So the output will be like: -

id, first_name, second_name, location, housen
1, john, locke, someroad, 123
2, jack, shepard, island, 555
3, abc, khan, house, 888
4, popeye, sailor, boathouse,143

I am rather new to batch scripting so not exactly sure how to tackle this. All help will be very greatly appreciated!

Assumed your .csv file is data.csv, then

@Echo Off > data-new.csv
For /F "tokens=1-5 delims=,. " %%a in (data.csv) Do (
Echo %%a, %%b, %%c, %%e, %%d>> data-new.csv
)

and data-new.csv will hold the new format.

@echo off
setLocal EnableDelayedExpansion

for /f "tokens=1-4 delims=, " %%a in (my.csv) do (
set /a N+=1
if !N! equ 1 (echo id, first_name, second_name, location, housen > new.csv
) else (
for /f "tokens=1-2 delims=." %%P in ('echo %%d') do (
set st=%%Q & set hn=%%P
)
echo %%a, %%b, %%c, !st!, !hn! >> new.csv
)
)

=====================================
If at first you don't succeed, you're about average.

M2

Hi,

Thanks for the help so far.
This successfully pulls out the number from the location and moves this to a seperate column as should, but does not add a column header 'housen' to accompany the new column set. What i think must be done is to add this column name before this code is executed but i am not sure how this is done exactly.

Again many thanks for the help so far.

Hi IVO,

I gotta learn to type faster. LOL

Yours is better.

=====================================
If at first you don't succeed, you're about average.

M2

Hi, i have just tested the script by Mechanix2Go and it seems to do the trick!!!

Thanks again for all the help provided into tackling this problem!

BTW is it possible to manipulate the first script to add the housen column? As its much simpler to understand lol

@Echo Off > data-new.csv
setLocal EnableDelayedExpansion

For /F "tokens=1-5 delims=,. " %%a in (data.csv) Do (
set /a N+=1
if !N! equ 1 (echo id, first_name, second_name, location, housen > data-new.csv
) else (
Echo %%a, %%b, %%c, %%e, %%d>> data-new.csv
)
)

=====================================
If at first you don't succeed, you're about average.

M2

Suggested change?

echo id, first_name, second_name, location, housen > data-new.csv
For /F "skip=1 tokens=1-5 delims=,. " %%a in (data.csv) Do Echo %%a, %%b, %%c, %%e, %%d>> data-new.csv

Well coded, Razor2.3.

When I posted my version of the script, in Italy time was so late in night (and after a sunday of hard work) that no way I took care of the presence of a header row.

Hi, i just tried the code by Razor and it only returns the column names but not the data

Seeing how I openly stole the code you acknowledge as working, I'd say the problem is on your end.

My guess, the script can't find data.csv

I seem to have come up with a new problem. if i take the sample data set: -

id, first_name, second_name, location, housen
1,, locke, someroad, 123

and try to parse with comma delimiter i seem to get this: -

1,locke, someroad, 123,,

Essentally empty tokens are being filled with whatever token is next in line. Is there a fix for this? Thanks in advance!

here's an alternative:
[code]
Set objFSO=CreateObject("Scripting.FileSystemObject")
myFile = "c:\temp1\a.txt"
outFile = "c:\temp1\b.txt"
Set objFile = objFSO.OpenTextFile(myFile,1)
Set objOutFile = objFSO.CreateTextFile(outFile,True)
objOutFile.Write(objFile.ReadLine & ",Housen" & vbCrLf )
Do Until objFile.AtEndOfLine
splitted = Split(objFile.ReadLine,",")
splitaddr = splitted(3)
number= Split(splitaddr,".")(0)
address=Split(splitaddr,".")(1)
splitted(3) = address
objOutFile.Write(Join(splitted,",") & "," & number & vbCrLf )
Loop
objOutFile.Close
objFile.Close
[/code]

Hi, thanks for the alternative solution.
Im stuck to using regular dos batch scripting though and not vbscript.
it is easier to follow and understand the vbscript example though!