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?
LVL 1
Norbert2000Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jjafferrCommented:
Hi Norbert2000,

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

Hope this helps

Jaffer
0
jjafferrCommented:
OR
While keeping the field Data Type to Number, go down to the properties,
change the field Size to Double
0
jjafferrCommented:
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)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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).

0
jjafferrCommented:
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
0
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.
0
jjafferrCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
0
jjafferrCommented:
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.

jaffer
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

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.