MS Access: Importing

MS Access is an excellent software to manage small data. Its powerful Reports and Query features enable skillful handling of the data.
Many a times you have your data in a format other than MS Access tables. For example, you are in the process of converting your older dBase Inventory Management System to MS Access. It would be preferable to use the existing data in the new system without having to re type it all.
With the Import Data feature of MS Access, data from an external source can be converted to a MS Access table format. The external source can be MS Excel, dBase, text file, SQL server, Visual FoxPro etc.

While importing data, the source file is left unchanged. A copy of the information is created in a new MS Access table. Data can be appended to an existing MS Access table only if the source is a spreadsheet or text file.

Text files come in two formats. A text file with a Delimited Format has characters like comma or tab separating each field.
A Fixed Width Format text file has data in columns of predetermined width.
Based on the type the import action varies. A wizard (a help feature) will guide you along.

Import a text file.

- Open a database where the data is to be stored.
- Click File from the Menu. Click Get External Data, then Import. Navigate to the text file to be imported and click Import to proceed.
- Select Delimited or Fixed Width file format and click Next.
- In case of Delimited format, select the correct delimiter and the Text Qualifier and click next.
- For fixed width file format, define the breaks and click next.
- Click on the appropriate radio button to choose whether the data is to be imported in a new or existing table.
- If you choose Existing Table, select the name of that table from the list supplied.
- If the aligning fields of the source and destination do not match, an error message will appear. The data will be imported to a Paste Error Table.
- If this import operation causes a duplication of Primary key fields, again an error message will appear and the import will be aborted.
- If data is imported to a new table, you are prompted to supply information about each of the fields being imported. Click on each column to display and alter the default values for Field name, Data type, Indexing and Skip Field Flag.
- Next, the user is prompted for the Primary Key for the new table. A primary key is a combination of one or more fields that uniquely identifies each record. The user has an option to define a Primary Key, do not define a Primary Key or let MS Access define a Primary Key. In the last case, an auto number field is added to the table as ID.

The import of a delimited text file is completed successfully.

Import a fixed width text file

The wizard chooses the various field breaks but the user can arrange them too. When the various columns have be modified as per requirement, click Next

If you would like to make a comment, please fill out the form below.