Solved

Batch file to edit file

Posted on 2013-12-10
21
464 Views
Last Modified: 2013-12-13
I have a file that is generated from a system automatically and within the file it has financial figures with a decimal point in.
For example
The file produces the value £56.40
As 000000000000000564
I need the file to produce the figure as 0000000000000005640

The file produces the value £31.43
000000000000031.43
I need the file to produce the figure as 0000000000000003143

So if the value is a whole number it drops the zero of the end.
If the value is not a round number it inserts a decimal point and requires an additional zero before the value.

The figure will always start from column line 2 and 50 spaces in and end at 68 spaces in.
The file could have 100s of lines.

I need a batch file to edit this file automatically.

Any help would be appreciated. It need to a windows bat file not PowerShell.

I have attached the file.
ifc.txt
0
Comment
Question by:luketr
  • 8
  • 7
  • 2
  • +2
21 Comments
 
LVL 68

Expert Comment

by:Qlemo
ID: 39710045
You are requesting that the line will be 1 char longer after that, right?
0
 

Author Comment

by:luketr
ID: 39710056
Yes that's correct
0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 39710057
Your main problem doing it in batch is you have & in descriptions and potential for other characters in their to cause issues.  Is a VBScript OK, you can call it from batch with cscript //nologo fixfile.vbs or the like?

Steve
0
 
LVL 51

Expert Comment

by:Bill Prew
ID: 39710062
Hey, that looks like COBOL output... :-)

~bp
0
 
LVL 51

Expert Comment

by:Bill Prew
ID: 39710076
You are requesting that the line will be 1 char longer after that, right?
I don't think the answer to this is "yes".  As I look at the data I see:

000000000000000084
000000000000000014
0000000000002100.3
0000000000002100.3
000000000000350.05
000000000004014.11
000000000004014.11

I think the desired result of this would be the following:

000000000000008400
000000000000001400
000000000000210030
000000000000210030
000000000000035005
000000000000401411
000000000000401411

Wouldn't it? Values with no "." in them should get "00" added to the end. Values with "." in them, and only one position to the right of it get the "." removed, and a "0" added to the right. Values with a "." in them, and two digits to the right get the "." removed, and one "0" added to the left.

This way we still have a fixed length field, but with an implied two decimal places.

~bp
0
 

Author Comment

by:luketr
ID: 39710082
Yes you are correct, thank you for correcting me
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 39710122
Sorry, but dragon-it is correct - no cmd processing because of the ampersand. It just cannot be done without external tools. I would take his advice and switch to VBS, since PS isn't an option for you.
0
 
LVL 3

Expert Comment

by:telczj9
ID: 39710154
How do you know you would have "ampersand"?
0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 39710183
This is the VBS I came up with:


dim objFSO, objFileIn, objFileOut
dim args,strFileName,strLine,strFix

set args = wscript.Arguments

if args.count <2 then
  wscript.echo "Usage: cscript //nologo fixit.vbs filename.txt newname.txt"
  wscript.quit
end if

strFileName=args(0)
strNewName=args(1)

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFileIn  = objFSO.OpenTextFile(strFileName)
Set objFileOut = objFSO.CreateTextFile(strNewName)

Do while not objFileIn.AtEndOfStream
  strLine=objFileIn.ReadLine

  if len(strLine) > 50 then
    strFix=mid(strLine,50,18)
    if mid(strFix,16,1)="." then
      strFix="0" & left(strFix,15) & mid(strFix,17,2)
    elseif mid(strfix,17,1)="." then
      strFix=left(strFix,16) & mid(strFix,18,2) & "0"
    else 
      strFix=mid(strFix,3) & "00"
    end if
     objFileOut.writeline left(strLine,49) & strFix & mid (strLine,68)
     wscript.echo strFix
 else
    ' Send any short line out as they are
    objFileOut.writeline strLine
    wscript.echo strLine
  end if

Loop 

objFileIn.Close
objFileOut.Close

Open in new window


It reads the filenames from the command line, i.e.

cscript //nologo fixit.vbs ifc.tct out.txt

It shows the changed lines as it makes them, remove the wscript.echo to hide that.

Steve
0
 

Author Comment

by:luketr
ID: 39713976
Hi Steve,

That worked brilliantly!

However I have had to remove 1 leading zero before the financial figure and I have tried to edit your script and haven't had much success.

So the file is generated with 17 characters before your script edits the file.

I have attached the amended source file.

I can log this as another question to give you more points if you require.


Thanks

Luke
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:luketr
ID: 39713978
0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 39714323
Here is fine, should be just a case of removing 1 from all the numbers needed that are effected, will see.


Steve
0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 39714350
Try this...  seems it starts at same place but 17 digits?  If so have changed so starts at position 50 still but gets 17 chars, not 18, and last line adds rest of line from one character less

dim objFSO, objFileIn, objFileOut
dim args,strFileName,strLine,strFix

set args = wscript.Arguments

if args.count <2 then
  wscript.echo "Usage: cscript //nologo fixit.vbs filename.txt newname.txt"
  wscript.quit
end if

strFileName=args(0)
strNewName=args(1)

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFileIn  = objFSO.OpenTextFile(strFileName)
Set objFileOut = objFSO.CreateTextFile(strNewName)

