This script converts a standard report in a excel-readable csv file. Standard = header (1 up to 3 lines) and details with | field separator. Specially the querys are standard.

Sorry, the comments are french.

#! /bin/ksh
# trexcel
#
# transformation d'un fichier edition baan en fichier excel
#
if [ $# -ge 2 ]
then
echo "le nom de fichier ne doit pas comporter de blanc"
echo "le fichier sera sous forme brute et non transforme excel"
echo "taper transmit\c"
read reponse
fi
awk -f /administration/awk-supent $1 > $HOME/temp
# suppression des blancs apres | (cadrage excel correct)
cat $HOME/temp | sed '1,$s/| /|/g' | sed '1,$s/;/\./g' | sed '1,$s/|/;/g' > $1
unix2dos $1 $1 2>/dev/null
rm $HOME/temp
chmod 666 $1

#! /bin/ksh
# awk-supent
#
# supression de toutes les lignes non significatives d'une edition
#
#
BEGIN {
ligne1="";
ligne2="";
ligne3="";
titre=0;
entete=0;
}
# stockage des entetes
{
pipes=index($0,"|");
if (pipes>0&&entete==0) {
if (titre==0) {
ligne1=$0; titre++; print $0}
else
{if (titre==1)
{ ligne2=$0; titre++; print $0}
else
{if (titre==2)
{ ligne3=$0; titre++; print $0}
}
}
}
# test une entete trouvee
if (pipes==0&&titre>0)
{entete=1}
#
# impression des lignes differentes des entetes
#
if (pipes>0&&$0!=ligne1&&$0!=ligne2&&$0!=ligne3)
{print $0}
}


device:
| Maintain Device Data |
|------------------------------------------------------------------------------|
| Device : E |
| Description : Excel (extraction vers) (div RIM) |
| Device Type : Rewrite file |
| Locale : |
|--Printer -----------------------------------------------------------|
| Driver : |
| Device Queue : |
| Paper Type : |
| Left Margin : Form Feed : |
|--File -----------------------------------------------------------|
| Driver : |
| Shell Command : |
| 4GL Program : ttstpconv |
| Argument : ASCII\/administration/trexcel %s |
| Path : |
| Change allowed : Yes |
| Page Length : 66 |

After transcoding, excel starts with:

{
tmp.file = creat.tmp.file$(bse.tmp.dir$())
wait.and.activate("ttstpconv",argv$(1),tmp.file,argv$(3),argv$(4))
| parametre 1 = fichier tmp, 2 = fichier sortie, 3 = argument (ASCII), 4 = wt
ret = shell("/administration/trexcelv " & tmp.file,SHELL_NO_OUTPUT)
if ret <> 0 then
abort()
else
tmp.local = "\temp\" & strip$(logname$) &
str$(time.num()) & ".csv"
ret = server2client(tmp.file, tmp.local, 0)
if ret < 0 then | suppose citrix
tmp.local = "M:\WINNT\temp\" &
strip$(logname$) & str$(time.num()) & ".csv"
ret = server2client(tmp.file, tmp.local, 0)
endif
ret = app_start("excel.exe " & tmp.local, "", "", "", "")
endif
ret = seq.unlink(tmp.file) | suppression du fichier
}

Device ED:

Gestion données device |
|------------------------------------------------------------------------------|
| Device : ED |
| Description : Excel direct (dév. RIM) |
| Type device : Réécrire fichier |
| Locale : |
|--Imprimante -----------------------------------------------------------|
| Driver : |
| File device : |
| Type papier : |
| Marge gauche : Charger feuille : |
|--Fichier -----------------------------------------------------------|
| Driver : |
| Commande shell : |
| Programme 4GL : otccomconved |
| Argument : ASCII |
| Répertoire : Excel |
| Modif. autorisée : Non |
| Longueur page : 66 |
| Choix: .. |

Hi & Thanks for posting this :)

