Decimal and zeros added to text when using DC_Excel2Array

[GeneB]

I have tried to describe this problem on this forum since last month. Many others have offered suggestions and I have tried to explain why their suggestion is not the ultimate solution.
You said that you demonstrated for yourself what is happening. I have not tried to describe a different problem. You also said that the array items in my example are different from the original characters in the cell.

The problem again: An array is created from an excel spreadsheet to update pricing in an inventory file.
A Search is performed in the inventory.dbf for each item in the new array. Since the array contains a decimal and zeros that are added to every item from the cell that consists of only numeric characters 0-9, even though the cell was formatted as text and the characters in the new array item are text, there is no match found in the inventory dbf because of the added decimal and zeros. Those inventory items do not have their price updated.

The items in the array that are not Found in the inventory dbf are then available for the user to add to their inventory.dbf if they select to do so. If the user selects 'yes', they add scores of inventory items that don't exist since they are essentially adding their existing inventory with a decimal and zeros added.

This is not a problem with not identifying the format of the cell in the programing, it is a problem with the creation of the array. Some items in the array are not the same as items in the cell since under the condition described they have a decimal and zeros added to the item. Parsing the decimals can be done, but when does the prg parse the errant items with zeros, and when does it not parse the ones that actually do have a decimal and zeros in the cell?

This is not an irritation, this is a real problem. Under conditions I cannot control comparisons from a spreadsheet cannot be accurately made with records in a dbf since the array created does not contain the exact information from the spreadsheet.

I'm not trying to be pushy or flippant, and heaven knows I have been patient since I first reported this over a month ago, but I have a real exposure to both data corruption and also to the reputation of the accuracy of my program.

Roger, your solution truncates the array items to a fixed width without a decimal and zeros, but there are times when the decimal and zeros are legitimate in the array (if they are characters in the spreadsheet cell).

The bottom line is that DC_Excel2Array does not create an exact copy of the cell in each array item under the conditions I described and it should.

[GeneB]

DC_Excel2Array()

Excel -> Array

"ABC-123" -> "ABC-123"
"ABCDEFG" -> "ABCDEFG"
"123456" -> "123456.0000"
"123456.0000" -> "123456.000"

[skiman]

Hi,

I have the same situation with my customers, they receive an XLS and they want them to import. Each supplier sends another format. In my opinion it is impossible to read these files automatically.

I did it the following way. My users need to do the following:
- Save the XLS as 'text with TAB as delimeter'. Not as CSV, because Excel is saving EAN codes wrong.
- Define a template for that list. So for each supplier they need to define once the template. They can select the fields for each column. So for supplier ABC they have the following:
Column A : productnumber
Col B: Description
Col C: price
Col D: Ean
And for another supplier maybe the EAN is in Column A and the productnumber is in F.

- I know which type is in a column because it has to be according to the choosen field. Productnumber is Character, Price is numeric and so on.
- I know which column contains the productnumber, so this is my key field for the index.

- Once the template is defined, you can import. I do an import to a temporary file, and the users gets a browse with the data. So he/she can check before proceeding and importing into my database.

This is working since 2000 without a problem. Most of my users defines there templates by themself, others send the pricelist to me, and pay us to define the needed template. Something my helpdesk can do in 15 minutes.

It could be easily done without converting the XLS to a TXT. But the creation of some template is needed, otherwise you will never get a result which can read all the pricelists you get.

Don't expect Roger to create a function which reads those f*****g xls files without problems. He is a marvelous programmer, but everyone has his limits. And don't expect he can solve problems that are caused by our friend Bill Gates. He is the cause of that crappy XLS format.

[rdonnay]

Code: Select all

And don't expect he can solve problems that are caused by our friend Bill Gates. He is the cause of that crappy XLS format.

Poor Bill. Oops, I mean rich Bill.

Thanks, everyone, for helping me understand your problems.
Maybe we should take this conversation to the bar.
The bar will be open after 5 pm each day at my upcoming conference in early May.

Gene - You are one of the highest posters on this forum. I think you could benefit a lot by joining us here in Boise in May. Aaron Paul is from here and he always brings me a nice blue Albuquerque crystal when he comes to town. Says he gets it from some cowboy down in New Mexico.

[GeneB]