Do while not objFileIn.AtEndOfStream
  strLine=objFileIn.ReadLine

  if len(strLine) > 49 then
    strFix=mid(strLine,50,17)
    if mid(strFix,16,1)="." then
      strFix="0" & left(strFix,15) & mid(strFix,17,2)
    elseif mid(strfix,17,1)="." then
      strFix=left(strFix,16) & mid(strFix,18,2) & "0"
    else 
      strFix=mid(strFix,3) & "00"
    end if
     objFileOut.writeline left(strLine,49) & strFix & mid (strLine,67)
     wscript.echo "[" & strFix & "]"
 else
    ' Send any short line out as they are
    objFileOut.writeline strLine
    wscript.echo strLine
  end if

Loop 

objFileIn.Close
objFileOut.Close

Open in new window

0
 

Author Comment

by:luketr
ID: 39714456
I have used this script

dim objFSO, objFileIn, objFileOut
dim args,strFileName,strLine,strFix

set args = wscript.Arguments

if args.count <2 then
  wscript.echo "Usage: cscript //nologo fixit.vbs filename.txt newname.txt"
  wscript.quit
end if

strFileName=args(0)
strNewName=args(1)

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFileIn  = objFSO.OpenTextFile(strFileName)
Set objFileOut = objFSO.CreateTextFile(strNewName)

Do while not objFileIn.AtEndOfStream
  strLine=objFileIn.ReadLine

  if len(strLine) > 48 then
    strFix=mid(strLine,48,17)
    if mid(strFix,16,1)="." then
      strFix="0" & left(strFix,15) & mid(strFix,17,2)
    elseif mid(strfix,17,1)="." then
      strFix=left(strFix,16) & mid(strFix,18,2) & "0"
    else
      strFix=mid(strFix,3) & "00"
    end if
     objFileOut.writeline left(strLine,48) & strFix & mid (strLine,65)
     wscript.echo strFix
 else
    ' Send any short line out as they are
    objFileOut.writeline strLine
    wscript.echo strLine
  end if

Loop

objFileIn.Close
objFileOut.Close

Which has nearly worked just the values that have a decimal point are not quite correct.

It removes the decimal point but doesn't add an additional zero at the end.


Before 000000000000001.2

After

000000000000000012

required to be

0000000000000000120

I have attached the before file and the file after using the script file.
before.txt
after.txt
0
 

Author Comment

by:luketr
ID: 39714503
I have changed the starting position to 48 and have put that in the script too.
0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 39714597
will look in a bit, kids tea, bed, bath time etc. now...
best we go back to original one i did, the 50way bits will need amending as now, and the last bit to pickup rest of line.

the bit still to change is the middle part, didnt look before as thought had worked offset from right but didn't

will look when on pc.

steve
0
 

Author Comment

by:luketr
ID: 39714608
If you can get this working for me i will be over the moon.

Thanks

Steve
0
 
LVL 43

Accepted Solution

by:
Steve Knight earned 500 total points
ID: 39714830
I think this is it... while I watch fencing... I think that is same no. of chars as your before.txt and in the same position, if not say!

Steve

dim objFSO, objFileIn, objFileOut
dim args,strFileName,strLine,strFix

set args = wscript.Arguments

if args.count <2 then
  wscript.echo "Usage: cscript //nologo fixit.vbs filename.txt newname.txt"
  wscript.quit
end if

strFileName=args(0)
strNewName=args(1)

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFileIn  = objFSO.OpenTextFile(strFileName)
Set objFileOut = objFSO.CreateTextFile(strNewName)

Do while not objFileIn.AtEndOfStream
  strLine=objFileIn.ReadLine

  if len(strLine) > 49 then

' 48 here is the position of the first character to proces.  17 is the length

    strFix=mid(strLine,48,17)

' If the 15th char of those is a . then add 0 to beginning + last two digits
    if mid(strFix,15,1)="." then
      strFix="0" & left(strFix,14) & mid(strFix,16,2)
' If the 16th char of those is a . then take first 15, last 2 and a zero on the end
    elseif mid(strfix,16,1)="." then
      strFix=left(strFix,15) & right(strFix,1) & "0"
    else 
' Otherwise needs two chars stripped off front and 00 on the end
      strFix=mid(strFix,3) & "00"
    end if

' Output characters up to before the start point, the fixed bit and the part afterwards
     objFileOut.writeline left(strLine,47) & strFix & mid (strLine,65)
     wscript.echo "[" & strFix & "]"
 else
    ' Send any short line out as they are
    objFileOut.writeline strLine
    wscript.echo strLine
  end if

Loop 

objFileIn.Close
objFileOut.Close
                                            

Open in new window

0
 

Author Closing Comment

by:luketr
ID: 39716734
You are a legend, thank you so much for doing this.  Really appreciate it and works perfectly.

Thanks

Luke
0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 39716894
No problem... lot more reliable in VBScript for that than batch.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
This article was inspired by a question here at Experts Exchange (http://www.experts-exchange.com/Software/Photos_Graphics/Images_and_Photos/Q_28629170.html). The requirements stated in that question are (1) reduce the file size of a large number of…
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now