Lesson 5: Join a spreadsheet

Complexity: intermediate
Outcome: a checked join between the observations layer and a CSV table, plus a permanent observations_with_status layer in the course GeoPackage.

Before you begin

Complete Lesson 4 first.

You need:

• 03_project/qgis-course.qgz
• 02_working/course.gpkg
• the observations layer containing OBS-001, OBS-002 and OBS-003
• a plain-text editor such as Notepad or TextEdit in plain-text mode

1. Create the CSV exactly as shown

1. Open your plain-text editor.
2. Create a new blank file.
3. Copy these four lines exactly:

obs_id,status,checked_date
OBS-001,checked,2026-08-01
OBS-002,follow_up,2026-08-02
OBS-999,unmatched_test,2026-08-03

4. Choose File → Save As….
5. Browse to qgis-course/01_source.
6. Save the file as:

observation-status.csv

7. If the editor offers an encoding choice, choose UTF-8.
8. If using Windows Notepad, choose All files rather than Text Documents if needed so the file is not saved as observation-status.csv.txt.
9. Close the text editor.
10. In File Explorer or Finder, confirm the final filename ends in .csv.

You should now have

01_source/observation-status.csv

2. Open the course project

1. Start QGIS.
2. Choose Project → Open….
3. Open 03_project/qgis-course.qgz.
4. Confirm observations is in the Layers panel.
5. Right-click observations and choose Open Attribute Table.
6. Confirm OBS-001, OBS-002 and OBS-003 each appear exactly once in obs_id.
7. Close the attribute table.

If those IDs are missing or duplicated, fix Lesson 4 before continuing. A join is only as reliable as the key used to connect the records.

3. Add the CSV as a non-spatial table

1. Choose Layer → Add Layer → Add Delimited Text Layer….
2. Beside File name, click ….
3. Browse to qgis-course/01_source.
4. Select observation-status.csv.
5. Click Open.
6. Check the data preview. You should see three columns: obs_id, status, checked_date.
7. Under Geometry definition, choose No geometry (attribute only table).
8. Click Add.
9. Click Close.
10. In the Layers panel, find the newly added table.
11. Right-click it and choose Rename Layer.
12. Rename it:

observation_status

13. Save the QGIS project.

A non-spatial table appears in the Layers panel but has nothing to draw on the map. That is expected.

4. Inspect the table and key field

1. Right-click observation_status.
2. Choose Open Attribute Table.
3. Confirm there are exactly three rows.
4. Confirm the obs_id values are:

OBS-001
OBS-002
OBS-999

5. Confirm OBS-999 is present. It is deliberately unmatched.
6. Close the table.

5. Create a live join

1. Double-click observations in the Layers panel.
2. Choose Joins in the left-hand list.
3. Click the green + button to add a join.
4. In Join layer, choose observation_status.
5. In Join field, choose obs_id.
6. In Target field, choose obs_id.
7. If a custom field-name prefix is offered, set it to:

status_

8. Leave the remaining options at their defaults for this exercise.
9. Click OK to close the Add Vector Join dialog.
10. Click Apply in Layer Properties.
11. Click OK.
12. Save the QGIS project.

The join is now part of the QGIS project configuration. It has not rewritten the original observations table.

6. Inspect the joined result

1. Right-click observations.
2. Choose Open Attribute Table.
3. Scroll to the right until you find the joined fields. With the prefix above, they should begin with status_.
4. Find the row OBS-001.
5. Confirm the joined status is checked.
6. Find OBS-002.
7. Confirm the joined status is follow_up.
8. Find OBS-003.
9. Confirm the joined status fields are null/empty because the CSV contains no OBS-003 row.
10. Close the table.

You should now understand two different unmatched cases

• OBS-003 has geometry but no matching CSV row
• OBS-999 exists in the CSV but has no matching geometry

Neither should be silently mistaken for a successful match.

7. Check the unmatched CSV row explicitly

1. Right-click observation_status.
2. Open its attribute table.
3. Find OBS-999.
4. Confirm no OBS-999 feature exists in the observations table.
5. Close the CSV table.

Record in 03_project/README.md:

Lesson 5 join check
Target key: observations.obs_id
Join key: observation_status.obs_id
Matched: OBS-001, OBS-002
Target without table match: OBS-003
Table row without geometry match: OBS-999

Save the README.

8. Test why duplicate join keys are a problem

Do this in a separate test file. Do not alter the original source CSV.

1. In File Explorer or Finder copy 01_source/observation-status.csv.
2. Rename the copy:

observation-status-duplicate-test.csv

3. Open the copy in a text editor.
4. Add this line at the end:

