Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Importing Excel - Number as Text

    I am trying to import an Excel file that contains two columns. The second column is all numeric, and is not giving me any trouble, but the first is a mix of all-numeric and alpha-numeric fields. Access imports this column as a number field, erroring on all other field types being imported. How do I force Access to import this column into a text field? I've tried linking to the file, and the macro command TransferSpreadsheet as well.

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Excel - Number as Text

    Hi David,
    It seems that Access looks at the top few values in the Excel column to determine what the data type is for the field. This might be a case where you need to use ADO with ODBC to transfer the data.

    If it is only a one-time thing, you could possibly sort your Excel columns so that a text value is in the row beneath the field name. That way Access would be more likely to deem that field a text field rather than numeric.

    HTH <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Importing Excel - Number as Text

    The simplest method is to create the target table first and make the target field a text field. I've never had any problem importing numbers into a text field, only the other way around. The problem is really an Excel problem, since it refuses to recognize numbers as text unless you put a single quote at the first position in the cell.
    Charlotte

  4. #4
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Excel - Number as Text

    Chiming in with my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>, if it's a one time deal, I've found the easiest thing to do is insert a row directly under the column headings in the Excel file and fill the columns with the value "ZZZ" for that row; it forces Access to recognize the columns as text, and the record is easily recognized and deleted once it's in Access.

    If anyone from Redmond is reading, though the functionality of Access guessing at the data type in an Excel import is handy, ultimate discretion should be with the user/developer.
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  5. #5
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Excel - Number as Text

    I also use this approach.
    If it sees an text value in the top row of your column, it automatically assigns it a text value as it's default datatype.
    A fairly easy fix for me.

  6. #6
    2 Star Lounger
    Join Date
    May 2001
    Location
    Wichita, Kansas, USA
    Posts
    177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Excel - Number as Text

    I've also had luck by saving the Excel spreadsheet as delimited text, and then importing the delimited text file to Access, because then it will come up with the screen options of assigning (manually) each data type for each column/field. Pribb

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Importing Excel - Number as Text

    From all these good suggestions, this one seems to offer the best solution to me. A query can strip the first "dummy" record easily, and the rest manipulated at will.

    As a side thought -- does anyone know if MS does monitor these boards?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •