MFGPRO to excel sheets....

aasif163

New Member
Hi ppl ,
While reteiving data from MFGPRO to excel sheets , the data are not getting aligned properly in excel sheets between the cell. Lots of time spent in these sheets to align and then only we can analyse the data.... is dere any other way of retrieving data into excel so that al feilds are perfectly aligned in excel?? Or should i go for VB as front end to retieve data from MFGPRO and show charts for various values ....is this possible???
experts plzzz gimme a suggestion as i am doing a project on this..... thankuuu
 
DO NOT go for a VB front end for accessing data from MFG.
The Progress MFG product is equal. Progress is BETTER because it has a database encapsulated. Where VB would require ODBC and drivers.
 
You are simply not outputting the data appropriately.
Accessing the data is simple, just referencing tables and fields (rows and colums).
Output data in a stream, to an Excel file (filename.xls). DO NOT add formatting / manipulate the data. Just use Progress to output required data to a file, which will FORCE Excel to apply the formatting needed. All you are interested in is the data, why do you need to add a third party and use VB? The database is Progress, so U have the 4GL - USE IT.
 

aasif163

New Member
Thanks a lotttttttttttttttt ....i will try the things wich u hav told and in case if i have any doubt , i ll come back to u.....
 

aasif163

New Member
As told by u , there is no need to get VB into picture ..... so how do i proceed??? ITs goin to be MFGPRO --->PROGRESS--->CSV format ---> EXCEL SHEET.... is this rite??? will i get all data in proper excel format without raw data??? there wont be any need to align all cells if i do like this??? n can u propose any study material for PROGRESS 4GL online.....i am very thankful to you.... plzzz replyyyyyy
 

sphipp

Member
It is a shame that MFG/Pro doesn't have a standard option to produce a CSV file.

If you want to pull data out of MFG/Pro reports and convert them to Excel spreadsheets, then you really need a program to extract all the information from the report file then convert it and put it into Excel. You could do that in VB, but Progress 4GL does it just as easily.

If you are writing customised reports, then you could write a report that allows output as either a standard MFG/Pro report or output to a CSV or Excel File.

What we do for our new reports is to use a report include file that has everything pre-defined. We write our new report using the include file and can generate a CSV file. If we want this as an Excel file, we write out extra information to an ini file in a shared directory. Then we run a program from Windows to transfer this to an Excel Spreadsheet. That program reads any ini files in the shared directory, gets the CSV filename, target Excel Spreadsheet filename and a file containing any formatting information, copies the CSV file to a temporary location, c:\progress\excel.csv, then opens an Excel Program with an autorun macro that opens c:\progress\excel.csv, saves it as c:\progress\excel.xls, closes Excel, then connects to c:\progress\excel.xls and applies the formatting to the spreadsheet.

