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
37%
Installation Wizard into new VRC
39%
Manual into existing VRC
3%
Manual into new VRC
21%
Total votes: 38

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #1  
Old 12th September 2002, 10:49
richard richard is offline
Senior Member
 
Join Date: Oct 2001
Location: France
Posts: 178
richard is on a distinguished road
Baan: triton 3.2/Baan IV/LN - DB: Oracle 9.2.0.6/informix 31 - OS: HP UX 11.23
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.

Code:
#! /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

Code:
#! /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:
Code:
|  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                                                     |
Reply With Quote
  #2  
Old 22nd September 2003, 18:26
richard richard is offline
Senior Member
 
Join Date: Oct 2001
Location: France
Posts: 178
richard is on a distinguished road
Baan: triton 3.2/Baan IV/LN - DB: Oracle 9.2.0.6/informix 31 - OS: HP UX 11.23
Automatic run of excel

After transcoding, excel starts with:
Code:
{
        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:
Code:
  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: ..    |
Reply With Quote
  #3  
Old 22nd September 2003, 18:53
lbencic's Avatar
lbencic lbencic is offline
Guru
 
Join Date: Dec 2001
Location: Lisle, IL, USA
Posts: 1,148
lbencic will become famous soon enough
Baan: 2.2d - LN - DB: most - OS: most
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.
Reply With Quote
  #4  
Old 22nd September 2003, 21:15
NPRao's Avatar
NPRao NPRao is offline
Guru
 
Join Date: Aug 2001
Location: Pacific NW, USA
Posts: 3,032
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
Lisa,
Quote:
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.
__________________
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
  #5  
Old 22nd September 2003, 21:18
NvanBeest's Avatar
NvanBeest NvanBeest is offline
Guru
 
Join Date: May 2003
Location: South Africa
Posts: 521
NvanBeest is on a distinguished road
Baan: BaanIVc4 - DB: Oracle, TBase, SQL Server - OS: AIX, Linux, Window$
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.
__________________
Regards,
Nico
Reply With Quote
  #6  
Old 22nd September 2003, 21:20
NPRao's Avatar
NPRao NPRao is offline
Guru
 
Join Date: Aug 2001
Location: Pacific NW, USA
Posts: 3,032
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
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 ;-)
__________________
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
  #7  
Old 22nd September 2003, 21:25
lbencic's Avatar
lbencic lbencic is offline
Guru
 
Join Date: Dec 2001
Location: Lisle, IL, USA
Posts: 1,148
lbencic will become famous soon enough
Baan: 2.2d - LN - DB: most - OS: most
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.
Reply With Quote
  #8  
Old 22nd September 2003, 21:31
NvanBeest's Avatar
NvanBeest NvanBeest is offline
Guru
 
Join Date: May 2003
Location: South Africa
Posts: 521
NvanBeest is on a distinguished road
Baan: BaanIVc4 - DB: Oracle, TBase, SQL Server - OS: AIX, Linux, Window$
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.
__________________
Regards,
Nico
Reply With Quote
  #9  
Old 22nd September 2003, 21:52
lbencic's Avatar
lbencic lbencic is offline
Guru
 
Join Date: Dec 2001
Location: Lisle, IL, USA
Posts: 1,148
lbencic will become famous soon enough
Baan: 2.2d - LN - DB: most - OS: most
Thanks, Nico - looks like that link provides all the pieces :). The links for XLS format are also there. Very handy site overall.
Reply With Quote
  #10  
Old 22nd September 2003, 22:03
NvanBeest's Avatar
NvanBeest NvanBeest is offline
Guru
 
Join Date: May 2003
Location: South Africa
Posts: 521
NvanBeest is on a distinguished road
Baan: BaanIVc4 - DB: Oracle, TBase, SQL Server - OS: AIX, Linux, Window$
Wink

Credit goes to NPRao :)
__________________
Regards,
Nico
Reply With Quote
  #11  
Old 22nd September 2003, 22:09
~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: ~*~
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.
__________________
~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 22nd September 2003, 22:34
NPRao's Avatar
NPRao NPRao is offline
Guru
 
Join Date: Aug 2001
Location: Pacific NW, USA
Posts: 3,032
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
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.
Attached Images
File Type: jpg xls-outputs.jpg (105.5 KB, 531 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
Sponsored Links
  #13  
Old 22nd September 2003, 22:34
NPRao's Avatar
NPRao NPRao is offline
Guru
 
Join Date: Aug 2001
Location: Pacific NW, USA
Posts: 3,032
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
XML-IE

XML-IE
Attached Images
File Type: jpg xml-ie.jpg (72.1 KB, 449 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 22nd September 2003, 22:34
lbencic's Avatar
lbencic lbencic is offline
Guru
 
Join Date: Dec 2001
Location: Lisle, IL, USA
Posts: 1,148
lbencic will become famous soon enough
Baan: 2.2d - LN - DB: most - OS: most
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.
Reply With Quote
  #15  
Old 22nd September 2003, 22:47
~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: ~*~
Quote:
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.
__________________
~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
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
Page x of y: Kevin Brock's solution. ~Vamsi Code & Utilities 4 5th January 2006 12:08
Simple tax (VAT) report tapzhou Tools Development 1 18th June 2004 14:02
Code for a simple Maintain Session learner Tools Development 8 3rd September 2003 18:47


All times are GMT +2. The time now is 04:16.


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