Menu
Option 3: Import directly from Excel. Another great option is to import data right from within Excel. This is what you have to do: In Excel spreadsheet, select the data range and Format it as a Table; While still in Excel, highlight the table, then choose Export from the top ribbon, then Export Table to SharePoint list.
Clean up your Excel spreadsheet before importing it into Access. It will make it easier if you do a few simple steps before you import the Excel spreadsheet into Access. The key is that your data must be consistent between imported spreadsheets.[3]
- It's a good idea to make sure that the first row in the Excel spreadsheet contains your column headers (or field names), and that these are very clear and easy to understand.[4] For example, for a column containing people's last names, you may want to call the column header/field name 'last name.' Be clear and precise because it will make it easier when you try to match column headers in one Excel sheet with another.
- Access allows you to link common fields between two or among many spreadsheets. Let's say you have an Excel sheet that contains payroll information. It includes people's first and last names, addresses, and salaries. Let's say for the sake of argument that you want to match that sheet within Access to a second Excel sheet that contains information about campaign finance contributions. This second sheet contains people's names, addresses, and donations. What Access allows you to do is to match different column headers with one another. You could link name headers to see which people of the same name appear in both databases, for example.
- Scan the Excel sheets to make sure that each type of data is handled the same way, and clean it up before importing it into Access.[5] It must be what Access calls 'relational.' For example, if the hypothetical spreadsheet for payroll contained first name, last name and middle initial in one column, but the second spreadsheet contained only first name and last name in separate columns, Access will register this as no match. There needs to be matching column headers/fields.