Solved

TransferSpreadsheet Number Conversion

Posted on 2004-10-24
233 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
Question by:Norbert2000
    9 Comments
     
    LVL 27

    Expert Comment

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

    Author Comment

    by:Norbert2000
    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
    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
    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:
    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
    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
    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    933 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now