Excel - Importing to a Grid error

I am fetching an excel file into an acu grid but some cells isnt transfered properly. An excel cell of text format that contains "1" its tranfered as "1,000000000000000E0" in the Acu Grid. Does anyone know why?

01 W-BX-CELL-DATA pic X(100) VALUE SPACES.

INQUIRE olWrkSh
Range(TARGET)::Value IN W-BX-CELL-DATA

  • i know that Problem. But never found a 100% solution for it. In the most case in Excel all Looks good.

    In some programs i use a Excel Formular to inquire if the cell is numeric or not and then inquire the needed cells as number or text.

    But this also dosn't help everytime....

    Another way i used is to Export the Data to csv and then Import the csv back to Excel and then the cells can be inquire with the correct value.

  • Yes i already know that but thanks mention it! Well i do a:

              INQUIRE olWrkSh

                Range(TARGET)::NumberFormat IN W-BX-FORMAT-TYPE

    to find out what format has the cell and then do:

              EVALUATE W-BX-FORMAT-TYPE

               WHEN "@"

                INQUIRE olWrkSh

                  Range(TARGET)::Value IN W-BX-TMP-EXCEL-STRG

                MOVE W-BX-TMP-EXCEL-STRG TO W-BX-CELL-DATA

               WHEN "0"

                INQUIRE olWrkSh

                  Range(TARGET)::Value IN W-B9-TMP-EXCEL-NMBR1

                MOVE W-B9-TMP-EXCEL-NMBR1 TO W-BZ-TMP-EXCEL-SNBR1

                MOVE W-BZ-TMP-EXCEL-SNBR1 TO W-BX-CELL-DATA

               WHEN "0,000"

                INQUIRE olWrkSh

                  Range(TARGET)::Value IN W-B9-TMP-EXCEL-NMBR

                MOVE W-B9-TMP-EXCEL-NMBR TO W-BZ-TMP-EXCEL-SNBR

                MOVE W-BZ-TMP-EXCEL-SNBR TO W-BX-CELL-DATA

               WHEN "#,##0.00;[Red](#,##0.00)"

                INQUIRE olWrkSh

                  Range(TARGET)::Value IN W-B9-TMP-EXCEL-SNBR

                MOVE W-B9-TMP-EXCEL-SNBR TO W-BZ-TMP-EXCEL-SNBR

                MOVE W-BZ-TMP-EXCEL-SNBR TO W-BX-CELL-DATA

               WHEN "General Type"

                INQUIRE olWrkSh

                  Range(TARGET)::Value IN W-BX-TMP-EXCEL-STRG

                MOVE W-BX-TMP-EXCEL-STRG TO W-BX-CELL-DATA

               WHEN OTHER

                INQUIRE olWrkSh

                  Range(TARGET)::Value IN W-BX-TMP-EXCEL-STRG

                MOVE W-BX-TMP-EXCEL-STRG TO W-BX-CELL-DATA

              END-EVALUATE.

    to put value to the appropriate variable.

  • But i still have problem with numbers such telephones, they are transfered like "1,000000000000000E0" or something like that.

  • you inquire the Format, i insert in the Excel sheet a formula and inquire the result.

          ***  Import Data.
               perform varying zdx from 1 by 1 until zdx > mySize
                perform varying sdx from 1 by 1 until sdx > mySdx
                 compute ddx = (myOdx - zdx) * - 1 end-compute
                 move    ddx to ddt
                 inquire xls-sheet @CELLS::@Range("A4")::Offset(myOdx, sdx)
                         xls-range
                 modify  xls-range @SELECT()
                 string  '=ISNUMBER(R[' ddt ']C)'
                         delimited by size into xls-formel(sdx)
                 end-string
                 modify  xls-sheet Range("A4")::Offset(myOdx, sdx)::Formula
                         xls-formel(sdx)
                 inquire xls-range @VALUE typ-data(sdx)

                 evaluate typ-data(sdx)
                  when "-1"
                    inquire xls-sheet Range("A4")::Offset(zdx, sdx)::Value
                            in xls-integer(sdx)
                  when "0 "
                    inquire xls-sheet Range("A4")::Offset(zdx, sdx)::Value
                            in xls-string(sdx)
                    inspect xls-string(sdx)
                            converting "õ÷³─Í▄▀" to "äöüÄÖÜß"
                 end-evaluate
                end-perform
                perform convert-data
               end-perform.

     
     

  • Verified Answer

    Solution is!

    well the only thing that helped is this.

    pcunleashed.com/.../how-to-convert-text-to-columns-in-excel

    before i import data, especially numbers, first i use the "text to columns" command to convert the cells. Then the rest is made in the code we already mention above.