Baanboard.com

Go Back   Baanboard.com > Forum > Baan SIGs > Code & Utilities

User login

Frontpage Sponsor

Main

Poll
For ERP LN feature pack upgrade, what method of install are you using?
Installation Wizard into existing VRC
35%
Installation Wizard into new VRC
42%
Manual into existing VRC
3%
Manual into new VRC
19%
Total votes: 31

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #1  
Old 29th March 2002, 17:07
sitarammani sitarammani is offline
Junior Member
 
Join Date: Jan 2002
Posts: 14
sitarammani is on a distinguished road
AWKward Script for Baan Reports' passage to MS Excel

The attached script e.awk will provide an easy way for your
Baan report to MS Excel.

Benefits:
With full respects to all who suggested the direct excel.exe
solution, this script will save the user from doing the
operations required to convert delimited text to columns.

1> You will need AWK MS-DOS version to use this.
(I have selected the MS dos Route, I leave it to the reader to
try out the UNIX route).
AWK.exe ideally should reside in c:\windows\command
(windows will easily find it).
The version of awk suggested consists of just one file awk.exe.
Source[s] to obtain get awk.exe for MS-DOS:
Check the sources listed below
Why AWK?
The distribution suggested has a very small footprint.
It is lightweight. Only one exe file. ..........................(A)

If your company policy prevents you from using awk.exe
from the the net, simply translate the logic to WSH to
use it with wscript.exe or cscript.exe

2> You will need to define a device in Baan.
You may be familiar, but described below. .......................(B)

3> You will need to create excel.bat in
c:\windows\ ( not c:\windows\command) directory
This should contain just one line as follows
awk -f c:\baan\e.awk %1
(Modify the path of e.awk if you have to)

4> You should agree to the script e.awk removing all
occurance of ',' in the Baan report's character, numeric
and any other fields.

5> You should agree to the script e.awk deleting
BaanReport.txt (file coming from Baan) and accept to view
the newly created file BaanReport.csv in MS Excel.
So also if BaanReport.csv exists it will be overwritten.

6> You will ensure CSV files are associated with MS Excel
in Windows. (most likely they are)

7> Download the script e.awk and let it reside in
c:\baan (suitably change the path if baan directory is different)


(A)................................... Source you can get awk.exe free

http://cm.bell-labs.com/who/bwk/awk95.exe
ftp://ftp.oxy.edu/public/mawk113exe.zip

I prefer the distribution from
http://cm.bell-labs.com/who/bwk/awk95.exe
This accepts long file names.
copy awk95 exe c:\windows\command\awk.exe

Do retain a copy awk95.exe
(in case windows is reloaded it will be handy)
Please Maintain a backup of :
e.awk
excel.bat
along with awk95.exe

If you need awk documentation, it is available in UNIX Man.
For MS-DOS version search for MAWK.DOC in the net, others
also available.

.......................................................................................

(B)................................... Define Device using Maintain Devices Session
Device : MSEXCEL
Description : MS Office Excel
Device Type : Rewrite File
....
....
..........Skip these fields......
....
....
4GL Program : ottstpconv
Argument : ascii:c:\windows\excel.bat %s
path :/tmp/
change allowed :YES
Page Length :72
......................................

Ensure the path mentioned in excel.bat is correct. Change the path in excel.bat if you need to.
Attached Files
File Type: zip awkwards.zip (510 Bytes, 307 views)

Last edited by sitarammani : 2nd April 2002 at 19:16.
Reply With Quote
  #2  
Old 8th April 2002, 21:31
Bob Ino's Avatar
Bob Ino Bob Ino is offline
Junior Member
 
Join Date: Apr 2002
Location: Montreal
Posts: 12
Bob Ino is on a distinguished road
Baan: 4c4 - DB: Oracle 7.3.4 - OS: Aix RS6000
Unhappy Your little Excel/Awk trick ....

It's not working, nothing produced in temp directory.
no ms excel fired pu after completion of script.

I'll try a C program...
Reply With Quote
  #3  
Old 8th April 2002, 22:06
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 6,905
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
Will try it tomorrow

If I get a chance I will give this a try tomorrow and get back to you.

Mark
Reply With Quote
  #4  
Old 9th April 2002, 14:15
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 6,905
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
Worked for me...

Not a bad idea. It worked for me. A couple of things I did to get it to work for me. I downloaded awk95 and then renamed it under the c:\windows directory - same place I put the excel.bat file. For the e.awk script I had to move it from the directory it was unzipped in, into the c:\baan directory. Then I just created the device and it worked.

A good example of how people solve the same problem different ways.