High poster maybe, but not to be confused with high programmer. I don't even understand half the questions that are posted.
Like everyone on this forum, Roger, I too have a reverence for you and your ability, and I am always amazed at the time you give for the simplest of our needs.
And a personal invitation to come to Boise, I'm flattered. Unfortunately I have to be in Texas at that time, but I couldn't keep up with the group before 5:00, and certainly not after 5:00. I don't know what cowboy in New Mexico is supplying but it isn't me. Breaking Bad left town.

Chris, it sounds like you have handled inventory files almost exactly the way I have. I also provide a system setup where the common column names the manufacturers use for product number, price, etc. can be entered and selected later.

I understand that Excel and Active-X have some limitations, and I don't know anything about how they work, but my questions are:
1. Is the addition of decimals to numeric character data during the conversion to an array item only due to Excel formatting problems?
2. Is there any way to to compare the array items created to the original Excel cell to verify that they are exactly the same when both contain text characters and flag them or correct them if they aren't identical.
3. Is there really no solution to this due to Excel's formatting or is adding a checking routine into DC_Excel2Array a value vs cost issue?

I'm not trying to beat this horse any further, but I'm under a lot of pressure from some of my customers to provide a solution. I tried earlier to blame this on Bill Gates but they aren't buying it. I'm even willing to seek the help of an outside programmer. Please tell me if I should just bury this horse and I will.

[skiman]

Hi,

Your questions:

1. Is the addition of decimals to numeric character data during the conversion to an array item only due to Excel formatting problems?
2. Is there any way to to compare the array items created to the original Excel cell to verify that they are exactly the same when both contain text characters and flag them or correct them if they aren't identical.
3. Is there really no solution to this due to Excel's formatting or is adding a checking routine into DC_Excel2Array a value vs cost issue?

In fact, the system I have is using my dbase file to define the type of the data. If a column of the XLS must be saved in a character field, it doesn't matter what is in that xls. It can be 'ABC' or it can be '123'.

I don't think that you can solve this with DC_Excel2Array. I never used that function, but I expect it is to general. A EAN code as 1234567890123 looks as a numeric, but will be saved as a character in your dbf. If you open an xls file with EAN codes, also Excel is doing strange things with these codes. I don't know what dc_Exel2array will do with this, but I don't expect it will be correct. It can't know that it is a character type.

[GeneB]

I use DC_Excel2Array because I have no idea how to extract one value from a cell in a spreadsheet and store it in a field, variable, or array. Is there a way to do this in Xbase? I could then write a function to somehow compare the length of the string, value, number of decimals, etc. and then make a correction.
The problem, as I see it, is that I have no way to differentiate between a converted character string that contains a decimal and zeros added to a string, and a converted character string that did have a character decimal and character zeros in the string as it was stored in the original cell.This could be as simple as a length comparison.
We can't change the way Excel handles data but surely there is a way to identify whether a change was made by the conversion process and then change it back.
Without this, how can we trust 100% the data produced by DC_Excel2Array?

I just saw the horse twitch a little bit.

[Wolfgang Ciriack]

See my posting http://bb.donnay-software.com/donnay/vi ... rt=0#p7671

[bwolfsohn]

I use DC_Excel2Array because I have no idea how to extract one value from a cell in a spreadsheet and store it in a field, variable, or array. Is there a way to do this in Xbase? I could then write a function to somehow compare the length of the string, value, number of decimals, etc. and then make a correction.
The problem, as I see it, is that I have no way to differentiate between a converted character string that contains a decimal and zeros added to a string, and a converted character string that did have a character decimal and character zeros in the string as it was stored in the original cell.This could be as simple as a length comparison.
We can't change the way Excel handles data but surely there is a way to identify whether a change was made by the conversion process and then change it back.
Without this, how can we trust 100% the data produced by DC_Excel2Array?

I just saw the horse twitch a little bit.

Gene,

you can continue to use dc_excel2array.
once you have a separate definition file set-up, you can check the headers against the definition file.
if column3 is headed price, you can format the 3rd element in each row that way as you process each line..

i've never used dc_excel2array, so just guessing here

for row_number:=1 to len(myarray)
myarray[row_number,3]:=format this as a number
// and if the 4th element is a character
myarray[row_number,4]:=format this as a character
next

[GeneB]

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.