Batch Help needed to add a column and details in a CSV file

[majinfaisal]

Hi,

I require some help in modifying a csv file in batch.

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 (housenum 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, housenum
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 how to tackle this. All help will be very greatly appreciated!

[DosItHelp]

majinfaisal,

The following should work, assuming the name of the csv file is "mycsv1.csv":

Code: Select all

@echo off
(
    for /f "tokens=1-4 delims=," %%A in (mycsv1.csv) do (
        for /f "tokens=1-2 delims=." %%X in ("%%D") do (
            echo %%A,%%B,%%C,%%Y,%%X
))) > mycsv2.csv
pause

The output is created in a new file named "mycsv2.csv".
You might need to fix the title row manually, hope that's ok.

DOS IT HELP?

[majinfaisal]

Hi, thanks for the help.

Code is very close but the new column is not created. Would inserting the new columns first and then using the skip=1 function in the for statement be the best approach? Or is there another way to achieve this?

Many thanks again.

[DosItHelp]

majinfaisal,

Can you post the content of your resulting mycsv2.csv file?
mine looks like this:

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