-   Code & Utilities (
-   -   CODE: Simple report conversion for excel (

richard 12th September 2002 09:49

Simple report conversion for excel
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 ]
        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
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        {
# stockage des entetes
if (pipes>0&&entete==0) {
        if (titre==0) {
                ligne1=$0; titre++; print $0}
        {if (titre==1)
                { ligne2=$0; titre++; print $0}
        {if (titre==2)
                { ligne3=$0; titre++; print $0}
# test une entete trouvee
if (pipes==0&&titre>0)
# impression des lignes differentes des entetes
if (pipes>0&&$0!=ligne1&&$0!=ligne2&&$0!=ligne3)
        {print $0}


|  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                                                    |

richard 22nd September 2003 17:26

Automatic run of excel
After transcoding, excel starts with:

        tmp.file = creat.tmp.file$(bse.tmp.dir$())
| 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
                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)
                ret = app_start("excel.exe " & tmp.local, "", "", "", "")
        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: ..    |

lbencic 22nd September 2003 17:53

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.

NPRao 22nd September 2003 20:15


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 -

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.

NvanBeest 22nd September 2003 20:18

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.

NPRao 22nd September 2003 20:20


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 ;-)

lbencic 22nd September 2003 20:25

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.

NvanBeest 22nd September 2003 20:31

Ever looked at the SYLK format? That's the format used by DocumentStyler 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.

lbencic 22nd September 2003 20:52

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

NvanBeest 22nd September 2003 21:03

Credit goes to NPRao :)

~Vamsi 22nd September 2003 21:09


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.

NPRao 22nd September 2003 21:34

1 Attachment(s)
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.

NPRao 22nd September 2003 21:34

1 Attachment(s)

lbencic 22nd September 2003 21:34

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.

~Vamsi 22nd September 2003 21:47


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.

All times are GMT +2. The time now is 20:29.

©2001-2018 - -