Results 1 to 8 of 8

Thread: How to convert as400 data fl to .xls file

  1. #1
    Code400 Newbie
    Join Date
    Feb 2011
    Location
    Millstone, NJ
    Posts
    2
    Rep Power
    0

    Question How to convert as400 data fl to .xls file

    Hi Everyone,
    I have been sucessful converting both flat files(sys/36 files) and external described files to .csv files and then sending them to various sites via email or FTP using commands in a CL program. Some sites require "true" .xls files. I know that there is software that can be purchased to do this, my client already has it. However, I would like to know if I can do it directly on the 400. Here are the commands I use to convert an external defined file to .csv

    1.) Copy data file into a source file to add delimiters.
    CPYTOIMPF FROMFILE(QS36F/WBDATA) TOFILE(QS36F/WBSRC) MBROPT(*REPLACE) RCDDLM(*EOR) DTAFMT(*DLM) STRDLM(*DBLQUOTE) FLDDLM(',')
    2.) Then copy the source file which now contains data and delimiters into a stream file on the IFS
    CPYTOSTMF FROMMBR('/QSYS.LIB/QS36F.LIB/WBSRC.FILE/WBSRC.MBR') TOSTMF('/FLTRNSFR/WBSRC.CSV') STMFOPT(*REPLACE) DBFCCSID(*FILE) STMFCODPAG(00819) ENDLINFMT(*CRLF)

    This works fine. To make it convert to a .xls file is it simply a matter of changing the file extension and stmfcodpag? Is so, what is the code? Or else how is it done?

    Thanks for your help.
    ldb

  2. # 666
    Circuit advertisement
    Join Date
    Aug 1965
    Location
    Yakutsk, Russia
    Posts
    1,000,000
     

  3. #2
    Analyst GLS400's Avatar
    Join Date
    May 2006
    Location
    connecticut
    Posts
    1,903
    Rep Power
    5801

    Re: How to convert as400 data fl to .xls file

    Hi ldb:
    To make it convert to a .xls file is it simply a matter of changing the file extension and stmfcodpag? Is so, what is the code?
    Not quite that simple....google "HSSF" and "POI".
    (HSSF stands for horrible spread sheet function.)

    Best of Luck
    GLS
    The problem with quotes on the internet is that it is hard to verify their authenticity.....Abraham Lincoln

  4. #3
    Driver of cars, eater of food jamief's Avatar
    Join Date
    Jan 2004
    Location
    Belvidere, IL - United States of America
    Age
    49
    Posts
    9,416
    Rep Power
    12606

    Re: How to convert as400 data fl to .xls file

    This is an example of writing XML directly to the IFS...Will open with excel
    http://www.code400.com/forum/showthr...-read-as-EXCEL

    jamie
    All my answers were extracted from the "Big Dummy's Guide to the As400"
    and I take no responsibility for any of them.

    www.code400.com

  5. #4
    Code400 Newbie
    Join Date
    Feb 2011
    Location
    Millstone, NJ
    Posts
    2
    Rep Power
    0

    Re: How to convert as400 data fl to .xls file

    I am sorry I must be missing something.... My current .csv files that I create can be opened by excel, everthing appears in columns and rows as it should be. However, for some reason (I am not sure why), certain sites request an .xls file. I was looking to take the data from my externally defined native 400 file convert it to a file with an .xls ending, and whatever else it needed to be called an excel file. Which by the way, I have no idea what else it needs to be called an excel file. Do you? Also, I would need to do this in batch, not interactive, as I am doing now for my other file conversions. I am not sure what XML has to do with it. Please keep in mind that I am soley an RPG coder and that may be why I'm not getting it. :-)

  6. #5
    Driver of cars, eater of food jamief's Avatar
    Join Date
    Jan 2004
    Location
    Belvidere, IL - United States of America
    Age
    49
    Posts
    9,416
    Rep Power
    12606

    Re: How to convert as400 data fl to .xls file

    Microsoft Excel auto opens XML as an excel spread sheet...
    All my answers were extracted from the "Big Dummy's Guide to the As400"
    and I take no responsibility for any of them.

    www.code400.com

  7. #6
    Analyst BenThurley's Avatar
    Join Date
    Dec 2006
    Location
    Southampton (UK)
    Age
    32
    Posts
    818
    Rep Power
    2529

    Re: How to convert as400 data fl to .xls file

    Excel opens CSV files anyway doesn't it?

    If it was just a one off then I would use the "Data transfer from iSeries server" utility that comes with the client access/navigator package.

    It sounds like you want something to run in batch though. In this case you'll have to roll your sleeves up. I would have a read of this article as a start.
    http://systeminetwork.com/node/60954
    Ben

  8. #7
    Code400 Newbie
    Join Date
    Mar 2010
    Location
    Kalamazoo, MI USA
    Posts
    48
    Rep Power
    443

    Re: How to convert as400 data fl to .xls file

    Easy400.net has a good open source tool HSSFCGI, the TABLEXLS command works well.

  9. #8
    Driver of cars, eater of food jamief's Avatar
    Join Date
    Jan 2004
    Location
    Belvidere, IL - United States of America
    Age
    49
    Posts
    9,416
    Rep Power
    12606

    Re: How to convert as400 data fl to .xls file

    @ Ben - csv does open automagically, but with XML you can create headings, font changes, Tabs ..........
    I should have been more specific.

    jamie
    All my answers were extracted from the "Big Dummy's Guide to the As400"
    and I take no responsibility for any of them.

    www.code400.com

  10. # 666
    Circuit advertisement
    Join Date
    Aug 1965
    Location
    Yakutsk, Russia
    Age
    21
    Posts
    1,000,000
     

Facebook Comments


Similar Threads

  1. How to convert a AS400 spool file (report) to a formatted excel sheet?
    By manty in forum PC programming as it relates to Iseries
    Replies: 6
    Last Post: April 13th, 2010, 11:36 AM
  2. Replies: 14
    Last Post: November 17th, 2009, 09:03 AM
  3. convert a PF in AS400 to Access database (.MDB).
    By minishoppe in forum PC programming as it relates to Iseries
    Replies: 7
    Last Post: December 13th, 2007, 02:34 PM
  4. Reg : convert character data to hex
    By kmkmagesh in forum RPG/RPGLE
    Replies: 1
    Last Post: June 29th, 2006, 07:28 AM
  5. Replies: 1
    Last Post: April 27th, 2005, 06:04 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •