Welcome Serena Central users! CLICK HERE
The migration of the Serena Central community is currently underway. Be sure to read THIS MESSAGE to get your new login set up to access your account.
Ektoras Giorgos
New Member.
2882 views

Excel - Importing to a Grid error

Jump to solution

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

0 Likes
1 Solution

Accepted Solutions
Ektoras Giorgos
New Member.

RE: Excel - Importing to a Grid error

Jump to 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.

View solution in original post

0 Likes
6 Replies
neidingd Honored Contributor.
Honored Contributor.

RE: Excel - Importing to a Grid error

Jump to solution

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.

0 Likes
Ektoras Giorgos
New Member.

RE: Excel - Importing to a Grid error

Jump to solution

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.

0 Likes
Ektoras Giorgos
New Member.

RE: Excel - Importing to a Grid error

Jump to solution

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

0 Likes
neidingd Honored Contributor.
Honored Contributor.

RE: Excel - Importing to a Grid error

Jump to solution

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.

 
 

0 Likes
Ektoras Giorgos
New Member.

RE: Excel - Importing to a Grid error

Jump to solution

let me check on that

0 Likes
Ektoras Giorgos
New Member.

RE: Excel - Importing to a Grid error

Jump to 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.

View solution in original post

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.