Help Please: Transferspreadsheet brings in odd format

[Perico]

I'm using Ac 2003, Win xp pro, Excel 2003. My sheet is formatted as text.
Example: data is 3249388005 in Excel but imports as 3.24939e+009 in Access.

I've formatted the target table to which the data is appended as Text. As
both the Excel cells and the Access field is formatted as text, I don't know
how to correct this. It appears this is a scientific notation type data
format problem.

Please help: how to I preserve the Excel format?

 

[Tony Toews]

I'm using Ac 2003, Win xp pro, Excel 2003. My sheet is formatted as text.
Example: data is 3249388005 in Excel but imports as 3.24939e+009 in Access.

I've formatted the target table to which the data is appended as Text. As
both the Excel cells and the Access field is formatted as text, I don't know
how to correct this. It appears this is a scientific notation type data
format problem.

Insert a dummy row at the top of your spreadsheet below your heading
line. Put garbage text in the fields you want imported as text. And
so forth.

Then delete that row once it's imported.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Tony Toews]

I should've added that the import routine looks at the first few rows
of the spreadsheet and, on it's own, decides the format of the
incoming data.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Chris Mills]

It's a very clever suggestion. On the face of it, a bit obscure.

Of course, it will cause all imports to occur as text.

Rounding of numerics in digital computers is often an issue. All I can say, is
store numerics only to the required precision (Integer, or Currency which is
the same thing with decimal point shifted). Floating-point always has the
possibility of giving an "e to the x format", which can usually be suppressed
(an AXE taken to) with code-rounding or at a pinch output formatting.

An example of code-rounding would be (in vapourware)
((INT(NUMBER + 1000) +0.5)/1000
Such is likely to be necessary even if the import is successfull!

(Thinks: why does my supermarket have notices where they say they use SWISS
ROUNDING? I don't think the SWISS are twisted, at least no more than the rest
of us)

Chris

(Floating-point numbers are stored in a fancy BINARY e^x layout, which can
only ever be as precise as the NEAREST decimal number, to the precision the
storage or display method allows. Integers, on the other hand, are just the
same integer numbers whether in Binary or Decimal notation)

 

[Tony Toews]

It's a very clever suggestion. On the face of it, a bit obscure.

Not my idea.

Of course, it will cause all imports to occur as text.

Yes, assuming you put text in all fields. When I reread my posting it
came as implied that you'd put numeric or date data in other fields
but it wasn't explicit.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm