Excel - Importing to a Grid error

[Ektoras Giorgos]

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

[neidingd]

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.

[Ektoras Giorgos]

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.

[Ektoras Giorgos]

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

[neidingd]

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.

[Ektoras Giorgos]

let me check on that

[Ektoras Giorgos · Accepted solution]

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.