Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

TransferSpreadsheet Number Conversion

Posted on 2004-10-24
9
Medium Priority
?
235 Views
Last Modified: 2008-03-06
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?
0
Comment
Question by:Norbert2000
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
9 Comments
 
LVL 27

Expert Comment

by:jjafferr
ID: 12392076
Hi Norbert2000,

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

Hope this helps

Jaffer
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 12392084
OR
While keeping the field Data Type to Number, go down to the properties,
change the field Size to Double
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 12392090
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 1

Author Comment

by:Norbert2000
ID: 12392095
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
 
LVL 27

Expert Comment

by:jjafferr
ID: 12392107
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
 
LVL 1

Author Comment

by:Norbert2000
ID: 12392115
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
 
LVL 27

Accepted Solution

by:
jjafferr earned 2000 total points
ID: 12392123
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
 
LVL 1

Author Comment

by:Norbert2000
ID: 12392125
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
 
LVL 27

Expert Comment

by:jjafferr
ID: 12392132
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

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

597 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question