ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

How to convert as400 data fl to .xls file

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

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

    Comment


    • #3
      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


      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

      Comment


      • #4
        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. :-)

        Comment


        • #5
          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

          Comment


          • #6
            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.
            Ben

            Comment


            • #7
              Re: How to convert as400 data fl to .xls file

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

              Comment


              • #8
                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

                Comment

                Working...
                X