Batch script to add/remove header n trailer

June 9, 2009 at 12:10:39
Specs: Windows XP
I am new to Batch Script for Windows so hoping you can help me out.
There are two things I need:
1) Say a1.csv is in C:\batch and has below info:
aaa 111 222 333 bbb 400
bbb 222 111 444 ccc 200
ccc 333 444 111 ddd 350
Need to create a header info like:
HDRYYYYMMDDa1
Trailer info:
TRL000000003000000000950.00
I need to copy C:\batch\a1.csv inserting the header and trailer info save it and copy to c:\batch\output folder.
2) C:\batch\a2.csv has below info:
HDRYYYYMMDDA2
aaa 111 222 333 bbb
bbb 222 111 444 ccc
ccc 333 444 111 ddd
TRL000000003
Need to make sure the total # of rows are correct in the trailer and the date in the header is correct. If yes, copy the file to C:\Batch\Output.

Really need help. Hope to hear from someone.


See More: Batch script to add/remove header n trailer

Report •


#1
June 9, 2009 at 17:25:59
so how do you come up with the format of the trailer? explain properly...


to answer your 2nd qns, if you have Python

data=open("a2.csv").readlines()
trailer=int(data[-1].replace('TRL',""))
num_lines = len(data[1:-1])
if num_lines == trailer:
    print "Total rows match."
else:
    print "Total rows don't match."

output
C:\test>more a2.csv
HDRYYYYMMDDA2
aaa 111 222 333 bbb
bbb 222 111 444 ccc
ccc 333 444 111 ddd
TRL000000003

C:\test>python test.py
Total rows match.

C:\test>more a2.csv
HDRYYYYMMDDA2
aaa 111 222 333 bbb
bbb 222 111 444 ccc
ccc 333 444 111 ddd
TRL000000004

C:\test>python test.py
Total rows don't match.


Report •

#2
June 9, 2009 at 18:39:11
In the Trailer, to count the total # of rows, the length is 9 char. So if there are 12 rows it would be 000000012. I need to create a .bat so would Python work?

Thanks for replying. Really appreciate it.


Report •

#3
June 9, 2009 at 19:21:19
i was asking about your first qns..
this : TRL000000003000000000950.00 means what???
you have 950 rows?? some more its not 9 chars.. what about the last decimal ".00"?? what do they represent???

>>I need to create a .bat so would Python work?
Python is a programming language (you have to download it). you just create a script like what i have shown, save it as something.py and use the python interpreter to run it. upon automation, your batch (eg mytest.bat) will only contain

c:\test> python myscript.py


Report •

Related Solutions

#4
June 10, 2009 at 06:39:58
TRL000000003000000000950.00 => 000000003 is the # of rows and 000000000950.00 is the total of column 6 (400+200+350) with 2 decimals.

So confused as I haven't done this before. But is this what I need to do:
1) Download python
2) Save the script myscript.py as shown above.
3) test.bat will contain c:\test\python myscript.py
and myscript.py will have :

data=open("a2.csv").readlines()
trailer=int(data[-1].replace('TRL',""))
num_lines = len(data[1:-1])
if num_lines == trailer:
print "Total rows match."
else:
print "Total rows don't match."

Is this ok? Thanks


Report •

#5
June 10, 2009 at 09:02:35
for your 1st qns, here's the solution in Python
import os,sys,time
filename,ext = sys.argv[1].split(".")
total=0
year,mth,day=time.localtime(time.time())[:3]
mth=str(mth).zfill(2)
day=str(day).zfill(2)
header="HDR"+str(year)+mth+day+filename
print header
for n,item in enumerate(open(filename+"."+ext)):
    item=item.strip()
    total=total + int(item.split()[-1])
    print item
    total_len=n+1
trailer="TRL"+str(total_len).zfill(9)+"%.2f" % total
print trailer

output:
C:\test>more a1.csv
aaa 111 222 333 bbb 400
bbb 222 111 444 ccc 200
ccc 333 444 111 ddd 350

