Welcome to SPN

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

Sign Up Now!

Linking Excel Table to Access Database

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

  1. Maurice

    Maurice
    Expand Collapse
    Guest

    When linking an excel table to Access, certain data are displayed as "#Num!".
    How do i overcome the mentioned problem, as it does not affect all data of
    the exact same nature. Certain values are displayed correctly, whilst others
    are not.
    I will appreciate some advise towards resolving this particular problem.

    Kind regards.
    Maurice
     
  2. Loading...


  3. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Tue, 13 Jun 2006 00:31:02 -0700, Maurice
    <Maurice@discussions.microsoft.com> wrote:

    >When linking an excel table to Access, certain data are displayed as "#Num!".
    >How do i overcome the mentioned problem, as it does not affect all data of
    >the exact same nature. Certain values are displayed correctly, whilst others
    >are not.
    >I will appreciate some advise towards resolving this particular problem.
    >
    >Kind regards.
    >Maurice


    Since Access has strong datatyping (every field must have a defined
    datatype) while Excel does not (each cell is independent, a column may
    contain any combination of datatypes), Access must *guess* the
    appropriate datatypes when importing or linking to a spreadsheet. It
    does so by looking at the first few (I don't know the numeric value of
    "few" here <g>) rows. If they are all numeric, Access treats that
    column as if it were of Number type.

    If further down the sheet you have non-numeric data you'll get this
    error. A common example is Zip or PostCodes - if you have five-digit
    numbers in the first several rows, and further down something like
    83660-6354 or N3Y 8B1, you'll get an error.

    One getaround is to put a dummy row at the top of the spreadsheet with
    an unambiguous text value ('X for example) in each such column.

    John W. Vinson[MVP]
     
  4. aaron.kempf@gmail.com

    aaron.kempf@gmail.com
    Expand Collapse
    Guest

    make your beancounters export into CSV and use BULK INSERT.

    this of course; assumes that you know SQL Server.
    if you don't then get out of the db industry.

    -Aaron


    John Vinson wrote:
    > On Tue, 13 Jun 2006 00:31:02 -0700, Maurice
    > <Maurice@discussions.microsoft.com> wrote:
    >
    > >When linking an excel table to Access, certain data are displayed as "#Num!".
    > >How do i overcome the mentioned problem, as it does not affect all data of
    > >the exact same nature. Certain values are displayed correctly, whilst others
    > >are not.
    > >I will appreciate some advise towards resolving this particular problem.
    > >
    > >Kind regards.
    > >Maurice

    >
    > Since Access has strong datatyping (every field must have a defined
    > datatype) while Excel does not (each cell is independent, a column may
    > contain any combination of datatypes), Access must *guess* the
    > appropriate datatypes when importing or linking to a spreadsheet. It
    > does so by looking at the first few (I don't know the numeric value of
    > "few" here <g>) rows. If they are all numeric, Access treats that
    > column as if it were of Number type.
    >
    > If further down the sheet you have non-numeric data you'll get this
    > error. A common example is Zip or PostCodes - if you have five-digit
    > numbers in the first several rows, and further down something like
    > 83660-6354 or N3Y 8B1, you'll get an error.
    >
    > One getaround is to put a dummy row at the top of the spreadsheet with
    > an unambiguous text value ('X for example) in each such column.
    >
    > John W. Vinson[MVP]
     
  5. Ron2006

    Ron2006
    Expand Collapse
    Guest

    I have had better results if I imported the excel spreadsheet into
    access instead of linking it.

    There can still be problems but fewer. If problems still come up then
    the I have used the approach that John has suggested. Once imported
    then just run a quick query to delete that particular row.
     

Share This Page