TransferSpreadsheet Number Conversion

I am using TransferSpreadsheet to import Excel data (Name, Address, Phone Number) into Access, which works very well except for the Phone Number - which displays "2.08813e+009" - instead of "0208813....) as an example.   The leading zero would be easy enough IIf(Left([Phone],1)=0,[Phone],"0" & Str([Phone])) but the rest of the number seems lost.

I think the number is already buggered by the import - but has anyone any bright ideas?
Who is Participating?
jjafferrConnect With a Mentor Commented:
Again, I still think the problem is from Excel,
copy and paste this number in the Table Or Form and see what you get,
I tried it for both Double and text and they both worked,
Thats why I suspect its from Excel.

In the Table, Make you Text field 20 characters long and try it again.
Hi Norbert2000,

Change the Data Type of the field in the Table to Text

Hope this helps

While keeping the field Data Type to Number, go down to the properties,
change the field Size to Double
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

The second option will NOT maintain the leading zeros unless you use Format, for example:
if your number is 0208813 which is 7 characters long, So ALL your numbers should be 7 characters long in order for this to work:
in the Format, write: 0000000 (7 zeros)
Norbert2000Author Commented:
Hi Jaffer,

Oh, if only it were that easy.  It already is set to text.  In fact, as TransferSpreadsheet creates the table if one isn't present, it automatically creates the Data Type as Text.  (This may be because one of the entries in my example has 2 phone numbers in the style xxxx/xxxx).

As a try,
Please select the Phone column, in Excel, and change it to Text, then Import and see if changes anything.
I suspect it is Excel that is converting the number to A sience field
Norbert2000Author Commented:
Double seems to give a better result - but still not right.  

A phone number which should be 01752666666 would read : 1752666666 in the spreadsheet and 1752670000 in the database.   The conversion seems to be doing the rounding.
Norbert2000Author Commented:
Very strange results - although you are right.   The spreadsheet arrives as a CSV, and if I go through the import into excel properly and format as text, it works.   If I don't - and set the property on the column afterwards, it fails.    Looks like the data I was testing on wasn't perhaps the best.

Points awarded! Thanks Jaffer
I am glad I was of help,
Thanks for the points and the grade.

But why do you go: CSV > Excel > Access
Why not go directly: CSV > Access
One trick to take care of is, make the first field as text, this way You are telling Access what you want the field to be, rather than making Access go through the columns and set the Data Type automatically as it reads it.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.