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.

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

I'll try a C program...

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

Mark

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

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)

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

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

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 awk script removes any occurance of comma in any input line.
subsequently it replaces pipe [|] with comma.

sitarammani

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.

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 (http://www.baanboard.com/baanboard/showthread.php?s=&threadid=5880).

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

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.. :-(

Smilies are disabled in the CODE & Utilities forum to avoid unwanted smilies when members post code snippets. Voila.

I got repotr in excel but it looks like in attachment

Have you made sure the .csv extension is associated with Excel and that the default delimiter is a ","? I know on my system when I double click a .csv file it pops up in excel and formats it. From your display it looks like the formatting piece did not take place, it seems like it was something in the system setup. I can not remember where you set this up, hopefully someone else will know this.

I also deleted the other post - the one with the .doc attachment. My system really choked on it.

Good Luck!

Mark

MyComputer
Tools
Folder Options
FileTypes

What I was thinking was that his Excel needed to know how to process the comma delimited file. Not really the file association. I was thinking that maybe there was a system parameter(or maybe excel) that needed to be set so it new it was a comma delimited file. It seems to me I read something somewhere that showed you how to set a default delimiter for the system. Then when you saved a file as .csv it saved it with that delimiter and not the "," delimiter. I was thinking his maybe set to something else. I wish I could find that post. Hope I am explaining it better.

Mark

It was under the control panel on regional settings and is called the list separator. When I changed it to |, my CSV files opened like markoms.
See attached. Where you see the pipe it should be a comma - under list separator.

Mark

You are right Mark!!