Decimal and zeros added to text when using DC_Excel2Array

[bwolfsohn]

Thanks, but it is not a format problem. The problem is that a character string in a spreadsheet cell, formatted as text, containing the data "12345", when converted to an array item, becomes "12345.0000" (text) in the array.
The solution is not to truncate the decimal and zeros from the character string created, since converting a cell containing "12345.000" would then be changed to "12345" by the truncation. This would also be an incorrect transfer of data.
I'm hoping someone knows a way to extract the exact character string from the spreadsheet so it can then be compared to the array item.

thanks for the clarification gene..
We don't use dc_excel2array.

we import from csv's.

if you save the excel file as a csv, does it still contain the same problem ? i.e. 12345 = 12345.0000 ?

[Auge_Ohr]

Thanks, but it is not a format problem.

it is a Format Problem ... but it is "in" Excel as i say.
you must check Excel Sheet how your User have format it.

Code: Select all

   cType := oSheet:range("A1:A1"):NumberFormat
   cType := oSheet:range("B1:B1"):NumberFormat
   cType := oSheet:range("C1:C1"):NumberFormat
   cType := oSheet:range("D1:D1"):NumberFormat

if User have use "@" or "Text" its easy but if User have NOT format Column -> "Standart"

[GeneB]

Good suggestion about the csv file. Saving a spreadsheet in that format does yield text data correctly, and it could be easily manipulated, but getting an entire industry to change their pricing format from xls to csv, or trying to get everyone that uses my software to open every price sheet and save it in a different format would be embarrassing to ask, and I don't even want to think about what my competitors would say.

Thanks, Jimmy, but this is not a format problem. This happens whether the cell's format is 'text' or 'general'. Somewhere in the conversion to an array item the character string, under the conditions already described, is altered. it appears that the string is converted to numeric data with perhaps a default decimal format, and the data is converted back to a character string.

Fortunately, vendors that use inventory numbers that consist of all numeric characters are the exception. Unless I discover a way to convert xls data to an array 100% correctly every time, I am forced to build a work around into the software. I will add a last step in the inventory update that scans the array to spot any inventory numbers that contain ".0000". If this is true, then I will pop up a warning window and an option to truncate the zeros if necessary.
Not the way I like to do things. I still hope there is a solution.

I think the horse is almost at room temperature.

[GeneB]

Thanks, Wolfgang.
Your code is remarkably close to the method I am using with the exception that I use DC_Excel2Array to create the array.
I will try creating the array using just your Xbase code to see if makes a difference but I suspect, as was originally discussed, that Excel is the culprit.

[bwolfsohn]

can you xbase++ program using automation object to open the file .xls and save it .csv ??

just throwing an idea off the wall..

from microsoft:

You may try Cygwin (https://www.cygwin.com). Its a open source application with a command prompt and you can convert large xls files to csv without even opening it with a single command. There are many other utilities available apart from csv conversion.

from stackoverflow:

http://stackoverflow.com/questions/1858 ... mmand-line

[GeneB]

Browsing the links you posted gave me hope that there is a solution to this, but it is way above my capacity to learn. This is why people like me buy libraries and language enhancements.

I really appreciate your input. I guess until more people are affected by the problem and it is resolved I'll have to rely on my workaround.

RIP horse.

[skiman]

Hi,

Take care if you save to CSV! You better save to TEXT with TAB as delimitter. Otherwise you can have strange results with EAN codes.

The following code shows how to read your XLS file. This way you can get an array row by row. Then it is up to you to know the type you need for each column, and to save it accordingly in your database.

Code: Select all

	oExcel := CreateObject("Excel.Application")
	IF Empty( oExcel )
		dc_alert("Excel must be installed on this computer")
		RETURN nil
	ENDIF

	oExcel:DisplayAlerts := .t.
	oExcel:visible       := .f.
	cXls := alltrim(cXls)
	oBook  := oExcel:workbooks:open(cXls)
	oSheet := oBook:ActiveSheet
	oUsedRange := oSheet:usedrange
	nMaxLines :=  oUsedrange:rows:count()	
	aData := oSheet:Range("A1:BZ1"):Value 

I hope this helps.

[skiman]

Hi,

The following should work:

oExcel:workbooks:saveas("c:\temp\abc.csv",xlCSV)

I didn't try this.

[GeneB]

I will give this a try.
Thank you very much.