Import Excel Data into Access

Introduction

• Purpose: Create a new database and import data into it using Microsoft Access.
• Key focus: Import data from Excel files into Access tables.

Creating a New Database

• Opening Access: Choose 'Blank Database' or 'New'.
• Naming the Database: Change default name if desired.
  • Example used: Named the database as 'my access demo'.
• Setting Default Folder: Can change from 'Documents' folder.
  • Example: Changed to 'Desktop'.
• Initial Setup: Closes the automatically created empty table (Table 1) to start with an empty database.

Importing Data from Excel Files

• Objective: Import data from three Excel files - customer master, vendor master, credit card transactions.
• Steps to Import Data:
  1. Go to 'External Data' > 'New Data Source' > 'From File' > 'Excel'.
  2. Browse to locate Excel files (stored on Moodle page, download first).
  3. Ensure Excel is closed before importing to avoid conflicts.
  4. Import data into a new table, ensuring first row is column headings.

Handling Data Types During Import

• Credit Card Numbers:
  • Recognized as double (suitable for large numbers like 16-digit credit card numbers).
• Types of Data:
  • Integer: Smaller numbers (e.g., ZIP codes).
  • Currency: Numeric with currency symbols and decimals.
  • Date with Time: Date/time data.
  • Short Text: Alphanumeric data.

Setting Primary Keys

• Definition: A unique identifier for each row in a table.
  • Must be unique (e.g., Social Security Number, Email, Student ID).
• Access Default: Assigns an ID column as primary key (auto-number).
• Choosing Own Primary Key: Select a column with unique identifiers (e.g., credit card number).

Import Process for Each File

1. Customer Master
   • Ensure column headings are correct.
   • Set credit card number as primary key.
2. Vendor Master
   • Verify data types (e.g., vendor number as double).
   • Choose vendor number as primary key.
3. Credit Card Transactions
   • Handle additional, possibly empty fields (field 7 & 8) found by Access.
   • Choose transaction ID as primary key.

Post-Import Adjustments

• Checking Imported Data:
  • Adjust column widths for readability (Excel-style adjustments in Access).
• Removing Unnecessary Columns:
  • Use design view to delete unwanted fields.
• Changing in Design View:
  • Alter field names, data types, and primary keys if needed.

Conclusion

• Successfully imported and adjusted three Excel files into Access.
• Next steps: Learn about creating an Entity Relationship Diagram (ERD) to connect tables.