Welcome to SPN

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

Sign Up Now!

remedy: import problems with alpha and numeric data in column

Discussion in 'Information Technology' started by Veronica, Oct 31, 2005.

  1. Veronica

    Veronica
    Expand Collapse
    Guest

    I have mixed data in a record (column) such as PA123 and 10234. When I
    import two different worksheets into Access from Excel, I get type conversion
    errors. What is the best was to format these columns to avoid errors. I use
    Access to compare the data from one spreadsheet to another. The data above
    represents position number in my organization and some positions start with
    the alpha characters and some with just numeric numbers. I formatted the
    columns as "text" prior to importing but one spreadsheet imports that column
    as "number" (even though I formatting it as text. and the other comes in as
    text--so I get a type mismatch and the import deletes records.
     
  2. Loading...

    Similar Threads Forum Date
    Difficult Areas Of Turban And How To Remedy It ? Is A Plastic Cup For Your Ears The Answer Health & Nutrition Jun 4, 2016
    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

  3. '69 Camaro

    '69 Camaro
    Expand Collapse
    Guest

    Hi, Veronica.

    The easiest way to get around Jet making the wrong guess about a column's
    data type is to use the Import Text Wizard, not the Import Spreadsheet Wizard.

    To do so, convert the spreadsheet to a CSV file (don't worry, the formatting
    will be lost anyway when importing the data into an Access table). Use
    Access's Import Text Wizard to import the CSV file into the database.
    Designate the appropriate column to be a text field, not a numerical field.
    When using this Wizard, save these settings as a new import specification so
    that it can be reused next time, and Access will always use the data types
    for each column that you've designated in this import specification.

    In case you're hesitant to take the extra time to convert the spreadsheet,
    importing text is _much_ faster than importing XLS files (the Wizard has to
    dig through a lot of formatting in the XLS file to dig out the data), so the
    more records imported, the more time will be saved by converting to CSV or
    TXT first.

    And if there are any errors while using the Import Text Wizard, Access gives
    an error message to the user about what failed and saves information about
    each record's inability to be imported in an Import_Errors table. If the
    Import Spreadsheet Wizard is used and something fails, that Wizard will give
    the fatal error message ". . . failed to import <fileName>," which leaves one
    completely in the dark as to what went wrong.

    HTH.
    Gunny

    See http://www.QBuilt.com for all your database needs.
    See http://www.Access.QBuilt.com for Microsoft Access tips.

    (Please remove ZERO_SPAM from my reply E-mail address so that a message will
    be forwarded to me.)
    - - -
    If my answer has helped you, please sign in and answer yes to the question
    "Did this post answer your question?" at the bottom of the message, which
    adds your question and the answers to the database of answers. Remember that
    questions answered the quickest are often from those who have a history of
    rewarding the contributors who have taken the time to answer questions
    correctly.


    "Veronica" wrote:

    > I have mixed data in a record (column) such as PA123 and 10234. When I
    > import two different worksheets into Access from Excel, I get type conversion
    > errors. What is the best was to format these columns to avoid errors. I use
    > Access to compare the data from one spreadsheet to another. The data above
    > represents position number in my organization and some positions start with
    > the alpha characters and some with just numeric numbers. I formatted the
    > columns as "text" prior to importing but one spreadsheet imports that column
    > as "number" (even though I formatting it as text. and the other comes in as
    > text--so I get a type mismatch and the import deletes records.
     
  4. Default User

    Default User
    Expand Collapse
    Guest

    underwent

    "Veronica" <Veronica@discussions.microsoft.com> wrote in message
    news:BCDA7C95-136E-45F8-849F-4545212336A0@microsoft.com...
    >I have mixed data in a record (column) such as PA123 and 10234. When I
    > import two different worksheets into Access from Excel, I get type
    > conversion
    > errors. What is the best was to format these columns to avoid errors. I
    > use
    > Access to compare the data from one spreadsheet to another. The data
    > above
    > represents position number in my organization and some positions start
    > with
    > the alpha characters and some with just numeric numbers. I formatted the
    > columns as "text" prior to importing but one spreadsheet imports that
    > column
    > as "number" (even though I formatting it as text. and the other comes in
    > as
    > text--so I get a type mismatch and the import deletes records.
     
  5. Van T. Dinh

    Van T. Dinh
    Expand Collapse
    Guest

    You have mixed columns in Excel in your spreadsheets but all values in a
    Field in an Access Table must have the same data type.

    What I normally do with a mixed column is to create a calculated column in
    Excel using the spreadsheet function TEXT() to convert the mixed column to a
    column of Text data type (some values will contain only digit characters!).

    When I import the modified Excel spreadsheet, I import the calculated column
    and skip the original mixed column.

    --
    HTH
    Van T. Dinh
    MVP (Access)



    "Veronica" <Veronica@discussions.microsoft.com> wrote in message
    news:BCDA7C95-136E-45F8-849F-4545212336A0@microsoft.com...
    >I have mixed data in a record (column) such as PA123 and 10234. When I
    > import two different worksheets into Access from Excel, I get type
    > conversion
    > errors. What is the best was to format these columns to avoid errors. I
    > use
    > Access to compare the data from one spreadsheet to another. The data
    > above
    > represents position number in my organization and some positions start
    > with
    > the alpha characters and some with just numeric numbers. I formatted the
    > columns as "text" prior to importing but one spreadsheet imports that
    > column
    > as "number" (even though I formatting it as text. and the other comes in
    > as
    > text--so I get a type mismatch and the import deletes records.
     

Share This Page