Mark
Reply With Quote
  #5  
Old 9th April 2002, 14:42
Ravenscross's Avatar
Ravenscross Ravenscross is offline
Guru
 
Join Date: Aug 2001
Location: Manchester
Posts: 318
Ravenscross is on a distinguished road
Baan: Lots of different versions - DB: Most of them - OS: Almost all of them
Excel solution at last

Sounds like I shall have to recommend this to a few people if it works, could help sort out loads of problems!

I normally find that creating a device with 999 lines for page length makes life even easier for execl documents (fewer page breaks)
__________________
Christopher Cross

"Grace is there for only one reason, to fall from"
Reply With Quote
  #6  
Old 9th April 2002, 15:59
sitarammani sitarammani is offline
Junior Member
 
Join Date: Jan 2002
Posts: 14
sitarammani is on a distinguished road
A Few checks if disappointed

Please check the following:

a)Awk should be available in the Dos path

From the dos prompt
(some other directory,
not where you copied awk.exe)
execute the command
awk "BEGIN{print \"Hello World\"}"
Did you see a "Hello World" on the DOS Screen?

b) Csv should be associated with some program (pref Excel)
Create a file xyz.csv say "ABC,54321"
From the dos prompt (at where you created
xyz.csv ) execute start xyz.csv

Does this start excel?
is the file xyz.csv loaded in (excel or another program)


c) Match the path stated in excel.bat and location of e.awk.

d) The file created by Baan report should be found:
Can you locate the file (say abc.txt) you created from Baan
in the Baan client directory.

The Baan client directory could be c:\baan
At the dos prompt
execute c:\windows\excel.bat c:\baan\abc.txt
Does this work?

if the baan client directory is under program files
execute c:\windows\excel.bat c:\progra~1\baan\abc.txt

If you are not disappointed in a..d
BW.exe should be able to deliver the goods!

Sitaram Mani
Reply With Quote
  #7  
Old 11th July 2002, 14:24
p.cole's Avatar
p.cole p.cole is offline
Senior Member
 
Join Date: Nov 2001
Location: Portsmouth, UK
Posts: 265
p.cole is on a distinguished road
Baan: Baan IVc4 SP20, ERP LN FP3 - DB: MSSQL 2005 - OS: W2K3
Slightly improved version for UNIX

Here's a shell script which does what the above does but is slightly improved with the following features:

* Joins lines ending with \ together, allowing reports to output big lists for excel
* Handles numbers with a trailing minus sign, moves minus sign to front for excel import
* Trims whitespace from start and end of each field

Phil
Attached Files
File Type: sh rpt2csv.sh (2.1 KB, 215 views)
Reply With Quote
  #8  
Old 11th July 2002, 20:28
NPRao's Avatar
NPRao NPRao is offline
Guru
 
Join Date: Aug 2001
Location: Pacific NW, USA
Posts: 3,028
NPRao will become famous soon enough
Baan: iBaanERP-5.2a(Reger),SSA-ERP-LN-6.1,Infor LN-10.x - DB: Oracle-10g,11g,12c,MS-SQL - OS: HP-UX, Linux, Windows
Hi Phil and Mani,

I have a question for this utility if it can handle the Header kind of Text on the report layouts.
__________________
The art of perfection does not lie in doing extraordinary things but, doing ordinary things extraordinarily well. [-N. Prashanth Rao]
How To Ask Questions The Smart Way,BaaNBoard,NPRao
Reply With Quote
  #9  
Old 12th July 2002, 04:00
sitarammani sitarammani is offline
Junior Member
 
Join Date: Jan 2002
Posts: 14
sitarammani is on a distinguished road
Header text handling

the awk script removes any occurance of comma in any input line.
subsequently it replaces pipe [|] with comma.

sitarammani
Reply With Quote
  #10  
Old 5th August 2002, 23:20
NPRao's Avatar
NPRao NPRao is offline
Guru
 
Join Date: Aug 2001
Location: Pacific NW, USA
Posts: 3,028
NPRao will become famous soon enough
Baan: iBaanERP-5.2a(Reger),SSA-ERP-LN-6.1,Infor LN-10.x - DB: Oracle-10g,11g,12c,MS-SQL - OS: HP-UX, Linux, Windows
Hi Mani,

I guess my question was -

If the output is more than 1 page, would you have the repeating header?

(or)

would you have a single header on the top of the excel sheet and the columns/data printed below ?

I was also wondering if you considered developing it in the BaaN Tools itself, without using, awk, sed. If so, you can achieve, portability across different operating systems.
__________________
The art of perfection does not lie in doing extraordinary things but, doing ordinary things extraordinarily well. [-N. Prashanth Rao]
How To Ask Questions The Smart Way,BaaNBoard,NPRao
Reply With Quote
Sponsored Links
  #11  
