Importing Spreadsheet Data

Access 2010 also allows you to import data from spreadsheet files created by Excel version 5 and later. You can specify a portion of a spreadsheet or the entire spreadsheet file to import into a new table or to append to an existing table. If the first row of cells contains names suitable for field names in the resulting Access table, you can tell Access to use these names for your fields.

Preparing a Spreadsheet

Access 2010 determines the data type for the fields in a new table based on the values it finds in the first few rows of data being imported (excluding the first row if that row contains field names). When you import a spreadsheet into a new table, Access stores alphanumeric data as the Text data type with an entry length of 255 characters, numeric data as the Number type with the Field Size property set to Double, numeric data with currency formatting as the Currency type, and any date or time data as the Date/Time type. If Access finds a mixture of data in any column in the first few rows, it imports that column as the Text data type.

If you want to append all or part of a spreadsheet to a target table, you should import or link the entire spreadsheet as a new table and then use an append query to edit the data and move it to the table you want to update.

If the first several rows are not representative of all the data in your spreadsheet (excluding a potential field names row), you might want to insert a single "dummy" row at the beginning of your spreadsheet with data values that establish the data type you want to use for each column. You can easily delete that row from the table after you import the spreadsheet.

Because Access sees only numbers in the first few rows of the Zip column, it will use a Number data type for the Zip field. However, the entry for the Canadian address has letters and spaces, which requires the field to be defined as text. As you'll see later, if you attempt to import this spreadsheet without fixing this problem, Access generates an error for each row that contains nonnumeric data. Access sets the contents of fields it cannot import to Null. You can solve this by inserting a dummy row at the top with the proper data types in each column, moving the row to the top, or fixing the one bad row after you import the file.