Be able to import data from existing files (including .csv, .txt) using specified field names to create tables

Topic 18 – Databases

Learning Objective

Be able to import data from existing files (including .csv and .txt) using specified field names to create tables.

1. Understanding Source Files

• CSV (Comma‑Separated \cdot alues) – plain‑text file where each record is on a new line and fields are separated by commas (or another delimiter).
• TXT (Text) – can be delimited by commas, tabs, spaces, or a custom character; sometimes used for fixed‑width fields.
• Header row – optional first line that contains field names. If absent, you must supply names during import.

2. Preparing the File Before Import

1. Open the file in a plain‑text editor (e.g., Notepad) or spreadsheet program.
2. Check that each record has the same number of fields.
3. Ensure that text fields containing the delimiter are enclosed in quotation marks (e.g., "Smith, John").
4. If a header row is missing, create one with the required field names, e.g.:

   StudentID,FirstName,LastName,Score

5. Save the file using UTF‑8 encoding to avoid character‑set problems.

3. Common Delimiters and Their Uses

4. Import Process – General Steps (Applicable to Access, LibreOffice Base, MySQL Workbench)

1. Start the Import Wizard – Choose External Data → Import or similar command.
2. Select the source file – Browse to the .csv or .txt file.
3. Specify the file format:

   • Delimiter character (comma, tab, pipe, etc.).
   • Text qualifier (usually double quotes).
   • Whether the first row contains field names.

4. Map fields to database columns:

   • If the file has a header row, the wizard will display those names.
   • Otherwise, you will be prompted to type each field name.
   • Adjust names to follow database naming rules (no spaces, start with a letter, avoid reserved words).

5. Define data types for each column (e.g., INTEGER, TEXT, DATE):

   • Numeric fields → INTEGER or DECIMAL.
   • Date fields → specify the expected format (e.g., DD/MM/YYYY).
   • Long text → MEMO or VARCHAR with appropriate length.

6. Set primary key (optional) – Choose a field that uniquely identifies each record, or let the system create an auto‑number field.
7. Review a preview – The wizard shows the first few rows as they will appear in the table.
8. Complete the import – Click Finish. The new table appears in the database window.

5. Specifying Field Names During Import

If the source file does not contain a header row, you must provide field names manually. Follow these guidelines:

• Use concise, meaningful names (e.g., StudentID, FirstName, Score).
• Do not include spaces; use camelCase or underscores.
• Avoid reserved words such as SELECT, TABLE, DATE.
• Keep the order identical to the column order in the file.

6. Common Issues and Troubleshooting

7. Example: Importing a Student‑Score CS \cdot File

Suppose we have studentscores.csv with the following content (no header row):

001,John,Doe,78
002,Jane,Smith,85
003,Emily,Jones,92

Steps to import:

1. Open the database program and start the Import Wizard.
2. Choose the file studentscores.csv.
3. Set delimiter to comma and indicate that the first row does not contain field names.
4. Enter field names in order:

   • StudentID – INTEGER
   • FirstName – TEXT (length 30)
   • LastName – TEXT (length 30)
   • Score – INTEGER

5. Set StudentID as the primary key.
6. Review the preview, then finish the import.

8. Summary Checklist

• File is plain text and saved in UTF‑8.
• All records have the same number of fields.
• Delimiter and text qualifier are correctly identified.
• Header row present or field names supplied.
• Data types assigned appropriately.
• Primary key chosen to avoid duplicate errors.