1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

MFGPRO to excel sheets....

Discussion in 'QAD's MFG/PRO' started by aasif163, Sep 25, 2007.

  1. aasif163

    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
     
  2.  
  3. mpowell_esq

    mpowell_esq New Member

    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.
     
  4. mpowell_esq

    mpowell_esq New Member

    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.
     
  5. aasif163

    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.....
     
  6. aasif163

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

    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.
     
  8. Lexiconmike

    Lexiconmike New Member

    Fraser Stream Integration www.fraserstream.com is designed specificly for this, it not only transforms text reports to Excel but also can convert them to datasets PDF, XML, HTML or word.

    LEX
     
  9. aasif163

    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.....
     
  10. willy

    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
     
  11. lord_icon

    lord_icon New 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
     
  12. rbaggetta

    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.
     
  13. shoam.boinus

    shoam.boinus New Member

    32printer is good but NOT support double byte reports conversion.

    Check MFG2XLS at www.erp2xls.com , it support 27 languages standard MFG/PRO reports convert to Excel with one click.
    Online flash demo also available at http://www.erp2xls.com/help/userguide/

    Best Rgds.
    Shoam Boinus
     
  14. tburtovoy

    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.
     
  15. sphipp

    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 (progress):
    1.  
    2. . /usr/bin/qad/prn2.sh
    3. f_stdin2file "tmp" "xls" "$PRN2DIR/sh/prn2xls.p"
    4.  
    /usr/bin/qad/prn2pdf.sh:
    Code (progress):
    1.  
    2. . /usr/bin/qad/prn2.sh
    3. f_stdin2file "tmp" "pdf" "$PRN2DIR/sh/prn2pdf.p"
    4.  
    /usr/bin/qad/prn2htm.sh:
    Code (progress):
    1.  
    2. . /usr/bin/qad/prn2.sh
    3. f_stdin2file "tmp" "htm" "$PRN2DIR/sh/prn2htm.p"
    4.  
    /usr/bin/qad/prn2dummy.sh:
    Code (progress):
    1.  
    2. . /usr/bin/qad/prn2.sh
    3. f_stdin2file "tmp" "txt" "$PRN2DIR/sh/prn2dummy.p"
    4.  

    /usr/bin/qad/prn2.sh:

    Code (progress):
    1.  
    2. PRN2DIR=/home/prn2
    3. PRN2LOGDIR=$PRN2DIR/log
    4. SCRIPTS=/usr/bin/qad
    5. PROGEXE=$DLC/bin/mpro
    6. PROGPF=" "
    7. PROGSTARTPROG="-p $PRN2DIR/sh/run.p"
    8.  
    9. f_prorun ()
    10. {
    11.  
    12. cd $SCRIPTS/work
    13. $PROGEXE $1 $PROGPF $PROGSTARTPROG -param "$2,$3,$4,$5,$6,$7,$8" 1>/dev/null 2>&1
    14.  
    15. }
    16.  
    17. f_batchrun ()
    18. {
    19.  
    20.  f_prorun "-b" "$1" "$2" "$3" "$4" "$5" "$6" "$7"
    21.  
    22. }
    23.  
    24. f_stdin2file ()
    25. {
    26.  
    27. TMPROOT=`f_gettmp `
    28. cat > $TMPROOT.$1 ; f_batchrun "$3" "$TMPROOT.$1" "$TMPROOT.$2" ; rm $TMPROOT.$1
    29.  
    30. }
    31.  
    32. f_getcount ()
    33. {
    34.  
    35. COUNTFILE="$PRN2DIR/count/$1.count"
    36. if [ ! -e $COUNTFILE ]
    37. then
    38.   echo 0 > $COUNTFILE
    39. fi
    40. TMPCOUNT=`cat $COUNTFILE`
    41. TMPCOUNT=`expr $TMPCOUNT + 1`
    42. echo $TMPCOUNT > $COUNTFILE
    43. echo $TMPCOUNT
    44.  
    45. }
    46.  
    47. f_gettmp ()
    48. {
    49.  
    50. echo $PRN2DIR/tmp/tempfile_` whoami `_`f_getcount gettmp `
    51.  
    52. }
    53.  
    54. export PRN2DIR PRN2LOGDIR SCRIPTS
    55.  
    56.  

    /home/prn2/sh/run.p:

    Code (progress):
    1.  
    2. define variable cProgram as char no-undo.
    3. assign cProgram = search(entry (1,session:parameter)).
    4. if cProgram <> ? then run value (cProgram).
    5. QUIT.
    6.  
    /home/prn2/sh/prn2.i:

    Code (progress):
    1.  
    2. def var cInputFilename as char no-undo.
    3. def var cOutputFilename as char no-undo.
    4. def var cline as char no-undo.
    5. def var clog as char no-undo.
    6.  
    7. def temp-table ttline no-undo
    8.   field tcount as int
    9.   field tline as char
    10.   index idx1 is primary tcount.
    11. def var icount as int no-undo.
    12.  
    13. def stream sIn.
    14. def stream sOut.
    15.  
    16. run ipGetFilenames.
    17. procedure ipGetFilenames:
    18.   if num-entries (session:parameter) >= 2 then
    19.     cInputFilename = entry (2,session:parameter).
    20.   if num-entries (session:parameter) >= 3 then
    21.     cOutputFilename = entry (3,session:parameter).
    22.   if search (cInputFilename) = ? then return.
    23. run ip_import_prn_file.
    24. end procedure.
    25.  
    26. procedure ip_import_prn_file:
    27.   input stream sIn from value (cInputFilename).
    28.   repeat:
    29.     cline = "".
    30.     import stream sIn unformatted cline.
    31.     icount = icount + 1.
    32.     create ttline.
    33.     ttline.tline = cline.
    34.   end.
    35.   output stream sIn close.
    36. end procedure.
    37.  

    /home/prn2/sh/prn2xls.p:
    Code (progress):
    1.  
    2. {/home/prn2/sh/prn2.i}
    3. run ipProduceXlsFile.
    4.  

    /home/prn2/sh/prn2pdf.p:
    Code (progress):
    1.  
    2. {/home/prn2/sh/prn2.i}
    3. run ipProducePDFFile.
    4.  
    /home/prn2/sh/prn2dummy.p:
    Code (progress):
    1.  
    2. {/home/prn2/sh/prn2.i}
    3. run ipProduceDummyFile.
    4.  
    5. procedure ipProduceDummyFile:
    6.   output stream sOut to value (cOutputFilename).
    7.   for each ttline use-index idx1:
    8.     put stream sOut unformatted "NOW:" ttline.tline skip.
    9.   end.
    10.   input stream sOut close.
    11. end procedure.
    12.  
    13.  
     

Share This Page