Old 6th August 2002, 00:10
~Vamsi's Avatar
~Vamsi ~Vamsi is offline
Guru
 
Join Date: Aug 2001
Location: San Diego CA, USA
Posts: 590
~Vamsi will become famous soon enough
Baan: ~*~ - DB: ~*~ - OS: ~*~
Prashanth,

There is more than one way to skin a cat. As a user you have choices. And choices are good :). If you need a Baan tools solution look at my posting on BaanXL.
__________________
~Vamsi
Vamsi Potluru
Baan XL Yet another tool to format Baan output in Excel from Baan Board
This one just happens to be free :)

Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
  #12  
Old 9th August 2002, 06:00
sitarammani sitarammani is offline
Junior Member
 
Join Date: Jan 2002
Posts: 14
sitarammani is on a distinguished road
Hi Prashant:
The awk script does not distinguish an input line as header or detail.

Given a line the script removes any occurance of comma in any input line. Subsequently it replaces pipe [|] with comma.

I went in for this solution since Baan uses '|' as a separator in all its reports.
?? can Pipe Separator be changed to comma?
!! if changed will spoil the presentation of the report to view on screen with D(isplay) device.

As regards your second suggestion, we can use Vamsi's solution :BaanXL.

I also liked p.cole's rpt2csv.sh. This solution was very useful in breaking the 255Chars barrier.
Sitaram Mani
Reply With Quote
  #13  
Old 9th August 2002, 07:12
NPRao's Avatar
NPRao NPRao is offline
Guru
 
Join Date: Aug 2001
Location: Pacific NW, USA
Posts: 3,028
NPRao will become famous soon enough
Baan: iBaanERP-5.2a(Reger),SSA-ERP-LN-6.1,Infor LN-10.x - DB: Oracle-10g,11g,12c,MS-SQL - OS: HP-UX, Linux, Windows
Lightbulb

Well Mani,

As Vamsi said...
"There is more than one way to skin a cat".
I checked yours, Cole's as well as Vamsi's solutions and few others from Nazdaq etc.

I made the Excel utility and set it up as "Excel" device. The user just chooses it and it doesnt ask any questions (Vamsi had good feature of that, but our users liked less user intervention). I cant publish my solution here (due to copyright etc issues). Hence I was giving you hints so that you can build it or Vamsi can improvise it.

Here is a sample output of what can be done with the BaaN Tools without any other additional awk.exe etc files so that it can be portable across any OS installation.

So something for you have to take the path which is not treaded... as described perfectly in the poem "IF" by Rudyard Kipling or Robert Frost.

It was an interesting experience to get this working... :p

I dont know why those smileys are not working here.. :-(
Attached Files
File Type: doc excel-output.doc (641.5 KB, 334 views)
__________________
The art of perfection does not lie in doing extraordinary things but, doing ordinary things extraordinarily well. [-N. Prashanth Rao]
How To Ask Questions The Smart Way,BaaNBoard,NPRao
Reply With Quote
  #14  
Old 9th August 2002, 09:01
patvdv's Avatar
patvdv patvdv is offline
Board Master
 
Join Date: Aug 2001
Location: Belgium
Posts: 2,167
patvdv is on a distinguished road
Baan: n/a - DB: n/a - OS: AIX, HP-UX, Linux
Smilies are disabled in the CODE & Utilities forum to avoid unwanted smilies when members post code snippets. Voila.
__________________
Regards,

Patrick Van der Veken - Admin & Founder - (c) 2001-2017 baanboard.com/baanforums.com
Reply With Quote
  #15  
Old 30th August 2002, 14:39
markom's Avatar
markom markom is offline
Junior Member
 
Join Date: Jul 2002
Location: Slovenia
Posts: 24
markom is on a distinguished road
Baan: B40c4 - DB: Oracle 8i - OS: Win NT
Report in Excel

I got repotr in excel but it looks like in attachment
Attached Files
File Type: zip dokument.zip (24.1 KB, 124 views)
__________________
Lp Mare
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are Off
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Code: Sample Qkey Script mark_h Code & Utilities 7 9th December 2013 14:33
CODE: BaanXL - Yet another Baan to Excel utility. ~Vamsi Code & Utilities 45 23rd July 2013 03:43
CODE: Simple report conversion for excel richard Code & Utilities 17 12th February 2013 11:56


All times are GMT +2. The time now is 08:32.


©2001-2017 - Baanboard.com - Baanforums.com