Import from Excel problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I import several Excel spreadsheets into various tables
after being downloaded from an AS/400 without problem
I am now creating an enhancement to the Db and would like
to have users be able to manually change some fields after
download from AS/400 and prior to import into Access
While testing I find that some fields in records not
manipulated are somehow changed
An example is
Spreadsheet A is imported into table A containing Invoice#
and Amt
Some row's amt fields in Spreadsheet B are changed to 0
and imported into table B also containing invoice #'s and
amts for the most part identical to table A
I then need to join on invoice# and amt for export and
upload back up to the AS/400
The problem I am getting is that in some unchanged records
the Amt fields in table B are not equal to table A even
though the Amt is identical and the tables are structured
identically
For the most part the amts do equal and the export works
as expected for those records
Does anyone have an idea what could be changing just a few
amt fields in table B?

Thanks
Bil
 
In the past, I had problems due to the "," separator when
the field was formatted as a number. I fixed it on the
output end before bringing it into Access. I was able to
change the property to not utilize the comma.
 
Thanks for your reply
I have had that problem in the past under some different circunstances. That was the first place I looked
This appears to be different because the import actually works and I can join on invoice and amount for most records
It's just a handful of single records spaced apparrantly randomly through the table imported into get weird where a join on these fields comes up empty though the fields contain the same values. If a user doesn't change anything or save the spreadsheet all records are imported correctly. If the user does or doesn't change anything but does do a save(in the right format/version) the problem occurs.
 
Hi Bill,

This is probably due to the way Excel stores numbers in a binary format
(same as the VBA Double data type and Access's Double field type).
Depending on how the amounts were calculated in the spreadsheets you may
get the same amount in dollars and cents stored as different binary
numbers; e.g. if the amount is $35.97 the value actually stored in one
spreadsheet could be a binary representation of 35.97000000001 and the
other 35.969999999999 - but in the user interface you find that 35.97 is
not equal to 35.97.

One way round it is to use the Currency field type in Access: this
stores precise values to 4 decimal places. Another, using Double fields,
is to replace joins like this
FROM tblA INNER JOIN tblB ON tblA.Amt = tblB.Amt
with an approximate comparison by rounding the numbers
FROM tblA INNER JOIN tblB ON Round(tblA.Amt, 4) = Round(tblB.Amt, 4)
or even using the sort of comparison one would use in Excel or VBA:
FROM tblA, tblB WHERE Abs(tblA.Amt - tblB.Amt) < 0.0001
 
Hi Bill,

I suppose it's conceivable that current versions of Excel store numbers
differently from Excel 4. Certainly the format of the xls file has
changed - but as far as I know the binary encoding of real numbers
hasn't.

You say that the affected records are randomly located. As I understand
it the users are modifying or updating certain records in the table. If
my theory about the cause of the problem is correct, I would expect it
to occur only with records that have somehow been edited in Excel (but
only some of these), and not with records that have not been edited in
Excel. Is that the case?

Either way, if you are doing arithmetic with currency values stored in
Single or Double fields you are likely sooner or later to find
(X = Y)
returning false even though X and Y are superficially equal. Either use
the Currency data type (with whatever custom format your accountants
want) or take appropriate precautions (e.g. round the numbers before
comparing them, or use the Abs(X - Y) < 0.00001 trick.

John,
Thanks for your reply. I will try what you suggest.
I have been told that a problem may be that the AS/400 is only capable of outputting Excel version 4.
Even though users save the spreadsheet prior to import by clicking NO in the "Save as latest Excel version?" popup Excel still can only save the spreadsheet as the oldest version available to their local version, that being version 7.
When I compare values in the tables, one imported straight from the AS/400
Click to expand...
downloaded as Excel version 4 the other imported from the users saved
version 7 I incorrectly get Amount values not equal in records randomly
intersperced throughout the table.
The majority of records do come up equal as should be and export well.
I have the amount fields in both tables structured as double as Currency
adds $ and is not acceptable to my accountant users.
 
Back
Top