Reading a spreadsheet

Help
2002-05-15
2002-07-02
  • David M. Kellerman

    Is there anyway to read a Microsoft Excel spreadsheet using Regina Rexx?

     
    • Florian Grosse-Coosmann

      Hi,

      Not directly with plain Regina. You may use Object Rexx or just a package for Regina to do some OLE. May run or may not.

      A running solution is to save an Excel sheet as a CSV file. You
      can use delimiters as you like. The file may be created by a call to Excel. I'm not a guru for this.

      Reading a CSV is pretty simple. You can do something like
      line = linein( MyCSV )
      itemno = 0
      cline.0 = itemno
      do forever
         parse var line item ',' line
         if line = '' & item = '' then leave
         itemno = itemno + 1
         cline.itemno = item
         cline.0 = itemno
         end

      This is untested code. Don't blame me for errors.

      Cheers, Florian

       
    • Robert A "Bob" Cruz

      I agree with Florian that without OLE capabilities, your best course of
      action is to export the spreadsheet in a text form and have your REXX
      program read that.

      However, I have found that exporting spreadsheet files using horizontal
      tab (HT) works best.  One reason is that there is no potential problem
      with confusing a comma used as field separator with a comma which is
      part of a character cell value.

      When saving from Excel, choose Save as _t_ype:  "Text (tab delimited) (*.txt)"

      If you have a spreadsheet with 3 columns (A, B, and C), you can parse the exported, tab-delimited, text using:

            /* Example I(a) */
            HT = '09'X
            line = LINEIN( spreadsheet_text_filename )
            PARSE VALUE line WITH col_a (HT) col_b (HT) col_c

      If the number of items in the line is not fixed, you can use a loop:

            /* Example II(a) */
            HT = '09'X
            line = LINEIN( spreadsheet_text_filename )
            DO ii = 1  BY 1 WHILE line <> ''
               PARSE VALUE line WITH cell.ii (HT) line
            END ii
            cell.0 = ii - 1

      Generally, you will not have trouble with unformatted numbers.  However,
      formatted numbers (which may contain commas) and general text (which
      may contain commas and/or quotation marks) can cause headaches.

      Whether saving using .CSV or tab-delmited .TXT, you will have to deal
      with text enclosed in quotation marks.  This is done by Excel whenever
      a cell contains a comma, or quotation marks.  For example,
           Cruz, Bob
      becomes
           "Cruz, Bob"
      and
           Sean "Puffy" Combs
      becomes
           "Sean ""Puffy"" Combs"

      To cope with this, you will need to un-quote the cell values.
      My first example becomes:

             /* Example I(b) */
             HT = '09'X
             line = LINEIN( spreadsheet_text_filename )
             PARSE VALUE line WITH col_a (HT) col_b (HT) col_c

             IF  LEFT( col_a, 1 ) = '"'
             THEN  col_a = UnQuote( col_a )

             IF  LEFT( col_b, 1 ) = '"'
             THEN  col_b = UnQuote( col_b )

             IF  LEFT( col_c, 1 ) = '"'
             THEN  col_c = UnQuote( col_c )

             /* Example II(b) */
             HT = '09'X
             line = LINEIN( spreadsheet_text_filename )
             DO ii = 1  BY 1 WHILE line <> ''
                PARSE VALUE line WITH cell.ii (HT) line

                IF  LEFT( col_c, 1 ) = '"'
                THEN  cell.ii = UnQuote( cell.ii )

             END ii
             cell.0 = ii - 1  /* Number of cells in this row */

      Of course, you'll need the UnQuote function:

      UnQuote:  PROCEDURE
      /*REXX routine to remove quotes around, and doubled quotes within, a string */
          PARSE ARG /*quoted*/ string

          IF  LEFT( string, 1 ) <> '"'
          THEN  SIGNAL ERROR

          /* Remove leading quotation mark */
          string = SUBSTR( string, 2 )

          IF  RIGHT( string, 1 ) <> '"'
          THEN  SIGNAL ERROR

          /* Remove trailing quotation mark */
          string = LEFT( string, LENGTH( string ) - 1 )

          /* Replace doubled quotation marks with a single one */
          jj = POS( '""', string )
          DO  WHILE jj > 0
             string = LEFT( string, jj ) ,     /* text up to, and including, first quote of pair */
                   || SUBSTR( string, jj + 2 ) /* text following second quote of pair (if any)   */
             jj = POS( '""', string, jj + 1 )
          END

      RETURN /*unquoted*/ string

      Enjoy :-)

       

Log in to post a comment.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks