98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Access Training and help » Import of excell data into Access table
Import of excell data into Access table
Resolved · Low Priority · Version 2003
Darren has attended:
Access Introduction course
Import of excell data into Access table
Please could you advise what the best way is to create an access database from data held within excell.
Thanks
RE: Import of excell data into Access table
Hi Darren, Thank you for the post, Welcome to the forum,
In answer to your question;
Prior to importing from Excel it is necessary to eliminate all of the formatting in the file. This includes, centering, bold, underlining, etc. Also, ensure that there are no spaces in front of the column headings. By eliminating the spaces, you prevent importing errors and column heading issues so Access can recognise the headings.
Below, I have listed a step-by-step guideline for creating an Access table using an Excel file.
Save then close the .xls file.
Next, in Access go to "New" on the "Tables" tab.
Click on "Import table."
Change the "Files of Type" to Microsoft Excel.
Navigate to the "Excel" file that you would like to import.
Select the worksheet or named ranges you would like to import.
In the bottom half of the "Import Spreadsheet Wizard" dialog box, you will see the information that will be imported.
Click "Next."
If you have put the column headings in the Excel file, they are transferred to Access. If the first row of your data contains the column headings, check the box "First Row Contains Column Headings". If your first row of information does not contain column headings, do not check the box.
Click "Next". You will see that the first row of your data is grey and will be the column headings for your table.
To create a new table based on the data you are importing, click on "In a New Table". If you would like to import the information into an existing table, click on "In an Existing Table" and use the drop-down box to select the table name.
Please note that if you choose to edit/add information to this new or existing table each time you open Access. Click the Link option. (If not, you will be taken straight to the last step of the wizard to click on "Finish.")
From the Link option Click "Next."
Select the indexing options for each field by clicking on the field heading to highlight the column then go to the "Indexed" field drop-down box and determine whether or not you would like to allow duplicate values (allows fields on different rows to contain the same information), to not allow duplicate values (fields on different rows must contain unique information), to not index the field.
Indexing the field allows the database to run faster. If you do not want to import a column, click on the column heading to highlight the entire column, then check "Do not import field (Skip)." You can adjust the data types after the table has been imported.
Click "Next."
At this point, decide if you want Access to add a primary key, if you want to choose your own primary key, or if you do not want a primary key. If Access creates a primary key, it will create an ID field which autonumbers your records. If you choose your own primary key, select a column that does not contain duplicate fields. For example, an invoice number field cannot contain invoice 1234 twice in the same column.
Click "Next."
Type the name of your table into the "Import to Table:" field. If you do not want to overwrite an existing table, choose a name that is not currently being used.
Click "Finish."
Once you have successfully imported the file, a dialog box will display that reads: "Finished importing file [filepath] to table [table name]."
You can go to the table
Training information:
See also:
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Access tip:Change caseTo change case from upper or lower case, highlight the text area, hold down the Shift key and pres F3 |