C:\test>python test.py a1.csv
HDR20090610a1
aaa 111 222 333 bbb 400
bbb 222 111 444 ccc 200
ccc 333 444 111 ddd 350
TRL000000003950.00


redirect to a new file as needed.

>> Is this ok? Thanks
yes, download Python, then save the code as whatever name you want eg myscript.py, then on command prompt

# python myscript.py

Python emphasize on indentation, so copy the code as it is presented here with indentation.(verbatim)

Report •

#6
June 10, 2009 at 10:34:29
Hi,
1) I downloaded Python 2.6.1 (for windows binary).
2) Created batchscript.py with above code.
3) created a1.csv
a1 a2 a3 a4 a5 a6
aaa 111 222 333 bbb 400
bbb 222 111 444 ccc 200
ccc 333 444 111 ddd 350
4) Created a1.bat with below line
@c:\Amex\batch\Input\batchscript.py
5) at the command line > a1.bat a1.csv
this is what I get:

C:\Amex\Batch\Input>c:\Amex\batch\Input\batchscript.py
Traceback (most recent call last):
File "c:\Amex\batch\Input\batchscript.py", line 2, in <module>
filename,ext = sys.argv[1].split(".")
IndexError: list index out of range


Report •

#7
June 10, 2009 at 17:37:59
>> 4) Created a1.bat with below line
>> @c:\Amex\batch\Input\batchscript.py
>> 5) at the command line > a1.bat a1.csv
>>

this is wrong... you pass a1.csv to python script like this:

C:\test> python myscript.py a1.csv

in your batch file just execute the .bat

c:\test> a1.bat

you can then put a1.bat as a scheduled job. However, you can also set up your scheduled job to run just python , no need to create a batch file for that, because python itself is an executable..


Report •

#8
June 15, 2009 at 13:01:07
That helped. Now getting an error adding up the total:
a1.csv has
a1 a2 a3 a4 a5 total
aaa 111 222 333 bbb 400
bbb 222 111 444 ccc 200
ccc 333 444 111 ddd 350

Traceback (most recent call last):
File "c:\Amex\batch\Input\batchscript.py", line 11, in <module>
total=total + int(item.split()[-1])
ValueError: invalid literal for int() with base 10: 'a1,a2,a3,a4,a5,total'

I have to create a batch file. So that's why I created .bat.

Thanks


Report •

#9
June 15, 2009 at 18:25:20
>> 'a1,a2,a3,a4,a5,total'
you data has "commas", therefore split on commas

total=total + int(item.split(",")[-1])

i suggest you download Python to play with it, not just for this task, but many other tasks you might have in future.

GNU win32 packages


Report •

#10
June 16, 2009 at 14:17:57
that didn't work. I added the ',' and now gives an error "valueerror: invalid literal for int() with base 10: 'total'.

not sure what else I need to download.
thx


Report •

#11
June 16, 2009 at 17:50:33
no need to download anymore since you already have Python (right?).... show me your code (remember to indent and put in pre tags) and the input data you are using..show any other errors you have, if any.

GNU win32 packages | Gawk


Report •

#12
June 17, 2009 at 09:31:53
For now, lets forget about the total. I am just so lost.
Back to 2): Need to make sure header and trailer match.
In header - need to match if the current date is what's in the HDR row (20090617) and name of the file is what we are reading from (which is a2).
In trailer - Need to make sure total # of rows match what's in the file.

a2.csv file looks like this:

HDR20090617a2
aaa,111,222,333,bbb
bbb,222,111,444,ccc
ccc,333,444,111,ddd
TRL000000003

DO I need python for this? If not, what should be in .bat file. If this is doable in .bat file, please ignore the rest of the Questions.


If need python then please read:
Starting from scratch what do I need to do.
This is what I have:
1) Need to create a .bat file. Inbound.bat is like this:
c:\Amex\batch\Input\inbound.py a2.csv

2) what should be in inbound.py?
If header and trailer matches then cut the header and trailer from the file and save the file as new file a2new.csv.

Please send this to me ASAP. I tried a few things nothing is working. Also, python inbound.py does not work.

Thanks



Report •


Ask Question