← VCE Data Analytics U3O1

Exporting query results for use in a spreadsheet

Save the rows a query returns as a CSV file with a header row of field names. Import it into a spreadsheet, then check the columns came across correctly before further analysis.

Part of: VCE Data Analytics - U3O1 - Data Analytics — Module 28, ACARA Digital Technologies Years 9–10 — Entities, Relationships and Queries — Module 51, WA Computer Science ATAR Year 11 — Data Management — Module 88

Understanding Exporting query results for use in a spreadsheet in VCE Data Analytics U3O1

What it does: One line of a CSV file is one record. Commas separate the field values, so 'Ava,120' is a student's name and points. ISNUMBER returns TRUE if the imported points value is stored as a number. This checks the column came across correctly before analysis. The header row holds the field names, which become the column headings in the spreadsheet. The exported file is a copy of the query results. Analysing it in a spreadsheet does not change the database. Each field in the query result becomes one column, so three fields give three columns. Check the headings, the number of columns and the data types before you start calculating or charting.

Common mistakes: The export must be in CSV format so a spreadsheet can import it. The line should be .mode csv. .mode html would write HTML tags instead of comma-separated values. .output sends the query results into a file. .read tries to run a file as commands, so no CSV would be created. It should be .output stock.csv.

Class: _____________________ · Name: _____________________ · Date: _________

Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.

🖨 Print worksheet + answer key Add Exporting query results for use in a spreadsheet to a class →

This module's key terms are also in a printable crossword (Modules 17–49) and word search (Modules 17–91).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning

Duo

Which file format is commonly used to export query results so a spreadsheet can open them?

CSV fileA SQL CREATE TABLE statement

Matches

Ava 120 Liam 95

↓

Values are separated by spaces, not commas, so a spreadsheet would put each whole line into a single column. Use Ava,120 and Liam,95.

Applying

Meanings

Term

Export query results as CSV

Meaning

Save the rows a query returns as a CSV file with a header row of field names. Import it into a spreadsheet, then check the columns came across correctly before further analysis.

Practice

SELECT firstName, lastName, house, points

Testing

Gaps

.mode

Check Answer

Spots

1.headers on

2.mode html

Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.

Students earn collectable badges

Over 100 of them, across seven natural biomes.

Study Buddy

Students build a real study resource

Turns their work into revision material ready for Flash Cards and Exam study.

Diamond Dash — one of Cloodit's live, competitive Sprint games

Students lock in with Sprints

Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.

A Cloodit certificate of achievement, ready to personalise and print

Custom printable certificates

Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.

Add to class →

Quick Questions

1. Which file format is commonly used to export query results so a spreadsheet can open them?

A. A SQL CREATE TABLE statement

B. CSV file ✓

A CSV file stores each row of the query result as a line of text with values separated by commas, so spreadsheets can import it easily.

2. What should the first line of an exported CSV file contain?

A. The SQL query that was run

B. A header row of field names ✓

The header row holds the field names, which become the column headings in the spreadsheet.

3. After exporting query results, what is the main reason to import them into a spreadsheet?

A. To analyse the data further with formulas, sorting and charts ✓

B. To change the original database tables automatically

The exported file is a copy of the query results. Analysing it in a spreadsheet does not change the database.

4. A query returns studentId, firstName and points. How many columns should the imported spreadsheet have?

A. 3 columns ✓

B. As many as the database has tables

Each field in the query result becomes one column, so three fields give three columns.

5. You import a CSV file and all the points values are left-aligned and will not add up. What does this suggest?

A. The numbers were imported as text and need checking ✓

B. The SUM function is not available in spreadsheets

Left-aligned numbers are often text. Checking the imported columns lets you spot and fix wrong data types before analysis.

Match the Pairs

SELECT firstName, points FROM students; → A query whose returned rows and columns can be exported as a CSV file for a spreadsheet

firstName,points → The header row of a CSV file, holding the field names that become the spreadsheet column headings

A CSV file stores each exported record on its own line, with the values separated by commas → Ava,120

After importing, check that the imported columns match the query fields and hold the right data type → =ISNUMBER(B2)

Ava 120 Liam 95 → Values are separated by spaces, not commas, so a spreadsheet would put each whole line into a single column. Use Ava,120 and Liam,95.

Ava,120 Liam,95 Mia,130 → The header row is missing. The file should start with firstName,points so the columns have headings in the spreadsheet.

Key Term

Export query results as CSV

Save the rows a query returns as a CSV file with a header row of field names. Import it into a spreadsheet, then check the columns came across correctly before further analysis.

Fill in the Blanks

1. .headers on .mode csv .output results.csv

Hint: Type the file format name the export mode should use.

2. .headers on .mode csv .output marks.csv

Hint: Type the setting name that writes the field names as the first row of the file.

3. .mode csv .output house_points.csv SELECT house, points FROM students;

Hint: Type the dot command that sends query results to a file instead of the screen.

4. firstName,points Ava,120 Liam,95

Hint: Type the second field name in the header row, matching the query column that holds the scores.

5. =COUNT(B2:B11)

Hint: Type the function that counts only numeric cells, to check the imported column came across as numbers.

Spot the Error

Question 1

A .headers on

B .mode html

C .output top.csv

D SELECT name, price

E FROM products;

Line B — The export must be in CSV format so a spreadsheet can import it. The line should be .mode csv. .mode html would write HTML tags instead of comma-separated values.

Question 2

A .headers on

B .mode csv

C .read stock.csv

D SELECT name, stock

E FROM products

F WHERE stock = 0;

Line C — .output sends the query results into a file. .read tries to run a file as commands, so no CSV would be created. It should be .output stock.csv.

Question 3

A .headers off

B .mode csv

C .output houses.csv

D SELECT firstName, house

E FROM students;

Line A — The CSV needs a header row of field names so the spreadsheet columns are labelled. It should be .headers on.

Question 4

A firstName,points

B Ava,120

C Liam;95

D Mia,110

Line C — A CSV file separates values with commas. A semicolon would leave 'Liam;95' in one column when imported. It should be Liam,95.

Question 5

A name,price,stock

B Pencil,1.5

C Notebook,4,80

Line B — Every data row needs one value for each header field. This row is missing the stock value, so the imported columns would not line up. It should be Pencil,1.5,200.

Student with a class code? Join your class →