Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

How do I import fractions from an Excel Spreadsheet into Access?

Discussion in 'Information Technology' started by Lauralee, Jul 28, 2006.

  1. Lauralee

    Lauralee
    Expand Collapse
    Guest

    I am trying to take a Excel spreadsheet and link the sheet to a Access
    database. The problem is that part of the information is in fractions when I
    import it into the database all the fractions turn into decimals. I have
    changed the format to text and re-typed the fractions in and that seems to
    work but the information that I am using is quite extensive and re-typing
    each cell would take hours.
    Is there an easier way for this to be linked or imported?
     
  2. Loading...

    Similar Threads Forum Date
    Importance Of Dastar History of Sikhism Aug 12, 2016
    Importance Of Tirath / Pilgrimage As Per Gurbani Gurmat Vichaar Jun 1, 2016
    Superheroes: Arjan Bhullar Explains Wrestling's Importance In The Sikh Community Sports & Fitness Apr 15, 2016
    Important decisions in life Questions and Answers May 25, 2015
    Friendship Between Genders... Important Question Questions and Answers Dec 20, 2012

  3. Mike Labosh

    Mike Labosh
    Expand Collapse
    Guest

    >I am trying to take a Excel spreadsheet and link the sheet to a Access
    > database. The problem is that part of the information is in fractions
    > when I
    > import it into the database all the fractions turn into decimals. I have
    > changed the format to text and re-typed the fractions in and that seems to
    > work but the information that I am using is quite extensive and re-typing
    > each cell would take hours.
    > Is there an easier way for this to be linked or imported?


    If I am reading you correctly, you have stuff in a cell in an Excel
    worksheet that looks like this: "1/4"

    And after it imports to an Access table, it looks like this: ".25"

    I have never personally done an import from Excel to Access that contains
    fractions, but here are a few tips:

    1. To retain the text, never EVER use a Number data type in Access for
    stuff that looks numeric. For example, my ZIP (Postal Code) happens to be
    "19021-6020". If I store that in Access as Text, it retains the proper
    value. But if I store it in Access as Numeric, then Access interprets the
    "-" character as a subtraction symbol, automatically does the math, and
    stores the result. So in the case of my ZIP Code, I get "13001" stored in
    the database.

    2. Same thing with phone numbers, social security numbers, or any other
    value that looks like numbers but has "-" embedded in it. And you would not
    believe the bizarreness that happens when a phone number extention is
    prefixed with a "/" character: (215)-555-1212/1234

    3. The way to handle this kind of input is to make sure the column in
    your Access table is defined as Text instead of Number. The only time you
    should store stuff in Access as a Numeric datatype is when you fully intend
    to do math on it. Since you never do math on a postal code or phone number,
    just store it as text.

    4. Once you have done that, and you define your "xyz" column as text, and
    then you import some stuff into it, occasionally, Access's Import Wizard
    will screw it up, because the Import Wizard is brain-dead.

    So once you have converted your numeric column to text, and then fixed up
    all your data bindings, forms, code and so forth, it is still possible for
    the Import Wizard to mess up your data. If this is what you are
    experiencing, then you will have to put the Wizard behind you and just write
    your own code to open the file, read the contents, build a giant recordset
    object and run through a loop, saying rs.Update for each row in the file.
    --


    Peace & happy computing,

    Mike Labosh, MCSD MCT
    Owner, vbSensei.Com

    "Escriba coda ergo sum." -- vbSensei


    "Lauralee" <Lauralee@discussions.microsoft.com> wrote in message
    news:6C1F9CB7-027D-43FA-AE36-8D7165A8A435@microsoft.com...
     
  4. Lauralee

    Lauralee
    Expand Collapse
    Guest

    Re: How do I import fractions from an Excel Spreadsheet into Acces

    Thanks Mike, I certainly will try it, I was trying to work with the
    information in Excel and seems that the real issue was in Access. Whoda Thunk!

    Your help is appreciated.

    "Mike Labosh" wrote:

    > >I am trying to take a Excel spreadsheet and link the sheet to a Access
    > > database. The problem is that part of the information is in fractions
    > > when I
    > > import it into the database all the fractions turn into decimals. I have
    > > changed the format to text and re-typed the fractions in and that seems to
    > > work but the information that I am using is quite extensive and re-typing
    > > each cell would take hours.
    > > Is there an easier way for this to be linked or imported?

    >
    > If I am reading you correctly, you have stuff in a cell in an Excel
    > worksheet that looks like this: "1/4"
    >
    > And after it imports to an Access table, it looks like this: ".25"
    >
    > I have never personally done an import from Excel to Access that contains
    > fractions, but here are a few tips:
    >
    > 1. To retain the text, never EVER use a Number data type in Access for
    > stuff that looks numeric. For example, my ZIP (Postal Code) happens to be
    > "19021-6020". If I store that in Access as Text, it retains the proper
    > value. But if I store it in Access as Numeric, then Access interprets the
    > "-" character as a subtraction symbol, automatically does the math, and
    > stores the result. So in the case of my ZIP Code, I get "13001" stored in
    > the database.
    >
    > 2. Same thing with phone numbers, social security numbers, or any other
    > value that looks like numbers but has "-" embedded in it. And you would not
    > believe the bizarreness that happens when a phone number extention is
    > prefixed with a "/" character: (215)-555-1212/1234
    >
    > 3. The way to handle this kind of input is to make sure the column in
    > your Access table is defined as Text instead of Number. The only time you
    > should store stuff in Access as a Numeric datatype is when you fully intend
    > to do math on it. Since you never do math on a postal code or phone number,
    > just store it as text.
    >
    > 4. Once you have done that, and you define your "xyz" column as text, and
    > then you import some stuff into it, occasionally, Access's Import Wizard
    > will screw it up, because the Import Wizard is brain-dead.
    >
    > So once you have converted your numeric column to text, and then fixed up
    > all your data bindings, forms, code and so forth, it is still possible for
    > the Import Wizard to mess up your data. If this is what you are
    > experiencing, then you will have to put the Wizard behind you and just write
    > your own code to open the file, read the contents, build a giant recordset
    > object and run through a loop, saying rs.Update for each row in the file.
    > --
    >
    >
    > Peace & happy computing,
    >
    > Mike Labosh, MCSD MCT
    > Owner, vbSensei.Com
    >
    > "Escriba coda ergo sum." -- vbSensei
    >
    >
    > "Lauralee" <Lauralee@discussions.microsoft.com> wrote in message
    > news:6C1F9CB7-027D-43FA-AE36-8D7165A8A435@microsoft.com...
    >
    >
    >
     

Share This Page