I am looking into conversion programs for Excel - .csv does seem to be the way to go. There are several versions out there & on these boards.

One thing I have found is that if I have leading 0's in a string field, such as Part Number: 007884
That when this is sent to excel using csv and the app_start, or other conversions, that the leading 0's are removed.

Before I try this setup too, can you tell me is that solved? Has anyone else even noticed or solved this? I have tried formatting the strings with single / double quotes, many formats, but I have not yet been successful.

Lisa,
One thing I have found is that if I have leading 0's in a string field, such as Part Number: 007884
I tried that for sometime and I gave up. The alternative was to make a XLS interface, it took us sometime and we did it.

I think Vamsi posted this link on the board -

http://www.wotsit.org/search.asp?s=database

Our XLS version works fine and we just found a new problem with this interface is that we cannot have more than 255 characters in a single text box and the information they gave in that link was not for the newest Excel-5.0 version.

But current our users are happy and this wasnt the highest priority that we looked to solve.

I seen many solutions on the board here but I would still recommend a BaaN solution which makes it platform independent than using shell scripting, awk or perl.

So good luck and have fun with the string processing.

Although I haven't tried it, maybe you could ensure that the part number is exported as " 007884", thus with a space as the first character of a string field? Could just fool Excel enough to keep the leading zero's.

Nico,

tried that didnt work... well to make a generic solutions you cant fool the applications, as the end users use the excel sheets columns for summations, formulas, sorting etc....

sometimes work-arounds just dont work ;-)

Yea, Nico, tried EVERY possible thing I could think of on the quotes. Also, if the first entry in the column is string, the rest still get stripped below it, so making a dummy heading column didn't work.

I did NOT try ~Vamsi's yet, thanks, I will. For my own interest more than anything at this point, the csv was driving me nuts. I don't think the text limitation is too big a drawback, at least not as much as loosing the leading 0's.

Ever looked at the SYLK (http://www.wotsit.org/search.asp?page=2&s=database) format? That's the format used by DocumentStyler (http://www.macroscope.co.uk/solutions/documentStyler.asp) from MacroScope. I've used this at a customer's site, and it works quite well, except that it has a limit to the width of the exported file, namely the same as the maximum width of a Baan report.

Thanks, Nico - looks like that link provides all the pieces :). The links for XLS format are also there. Very handy site overall.

Credit goes to NPRao :)

Guys,

Don't sweat out the XLS format. Too cumbersome. Use the HTML format that Excel uses. Pretty easy and straight forward. Create an excel sheet and save as HTML to see how Excel saves into HTML. Will check with Praveen if he can post changes to BaanXL which solves the issue of lost leading zeroes. Also it adds the ability to add formatting like bold, italic and color.

Good idea, Vamsi, but when you compress columns in the excel sheet and save them as the HTML file then you loose the title or column information. Any clues how to save the data correctly ?

Alternatively, using the new Device data options of creating - Intermediate File in XML format, we can generate the XML file then install some XSL templates on the client end and then we can view it better in IE.

XML-IE

HTML - Excellent idea, as usual. Thanks all then. More to sort through than I have time for (read: no current project) , but I will post what I find.

NP: just saw your post, will check into all. One of my original thought was to play with templates, so I think this will also do the trick.

Good idea, Vamsi, but when you compress columns in the excel sheet and save them as the HTML file then you loose the title or column information. Any clues how to save the data correctly ?

Explain please.

A little explanation is in order. I was talking about generating custom HTML that is suitable for Excel. This is possible with minimum changes to BaanXL. Actually it reduces the number of lines of code.

For those of you who want to see the format of the HTML I am talking about, open a new Excel document and put in a few pieces of information. Save as HTML. Open the resulting file in a text editor. Voila!

Save yourselves from the trouble of trying to use Baan's own HTML.

A simple solution for the leading zero's problem is to surround the field with

="<part_number>"

The =" will make Excel read the colum as text.

Dear All,
Does such utility exist for Windows platform?

Regards.
DSR