Tom's Guide | Tom's Hardware | Tom's Games
 |
 |
 |
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.boathouseWhat 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,143I 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.
0 
@echo off
setLocal EnableDelayedExpansionfor /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
0
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.
0
Hi IVO,
I gotta learn to type faster. LOL
Yours is better.
=====================================
If at first you don't succeed, you're about average.M2
0
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!
0
BTW is it possible to manipulate the first script to add the housen column? As its much simpler to understand lol
0
@Echo Off > data-new.csv
setLocal EnableDelayedExpansionFor /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
0
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
0
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.
0
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
0
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, 123and 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!
0
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]
0
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!
0  |
 |
 |
This post is quite old and has been locked from receiving new replies. Please create a new posting instead.
| Ads by Google |