So we:
1. Generate the CSV File in Unix Character MFG/Pro.
2. Copy the CSV file to another location and call it something else (//desktop2/excel/xls00001.csv)
3. Generate an ini file (//desktop2/excel/xls00001.ini) that contains the name of the CSV file, name of the target Excel Spreadsheet (c:/progress/myexcel.xls) and name of a format file (//desktop2/excel/xls00001.d)
4. Manually run a program from Windows (csv2xls.p) which does the following:
5. Reads p:\excel and searches for any .ini files.
6. Reads p:\excel\xls00001.ini and pulls out the information.
7. Copy p:\excel\xls00001.csv to c:\progress\excel.csv.
8. Opens c:\progress\csv2xls.xls to import c:\progress\excel.csv, create c:\progress\excel.xls and exit.
9. Copies c:\progress\excel.xls to c:\progress\myexcel.xls and deletes c:\progress\excel.xls.
10. Opens p:\excel\xls00001.d and imports the formatting information into a temp-table.
11. Opens c:\progress\myexcel.xls and applies the formatting information.
12. Zips up p:\excel\xls00001.* into p:\excel\zip\xls00001.zip and deletes p:\excel\xls00001.*.

It seems to work well at the moment, with people happy with the result.

We also use a program (csv2xls.p) that takes a csv filename and creates the .ini/.d files required to convert that into an Excel spreadsheet. We use this where it is impractical to convert a program that generates a CSV file into the above format. The program calls csv2xls.p once it has created a CSV file.
 

aasif163

New Member
thanks for ur reply.....rite now the present state wat they are using in this company is "output wat they are getting in MFGPRO is getting converted into .PRN file using Ftp client software..... but this PRN file consists of raw data where data is not aligned.....seperate time is spent to align this data.... my work is to eliminate this problem.... I gotto get dta in excel sheet in a neat format!!!! how can i achieve this ???? plz gimme some suggestion.... @sphipp can i get ur e-mail id so that it ll be easy for me to clarify my doubts...??? Am very new to this ERP application.....
 

willy

New Member
http://www.32soft.com/site/modules/xt_conteudo/?id=1

this guys have software , u don't require to do ftp , software name is printer server ,
This software convert your document in desired format in excel , notepad , pdf etc,

there may be other tools available ,

ftp the data and in excel you have to use DATA->TExt to Coloums -> delimited and select the require delimeter
 

lord_icon

Member
How dumb are U guys?

That is NOT a rhetorical question. Why use ANY 3rd party software?
MFG / Eb x is written in Progress 4GL. Get your in-house Progress guys to develop /design and construct 4GL that is bespoke to your shops needs.
Progress is an advanced business application use it!!! 3rd parties are NOT needed
 

rbaggetta

New Member
We use 32soft's progam, its simple easy to install and anyone can use it..pretty much all of the standard reports in MFG/PRO will go to excel in the proper cells.
 

tburtovoy

New Member
"...3rd parties not needed"

..true, but some people just dont want to deal with learning Progress or MFG/Pro to such a high technical level. Try "Transform" , a stream-based converter to XL, email, fax and barcode, etc. www.sprolinux.com. Better customization of output than simple CSV. Written in Progress. Price is reasonable compared to products like Optio.
 

sphipp

Member
OK, resurrecting a dead thread, but here is one solution that can be used to generate Excel, HTML, PDF or whatever kind of file you want from Unix Character MFG/PRO. This is a long post and I have not attached any programs or scripts, but you should be able to see the method.

This is quite a long post. I have copied and pasted program chunks and parts of scripts, so it might have some typos in, but hopefully it should work. If there are typos/bugs in the scripts/programs then please let me know.


What you need are several shell scripts that allow data to be piped in, then you can set up MFG/PRO Printers that use these scripts.

So, you could have the following scripts:
/usr/bin/qad/prn2csv.sh
/usr/bin/qad/prn2html.sh
/usr/bin/qad/prn2xls.sh
/usr/bin/qad/prn2pdf.sh
/usr/bin/qad/prn2dummy.sh
You set up new MFG Printers with the command line "cat | /usr/bin/qad/prn2xls.sh"

Each script uses a common script in the same way as Progress uses include files to make certain functions available in the script. The scripts then call f_stdin2file and tell it the extension of the temporary file, extension of the output file and the program to be used to generate the file.

The programs used to generate the output are of the form:
prn2csv.p
prn2xls.p
prn2html.p
prn2pdf.p
prn2dummy.p
and will have to be written in-house. However, they share common functionality which is defined in an include file.

The include file prn2.i extracts the Input Filename and Output Filename from the SESSION:pARAMETER, check to see if the input file exists (it is created by the calling script so it should always exist) and then reads each line at a time, adding each line to a temp-table.

Each program then processes the temp-table accordingly.

You can easily set up as many different scripts as you want, depending on what kind of output you need.

The scripts themselves are of the form:

/usr/bin/qad/prn2xls.sh:
Code:
. /usr/bin/qad/prn2.sh
f_stdin2file "tmp" "xls" "$PRN2DIR/sh/prn2xls.p"

/usr/bin/qad/prn2pdf.sh:
Code:
. /usr/bin/qad/prn2.sh
f_stdin2file "tmp" "pdf" "$PRN2DIR/sh/prn2pdf.p"

/usr/bin/qad/prn2htm.sh:
Code:
. /usr/bin/qad/prn2.sh
f_stdin2file "tmp" "htm" "$PRN2DIR/sh/prn2htm.p"

/usr/bin/qad/prn2dummy.sh:
Code:
. /usr/bin/qad/prn2.sh
f_stdin2file "tmp" "txt" "$PRN2DIR/sh/prn2dummy.p"


/usr/bin/qad/prn2.sh:

Code:
PRN2DIR=/home/prn2
PRN2LOGDIR=$PRN2DIR/log
SCRIPTS=/usr/bin/qad
PROGEXE=$DLC/bin/mpro
PROGPF=" "
PROGSTARTPROG="-p $PRN2DIR/sh/run.p"
 
f_prorun ()
{
 
cd $SCRIPTS/work
$PROGEXE $1 $PROGPF $PROGSTARTPROG -param "$2,$3,$4,$5,$6,$7,$8" 1>/dev/null 2>&1
 
}
 
f_batchrun ()
{
 
 f_prorun "-b" "$1" "$2" "$3" "$4" "$5" "$6" "$7"
 
}
 
f_stdin2file ()
{
 
TMPROOT=`f_gettmp `
cat > $TMPROOT.$1 ; f_batchrun "$3" "$TMPROOT.$1" "$TMPROOT.$2" ; rm $TMPROOT.$1
 
}
 
f_getcount ()
{
 
COUNTFILE="$PRN2DIR/count/$1.count"
if [ ! -e $COUNTFILE ]
then
  echo 0 > $COUNTFILE
fi
TMPCOUNT=`cat $COUNTFILE`
TMPCOUNT=`expr $TMPCOUNT + 1`
echo $TMPCOUNT > $COUNTFILE
echo $TMPCOUNT
 
}
 
f_gettmp ()
{
 
echo $PRN2DIR/tmp/tempfile_` whoami `_`f_getcount gettmp `
 
}
 
export PRN2DIR PRN2LOGDIR SCRIPTS


/home/prn2/sh/run.p:

Code:
define variable cProgram as char no-undo.
assign cProgram = search(entry (1,session:parameter)).
if cProgram <> ? then run value (cProgram).
QUIT.

/home/prn2/sh/prn2.i:

Code:
def var cInputFilename as char no-undo.
def var cOutputFilename as char no-undo.
def var cline as char no-undo.
def var clog as char no-undo.
 
def temp-table ttline no-undo
  field tcount as int
  field tline as char
  index idx1 is primary tcount.
def var icount as int no-undo.
 
def stream sIn.
def stream sOut.
 
run ipGetFilenames.
procedure ipGetFilenames:
  if num-entries (session:parameter) >= 2 then
    cInputFilename = entry (2,session:parameter).
  if num-entries (session:parameter) >= 3 then
    cOutputFilename = entry (3,session:parameter).
  if search (cInputFilename) = ? then return.
run ip_import_prn_file.
end procedure.
 
procedure ip_import_prn_file:
  input stream sIn from value (cInputFilename).
  repeat:
    cline = "".
    import stream sIn unformatted cline.
    icount = icount + 1.
    create ttline.
    ttline.tline = cline.
  end.
  output stream sIn close.
end procedure.


/home/prn2/sh/prn2xls.p:
Code:
{/home/prn2/sh/prn2.i}
run ipProduceXlsFile.


/home/prn2/sh/prn2pdf.p:
Code:
{/home/prn2/sh/prn2.i}
run ipProducePDFFile.

/home/prn2/sh/prn2dummy.p:
Code:
{/home/prn2/sh/prn2.i}
run ipProduceDummyFile.
 
procedure ipProduceDummyFile:
  output stream sOut to value (cOutputFilename).
  for each ttline use-index idx1:
    put stream sOut unformatted "NOW:" ttline.tline skip.
  end.
  input stream sOut close.
end procedure.
 
Top