Decimal and zeros added to text when using DC_Excel2Array

[GeneB]

Roger, I know you're busy, but when you get a chance could you look at this.
I have some users I promised a fix a month ago that are getting restless.
This is a problem in an Express function.
Thanks.

[Auge_Ohr]

When converting a spreadsheet to an array using DC_Excel2Array, a column that is fomatted as text but contains only numeric characters has a decimal and many zeros added to every cell in the column.

hi,
have misunderstood you last Request ...

i do not think Roger have change Code ... it is just 1 Line

Code: Select all

FUNCTION DC_Excel2Array( cExcelFile, bEval )
...
aValues := oBook:workSheets(1):usedRange:value

Question : what Excel Version are you using "now" and what "before" ?

a Xbase++ Array did not know about Excel Format "TYPE"
that 1 Line to transfer Excel Value to Xbase++ Array is like copy/paste via Clipboard.
you can not expand this 1 Line ... you have manipulate Array after Export

Problem : how do i know what TYPE*** is a Excel Column ?

***Excel does have a TYPE Function ... but how to call it from Xbase++ ?
https://support.office.com/en-us/articl ... n-US&ad=US

Code: Select all

 aRange := oBook:workSheets(1):usedRange // -> {A1:Z100}
 iMaxCol := StripNumber(aRange[2]) // -> 100
 FOR i := 1 TO iMaxCol
  //
  // how to use Excel TYPE Function with Xbase++ ? not tested Code
  // 
  nType := oExcel:TYPE( oBook:workSheets(1):GetCell("A"+LTRIM(STR(i))+":A"+LTRIM(STR(i)) )
  DO CASE
     CASE nType = 1 	// Number
     CASE nType = 2 	// Text
        AADD(aType,i) 
     CASE nType = 4 	// Logical Value
     CASE nType = 16 	// Error Value
     CASE nType = 64 	// Array 
  ENDCASE 
 NEXT

if you know which Column to convert

Code: Select all

 aValues := oBook:workSheets(1):usedRange:value
 //
 // now manipulate your Array
 // 
 FOR i := 1 TO LEN(aType)
    nCol := aType[i]
    AEVAL(aValues,{|x| x[nCol] := STR(x[nCol]) }) 
 NEXT

[rdonnay]

I am planning to release build 261 this week.

It will be fixed in that release.
I will also send you an update to _DCFUNCT.PRG so you can update your current dclipx.dll.

Before I do this, I want to test it with your XLS file.

BTW - You can fix this with a simple workaround:

Assuming that column 10 is the offending data:

Code: Select all

FOR i := 1 TO Len(aArray)
  aArray[i,10] := DC_XtoC(aArray[i,10])
NEXT

[GeneB]

I have written a workaround but it appears to me that Express is adding the zeros so it should be corrected.
Of course, I could be wrong. I was wrong twice last year.

I will send the test file to your email since I cannot upload a 'xls' file here.

[rdonnay]

You didn't attach a file.

[GeneB]

I cannot upload 'xls' file.
I tried adding 'prg', 'txt', 'test' to the end, still won't take it.
I will send to your email.

[rdonnay]

Gene -

I ran your test.xls file and saw the problem in the array. Unfortunately, there is little I can do about this because Excel does not conform to the cell formatting properties when exporting to an array. It would take a major rewrite of DC_Excel2Array() to add custom formatting for each cell during the import. This is not necessary because it can easily be accomplished in your application. I would have to do the same thing in the function with a lot more code, more parameters, more documentation, and more complaints.

The below code gives you what you want.

Code: Select all

#INCLUDE "dcdialog.CH"

FUNCTION Main()

LOCAL aData, i

aData := DC_Excel2Array("c:\test\barnhouse\test.xls")

FOR i := 1 TO Len(aData)
  aData[i,1] := Transform(aData[i,1],'99999')
NEXT

wtf aData pause

RETURN nil

* -----------

PROC appsys ; RETURN

[GeneB]

Your example will remove decimals, but the exposure for error exists for data that is all numeric character strings that include decimals and zeros.
I have written a workaround that includes a user selection to allow decimals or not. But one dizzy clerk can ruin an inventory data file in a matter of seconds.

My users receive spreadsheets, often times weekly, with inventory numbers and prices from several hundred different manufacturers. All of the manufacturers use their own format. Most of them use alpha characters and/or dashes in their numbering system so they are not a problem. The 'pure numeric character' inventory numbers are the problem, since they are presented as characters.

For now I guess I will have to rely on the toggle in the program and caution the users. I have also written a utility to remove inventory numbers that contain a specific set of characters so that hopefully they can remove the numbers themselves if they are inadvertently added to their file.

And cross my fingers. (Hope is not something I'm used to offering my customers as a solution.)
(Hope: desiring an outcome without providing any input)

[rdonnay]

I asked for a file that demonstrates the problem.
I gave you a fix for the problem with that file.
Now you are telling me it's a different problem.

[bwolfsohn]

gene,

can i suggest a text file with headers and column format definitions.

check the .xls column headers against the text file, and format accordingly.