Page 1 of 2
Removing header and trailer row, removing field in csv file
Posted: 06 Oct 2014 04:50
by 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
Re: Removing header and trailer row, removing field in csv f
Posted: 06 Oct 2014 07:04
by foxidrive
Please explain the task with examples.
Re: Removing header and trailer row, removing field in csv f
Posted: 06 Oct 2014 07:05
by Squashman
Would need to see an example of the data before any of us could help you.
Re: Removing header and trailer row, removing field in csv f
Posted: 06 Oct 2014 08:14
by 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,,,,,,,
Re: Removing header and trailer row, removing field in csv f
Posted: 06 Oct 2014 08:27
by vsmeruga
ID - 1.1112E+11 - is a 11 digit number
Re: Removing header and trailer row, removing field in csv f
Posted: 06 Oct 2014 09:01
by Squashman
Will any of the fields ever have empty data?
Re: Removing header and trailer row, removing field in csv f
Posted: 06 Oct 2014 09:02
by vsmeruga
Yes. fields can be empty.
Re: Removing header and trailer row, removing field in csv f
Posted: 06 Oct 2014 09:20
by 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.batPlace `findrepl.bat` in the same folder as the batch file or on the path.
Re: Removing header and trailer row, removing field in csv f
Posted: 06 Oct 2014 09:40
by vsmeruga
Thank you very much. I will try it and respond back.
Re: Removing header and trailer row, removing field in csv f
Posted: 06 Oct 2014 09:43
by 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
Re: Removing header and trailer row, removing field in csv f
Posted: 06 Oct 2014 10:04
by foxidrive
It will also work for more rows and more columns, as long as the format of the data is the same.
Re: Removing header and trailer row, removing field in csv f
Posted: 07 Oct 2014 04:25
by 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
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.
Re: Removing header and trailer row, removing field in csv f
Posted: 07 Oct 2014 04:46
by foxidrive
Search this forum for FINDREPL.BAT
You got the wrong helper batch file.
Re: Removing header and trailer row, removing field in csv f
Posted: 07 Oct 2014 05:15
by 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
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,,,,,,,
Re: Removing header and trailer row, removing field in csv f
Posted: 07 Oct 2014 05:42
by foxidrive
vsmeruga wrote: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.