OBS-001,second_status,2026-08-04

5. Save and close the file.
6. Add the duplicate-test CSV to QGIS as No geometry (attribute only table) using the same method as step 3.
7. Open its attribute table.
8. Confirm OBS-001 now appears twice.
9. Close the table.

A simple one-to-one layer join cannot faithfully represent two status records for one spatial feature. In a real project, repeated visits or observations belong in a related child table rather than being flattened into a one-to-one join.

10. Right-click the duplicate-test table in the Layers panel.
11. Choose Remove Layer….
12. Confirm removal. This removes it from the QGIS project only; it does not delete the CSV from disk.
13. Save the project.

9. Open the Processing Toolbox

You will now create a permanent output rather than relying only on the live project join.

1. Choose Processing → Toolbox.
2. If the Toolbox opens as a panel, leave it open.
3. Click its search box.
4. Type:

Join attributes by field value

5. Double-click Join attributes by field value from the results.

10. Configure the durable join

In the Processing dialog:

1. Set the first/input layer to observations.
2. Set its join/key field to obs_id.
3. Set the second/input table to observation_status.
4. Set its join/key field to obs_id.
5. If there is an option for which table fields to copy, leave all fields selected for this exercise.
6. If a field prefix is offered, use:

status_

7. If an option says to discard non-matching input records, leave it off. You want OBS-003 retained so the missing match remains visible.
8. Find the main joined output destination.
9. Click its destination control and choose Save to GeoPackage….
10. Browse to 02_working/course.gpkg.
11. Set the output layer/table name to:

observations_with_status

12. If the algorithm offers a separate output for non-matching input features, save it to the same GeoPackage as:

observations_without_status_match

13. Click Run.
14. Wait until the log reports completion.
15. Click Close.
16. Save the QGIS project.

11. Verify the permanent output

1. Find observations_with_status in the Layers panel.
2. Right-click it and choose Open Attribute Table.
3. Confirm there are still three spatial rows.
4. Confirm obs_id still contains OBS-001, OBS-002 and OBS-003.
5. Confirm OBS-001 has status checked.
6. Confirm OBS-002 has status follow_up.
7. Confirm OBS-003 has no joined status.
8. Close the table.

The permanent output is now a GeoPackage layer. It no longer depends on the CSV join being active to retain the copied joined values.

12. Compare live join and durable output

1. Open the attribute table for original observations.
2. Observe the joined status_ fields supplied by the live join.
3. Close it.
4. Open observations_with_status.
5. Observe that the joined values are now physical fields in the saved output.
6. Close it.

The original layer remains the source feature layer. The new layer is a derived result. Do not quietly replace one with the other without recording the method.

13. Record the method

Add to 03_project/README.md:

Lesson 5 durable join
Input spatial layer: course.gpkg → observations
Input table: 01_source/observation-status.csv
Spatial key: obs_id
Table key: obs_id
Output: course.gpkg → observations_with_status
Unmatched spatial record retained: OBS-003
Unmatched table test row: OBS-999

Save the README and QGIS project.

14. Reopen and check

1. Close the QGIS project.
2. Reopen 03_project/qgis-course.qgz.
3. Confirm observations_with_status is present.
4. Open its attribute table.
5. Confirm the joined values remain after reopening.
6. Close the table.

Verify before moving on

You should be able to explain and show:

• where observation-status.csv is stored
• why it was loaded as No geometry
• the two fields used as join keys
• OBS-003 as a spatial feature with no table match
• OBS-999 as a table row with no spatial match
• why duplicate OBS-001 records are a one-to-many design issue
• the difference between a live QGIS join and observations_with_status
• obs_id preserved in the durable result

Common problems

Every joined field is null

Open both attribute tables and compare the key values character by character. Check field type, spelling, spaces and case. OBS-001, OBS-001 and obs-001 are not safe to assume are equivalent.

The CSV appears as points or asks for a CRS

Re-add it and choose No geometry (attribute only table). This file contains no coordinates.

observation-status.csv.txt will not import as expected

Rename the actual file so its extension is .csv. On Windows you may need to enable View → Show → File name extensions in File Explorer to see the real extension.

OBS-003 disappeared from the permanent output

Rerun Join attributes by field value and make sure you are not discarding non-matching input features.

Several table rows genuinely belong to one feature

Do not duplicate the geometry to make the join look successful. Use a one-to-many relation or a separate visits/observations table linked by the stable key.

Next: Lesson 6: Spatial analysis.

Official reference: QGIS joins and relations.

Test basis: QGIS 3.44 LTR. Last reviewed: 4 September 2026