Importing Excel spreadsheet into Access - data type problem

Discussion in 'Microsoft Access External Data' started by Andrew Good, Aug 26, 2004.

  1. Andrew Good

    Andrew Good Guest

    A number of users here at the University of Plymouth have
    reported to me a problem when they import Excel
    spreadsheets into Access. It does not allow them to
    specify the data types of the fields to be imported. I
    have tried this myself and have encountered the same
    problem.

    Versions are:
    Windows XP Professional SP 1
    MS Office XP Professional SP 2. (The problem is not
    resolved by applying Office XP Pro SP 3.)

    Here's the recipe: Create an Excel spreadsheet, with
    column headings in its first row, and save it as
    test.xls. Exit from Excel.

    Start up Access and create a new, empty database.
    Choose File -> Get External Data -> Import...
    and select test.xls.

    The Import Spreadsheet Wizard starts up. Select Sheet1,
    check "First Row Contains Column Headings", and
    select "Store Data in a New Table."

    It now asks for information about each of the fields to
    be imported. But the Data Type is grayed out and cannot
    be changed from the Data Type that Access displays.

    Can anyone tell me why this is happening, and how to put
    it right?

    Andrew Good
    Applications Team
    University of Plymouth
     
    Andrew Good, Aug 26, 2004
    #1
    1. Advertisements

  2. Andrew Good

    Guest Guest

    Hi,
    sorry forgot something, when importing an Excel Spreadsheet into Access,
    Access analyses the first 15 (approx.) rows of data and determines on the
    found data the type of the field (numeric, text, date, etc.) - you are not
    able to change the type. If you save the file as .csv ot .txt and import it
    then you have more control of the type and are able to select it.

    Bernd

    "BerHav" wrote:

    > Hi Andrew,
    >
    > If you mean with Office XP Office 2003 then it is a well known bug, which
    > was not fixed yet by Microsoft.
    > The only thing what you can do is import to a table and append the new table
    > to another one where you have all fields defined the way you want them to be.
    >
    > Regards,
    > Bernd
    >
    > "Andrew Good" wrote:
    >
    > > A number of users here at the University of Plymouth have
    > > reported to me a problem when they import Excel
    > > spreadsheets into Access. It does not allow them to
    > > specify the data types of the fields to be imported. I
    > > have tried this myself and have encountered the same
    > > problem.
    > >
    > > Versions are:
    > > Windows XP Professional SP 1
    > > MS Office XP Professional SP 2. (The problem is not
    > > resolved by applying Office XP Pro SP 3.)
    > >
    > > Here's the recipe: Create an Excel spreadsheet, with
    > > column headings in its first row, and save it as
    > > test.xls. Exit from Excel.
    > >
    > > Start up Access and create a new, empty database.
    > > Choose File -> Get External Data -> Import...
    > > and select test.xls.
    > >
    > > The Import Spreadsheet Wizard starts up. Select Sheet1,
    > > check "First Row Contains Column Headings", and
    > > select "Store Data in a New Table."
    > >
    > > It now asks for information about each of the fields to
    > > be imported. But the Data Type is grayed out and cannot
    > > be changed from the Data Type that Access displays.
    > >
    > > Can anyone tell me why this is happening, and how to put
    > > it right?
    > >
    > > Andrew Good
    > > Applications Team
    > > University of Plymouth
    > >
    > >
    > >
    > >
     
    Guest, Aug 26, 2004
    #2
    1. Advertisements

  3. Andrew Good

    John Nurick Guest

    Hi Andrew,

    This is normal. The Access routine that imports Excel data doesn't allow
    direct control over the types of the fields it creates, and often runs
    into trouble with Excel columns that contain a mix of numeric and text
    values.

    You can work round this in any of the following ways:

    1) create the table yourself with the field types you need, then import
    the spreadsheet data. The field names in the table must exactly match
    the column headings in Excel.

    2) make sure that at least one row near the top of the Excel table
    contains values that can only be interpreted as the data types you need
    (e.g. text that cannot be interpreted as a number if you want the column
    to become a text field). Sometimes the simplest way to do this is to
    insert a first row of "dummy" data into Excel just for this, and then
    delete it from the Access table once the data has been imported.

    3) Access assigns field types on the basis of the data it finds in the
    first dozen or so rows of the spreadsheet table. It pays no attention to
    cell formats. Sometimes a useful trick is to put an apostrophe ' in
    front of numeric values in thecells (e.g. '999): this forces Excel and
    Access to treat them as text, but the apostrophe is not displayed in
    Excel or imported into Access.

    (Just to make things more confusing, Access applies different rules
    when you're linking Excel data rather than importing it. Simplifying
    somewhat: when importing, any text value in the first few rows will
    cause a field to be imported as text. When linking, any *numeric* value
    in the first few rows will cause a field to be linked as numeric even if
    all the other values are non-numeric.)

    3) Write your own import code using Automation to get the values direct
    from the worksheet cells and recordset operations or queries to append
    them into your table.



    Sub AddApostrophes()
    Dim C As Excel.Range
    For Each C In Application.Selection.Cells
    If IsNumeric(C.Formula) Then
    C.Formula = "'" & C.Formula
    End If
    Next
    End Sub

    Sub RemoveApostrophes()
    Dim C As Excel.Range
    For Each C In Application.Selection.Cells
    C.Formula = C.Formula
    Next
    End Sub


    On Thu, 26 Aug 2004 03:51:49 -0700, "Andrew Good"
    <> wrote:

    >A number of users here at the University of Plymouth have
    >reported to me a problem when they import Excel
    >spreadsheets into Access. It does not allow them to
    >specify the data types of the fields to be imported. I
    >have tried this myself and have encountered the same
    >problem.
    >
    >Versions are:
    >Windows XP Professional SP 1
    >MS Office XP Professional SP 2. (The problem is not
    >resolved by applying Office XP Pro SP 3.)
    >
    >Here's the recipe: Create an Excel spreadsheet, with
    >column headings in its first row, and save it as
    >test.xls. Exit from Excel.
    >
    >Start up Access and create a new, empty database.
    >Choose File -> Get External Data -> Import...
    >and select test.xls.
    >
    >The Import Spreadsheet Wizard starts up. Select Sheet1,
    >check "First Row Contains Column Headings", and
    >select "Store Data in a New Table."
    >
    >It now asks for information about each of the fields to
    >be imported. But the Data Type is grayed out and cannot
    >be changed from the Data Type that Access displays.
    >
    >Can anyone tell me why this is happening, and how to put
    >it right?
    >
    >Andrew Good
    >Applications Team
    >University of Plymouth
    >
    >


    --
    John Nurick [Microsoft Access MVP]

    Please respond in the newgroup and not by email.
     
    John Nurick, Aug 26, 2004
    #3
  4. John Nurick <> wrote ...

    > Access assigns field types...


    No, this is a Jet process.

    > ...on the basis of the data it finds in the
    > first dozen or so rows of the spreadsheet table.


    This is determined by a Jet registry key which, if set to zero, scans
    all rows. For more details see:

    http://www.dicks-blog.com/excel/2004/06/external_data_m.html

    > It pays no attention to
    > cell formats.


    That is incorrect. As proof, create an Excel workbook containing a
    single cell formula

    =38000

    Change the cell format to (custom) dd mmm yyyy. Include the column in
    a query e.g.

    SELECT F1
    FROM [Excel 8.0;HDR=No;C:\Tempo\db.xls;].[Sheet1$]
    ;

    The value appears as 14 JAN 2004 (in local format) and, using ADO's
    OpenSchema method, the column is show to have been determined as
    adDate ('a date value').

    Jamie.

    --
     
    Jamie Collins, Aug 27, 2004
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Deborah
    Replies:
    1
    Views:
    778
    Ken Snell
    Jul 30, 2003
  2. Alex Saager

    Importing an excel spreadsheet into Access

    Alex Saager, May 28, 2004, in forum: Microsoft Access External Data
    Replies:
    1
    Views:
    172
    Ken Snell
    May 28, 2004
  3. Guest

    Preserving Excel Formulas while importing spreadsheet into Access

    Guest, Jan 24, 2006, in forum: Microsoft Access External Data
    Replies:
    3
    Views:
    1,637
    Douglas J. Steele
    Jan 26, 2006
  4. Guest

    Importing an Excel spreadsheet into Access 2002

    Guest, Sep 13, 2006, in forum: Microsoft Access External Data
    Replies:
    1
    Views:
    186
    Guest
    Sep 13, 2006
  5. Cillacil
    Replies:
    0
    Views:
    354
    Cillacil
    Apr 3, 2009
Loading...

Share This Page