Excel - Importing to a Grid error

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.

let me check on that

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.