Importing and Exporting Data in MS Access

Importing means bringing data from an external file or source into Microsoft Access. Depending on the source and import options, Access can create a new table or add data to an existing table.

Examples of Importing

• Import student records from an Excel workbook.
• Import a CSV file containing customer details.
• Import tables from another Access database.
• Import data from a supported text file.

Exporting means sending data from Microsoft Access to another file format or destination. It is useful when information needs to be shared, analyzed, printed, or used by another application.

Examples of Exporting

• Export student records to Microsoft Excel.
• Export a table or query to a CSV or text file.
• Export a report to PDF.
• Export an Access object to another supported format.

Follow these steps to import data from Excel:

1. Open your Microsoft Access database.
2. Open the External Data tab.
3. Choose the Excel import option.
4. Browse and select the Excel workbook.
5. Choose whether to import the source data into a new table, append it to an existing table, or link to the source, depending on the available options.
6. Select the worksheet or named range containing the data.
7. If prompted, indicate whether the first row contains column headings.
8. Review the field names and data types.
9. Set a primary key if appropriate and available during the import process.
10. Complete the wizard and review the imported records.

Example Excel Data

After importing, open the destination table and verify that the records and data types are correct.

A CSV file stores tabular data as text, commonly separating values with commas. It can be created by spreadsheet applications and many other systems.

Example: students.csv

StudentID,StudentName,Course
101,Rahul Kumar,ADCA
102,Priya Singh,Tally Prime
103,Amit Kumar,Web Development

Import Steps

1. Open the Access database.
2. Go to the External Data tab.
3. Select the Text File import option.
4. Browse to the CSV or text file.
5. Choose to import the data into a new table, append it to an existing table, or link to the source where supported.
6. Choose Delimited if values are separated by commas or another delimiter.
7. Confirm the delimiter and whether the first row contains field names.
8. Review the field definitions and complete the wizard.

You can reuse objects from another Access database by importing supported objects such as tables, queries, forms, reports, macros, and modules, depending on the source and import options.

Steps

1. Open the destination Access database.
2. Go to the External Data tab.
3. Choose the option to import from an Access database.
4. Browse to the source database file.
5. Select the objects you want to import.
6. Complete the import process.
7. Check the imported objects and their relationships.

This is useful when moving selected tables or other database objects into a new database.

Linking creates a connection to data stored outside the current Access database. Access displays the linked data without importing a separate copy of all the source records into a local table.

Importing vs Linking

The ability to edit linked data depends on the source, permissions, and type of connection.

Follow these steps to export a table:

1. Open the Access database.
2. Select the table in the Navigation Pane.
3. Open the External Data tab.
4. Choose the Excel export option.
5. Select the destination file name and location.
6. Choose any available export options.
7. Complete the export wizard.
8. Open the Excel file and verify the exported data.

CSV and text exports are useful when data needs to be transferred to another system or processed by a program.

Steps

1. Select the table or query you want to export.
2. Open the External Data tab.
3. Choose the Text File export option.
4. Specify the output file name and location.
5. Choose delimited or fixed-width formatting when prompted.
6. Set the delimiter and text qualifier if required.
7. Complete the export process.

Example CSV Output

StudentID,StudentName,Course
101,Rahul Kumar,ADCA
102,Priya Singh,Tally Prime
103,Amit Kumar,Web Development

CSV files generally store values as text and do not preserve all Access features, such as relationships, forms, or report formatting.

Exporting a report to PDF is useful for sharing a formatted document that is easy to view and print.

Steps

1. Open the report in Access.
2. Review the report in Print Preview or an appropriate view.
3. Open the External Data tab or use the available PDF/XPS export command.
4. Choose PDF as the output format.
5. Choose a file name and destination.
6. Export the report and open the resulting PDF to check it.

1. Create an Excel workbook containing at least five student records.
2. Import the Excel data into a new Access table.
3. Check the field names and data types.
4. Create a CSV file and import it into Access.
5. Export a table to Excel.
6. Export a query result to a CSV file.
7. Create a report and export it to PDF.
8. Compare importing and linking external data.
9. Check each exported file by opening it in the appropriate application.

1. What is importing data?
2. What is exporting data?
3. How can you import an Excel workbook into Access?
4. What is a CSV file?
5. What is the difference between importing and linking?
6. How can you export an Access table to Excel?
7. Why might you export a report to PDF?
8. What causes CSV data to appear in one column?
9. Does a CSV file preserve Access relationships and forms?
10. What should you verify after importing data?