Solved Manipulate CSV data using Dos Batch File

January 13, 2012 at 13:21:32
Specs: Windows 7, 4gb
Hi there everyone ,

I am a total noob in batch programming but have a very basic knowledge of dos which i have studied in college few years back

requirement :Batch file (Ms-dos) for manipulating csv data

i am uploading csv files(generated by an application ) to my server every few hrs automatically but i need to manipulate the data on csv file before i upload it to my server

the csv comes in a format


1. i want to add Name,Age,Sex,Phone to the first row of the csv , to name each column

2. i want to delete a column from the csv , lets say column D.

3. i want to search for certain search strings like age=16 and then copy all the rows with age colum = 16 to a new csv and save it

and in the last copy the manipulated and generated files to certain folder synchronised with my ftp

the application i am using to generate the data has an option to run macros after the process is complete , so i need macros which will run after each process completion and perform the following above task

i appreciate any kind of help or directions to perform the above tasks

Best regards

See More: Manipulate CSV data using Dos Batch File

Report •

January 13, 2012 at 17:23:07
1 2 and 3 seem to be fairly simple to achieve in batch scripting, for the rest I'd have to rely on you or another forum member to complete. Do you want me to write one script to perform all three options by user selection or would you prefer three independent scripts?

Please come back & tell us if your problem is resolved.

Report •

January 13, 2012 at 21:53:00
Thanks a million for the response mate
three independent will be much better

Report •

January 14, 2012 at 02:23:49
✔ Best Answer
You will be aware that batch scripting cannot alter files in situ, they must be re-written. Please test the following & come back with your results, good or bad.

@echo off

:: Adds Header Line To Existing 4-Column .csv file

echo Name,Age,Sex,Phone>output.csv

more input.csv>>output.csv

type output.csv

@echo off>output1.csv
setlocal enabledelayedexpansion

:: Deletes The Selected Column From Existing 4-Column .csv File.

set /p column=" Enter column number to delete: "

for /f "tokens=1-3* delims=," %%1 in (output.csv) do (
    set h1=%%1,&set h2=%%2,&set h3=%%3,&set h4=%%4
    set h!column!=
    set line=!h1!!h2!!h3!!h4!

    if !column! equ 4 (set line=!line:~0,-1!)
    echo !line!>>output1.csv
type output1.csv

@echo off
setlocal enabledelayedexpansion

:: Extracts Lines From Existing 4-Column .csv File Based On Column and Content.

set /p header=<output.csv
echo %header%>output2.csv

set /p column="Enter column to search:  "
set /p search="Enter column data to search:  "

for /f "skip=1 tokens=*" %%A in (output.csv) do (
    set inline=%%A
    for /f "tokens=1-4 delims=," %%1 in ("!inline!") do (
    set col1=%%1&set col2=%%2&set col3=%%3&set col4=%%$
        if !col%column%! equ !search! echo !inline!>>output2.csv

type output2.csv

Please come back & tell us if your problem is resolved.

Report •

Related Solutions

January 18, 2012 at 14:33:45
ok i have test all three , here are my results
i am kindda explaining things also , so that i can understand the code well, please correct if i am doint it wrong somewhere

First code you provide for ading a line :(Partially working )

Input.csv is the main file which i want to edit and this code is generating a new file by adding the custom first line and then copying the input file to it to generate new required file named output.csv

when i run this code , it generates a file and adds the first line but the command
More input.csv>>output.csv doesnt copy anything to the output file

the result i am getting by running this code is an output.csv file with only one line which is "name,age,sex,location"

Second code : for removing a column
i think i need to enter the column number where u have written "Enter column number to delete:"

i have entered 3 for example and nothing is happening , it gives the same output as the orignal file

Third code : havent actually tested it , i think i am doing something wrong while entering value for the column number etc will do it once i am able to get the above two working

I really appreciate all your help and advice and cant actually express how much this help means to me

Thanks a lot again and god blesss you

Report •

January 18, 2012 at 17:23:00
Puzzling, I tested the three scripts and all seem to be running ok and producing the expected results. Please post the actual scripts you are running.

I note that you say the one-line output from the first script is name,age,sex,location whereas the script should produce Name,Age,Sex,Phone, what other changes have you made?

Please come back & tell us if your problem is resolved.

Report •

January 19, 2012 at 06:35:33
I had some option checked in the software which is leading to the error , updated results

First one working now

For second one working also - if i copy the exact script u have posted , it will open up a dos window in which i have to put the number of column and then press enter
-- so this rerquires human input -is there a way i can do this without the need of enterning any value and pressing enter .like if i just want to delete the 4th row from the csv file with 5 columns

I havent tested the third one , lemme just get the first two workable and then i will focus on the third one

thanks again for your support

Report •

January 19, 2012 at 12:40:34
if i just want to delete the 4th row from the csv file with 5 columns

Sure, try this:

@echo off>fourcol.csv

:: Deletes The 4th column from existing 5-column .csv file

for /f "tokens=1-4* delims=," %%1 in (fivecol.csv) do (
    echo %%1,%%2,%%3,%%5>>fourcol.csv
type fourcol.csv

Please come back & tell us if your problem is resolved.

Report •

Ask Question