Removing header and trailer row, removing field in csv file

[vsmeruga]

Hello

I am an Architect and not a windows batch scripter and I need help to write a small script to remove header and footer of a csv file located in a specific directory and also I should remove an unwanted field from the list of fields.

Please help me on this .

Many Thanks
sheku

[foxidrive]

Please explain the task with examples.

[Squashman]

Would need to see an example of the data before any of us could help you.

[vsmeruga]

Hi Sorry for the delayed reply .

File looks like below, it is a .csv file, I should remove header, trailer and "ID" field with value from the file.

0HEADER20140930-100111,,,,,,,
Date,Exch,Product,ID,Price,Multiplier,Account,Tax
20140930,20Y ,ABC ,1.1112E+11,1000,10,12347890,30
9TRAILER00000004,,,,,,,

[vsmeruga]

ID - 1.1112E+11 - is a 11 digit number

[Squashman]

Will any of the fields ever have empty data?

[vsmeruga]

Yes. fields can be empty.

[foxidrive]

Here is a robust solution for the data you have shown:

Code: Select all

type "file.csv"|findrepl /o:2:-2 |findrepl "(.*?,.*?,.*?,).*?,(.*)" "$1$2" >"newfile.csv"

This uses a helper batch file called `findrepl.bat` (by aacini) - download from: https://www.dropbox.com/s/rfdldmcb6vwi9xc/findrepl.bat

Place `findrepl.bat` in the same folder as the batch file or on the path.

[vsmeruga]

Thank you very much. I will try it and respond back.

[vsmeruga]

Hi foxidrive

This is the example data I have provided. I get more rows every day in a single file with header and footer. I believe the solution provided to me will work for more rows

[foxidrive]

It will also work for more rows and more columns, as long as the format of the data is the same.

[vsmeruga]

Hi

I do not have access to dropbox and I could not get the file( repl.bat)
instead I found another link in the net as below and I tried creating a batch file(repl.bat) based on it but not succeded. Error came as shown as below
Please correct me what I have done wrong.

Thanks
Sheku

http://www.computerhope.com/forum/index ... 787.5;wap2

F:\>type "file.csv"|findrepl /o:2:-2 |findrepl "(.*?,.*?,
.*?,).*?,(.*)" "$1$2" >"newfile.csv"
'findrepl' is not recognized as an internal or external command,
operable program or batch file.

[foxidrive]

Search this forum for FINDREPL.BAT

You got the wrong helper batch file.

[vsmeruga]

Hi I copied the code from the below link and created findrepl.bat and got the below error. My file.csv looks like below

viewtopic.php?f=3&t=4697&hilit=FINDREPL.BAT

F:\>type "file.csv"|findrepl /o:2:-2 |findrepl "(.*?,.*?,
.*?,).*?,(.*)" "$1$2" >"newfile.csv"
ERROR: Insufficient arguments. Use REPL /? to get help.

0HEADER20140930-100111,,,,,,,
Date,Exch,Product,ID,Price,Multiplier,Account,Tax
20140930,20Y ,ABC ,1.1112E+11,1000,10,12347890,30
9TRAILER00000004,,,,,,,

[foxidrive]

Hi I copied the code from the below link and created findrepl.bat and got the below error. My file.csv looks like below

You are still using the wrong batch file.

ERROR: Insufficient arguments. Use REPL /? to get help.