Get Help

Query CSV and Excel data with DuckDB SQL

Csv@2 and Excel@2 load your files into an in-memory DuckDB database, so a test query is full SQL — this is what it looks like.

Csv@2 and Excel@2 do not query your files directly: they load them into an in-memory DuckDB database and run the queries there. Every worksheet, and every CSV file, becomes a table named "<data source name>"."<sheet or file name>" — Aero.DIM_PLANES for a sheet DIM_PLANES of a data source Aero. The rest is DuckDB’s SQL dialect, and almost everything you are used to from a database works. The examples below cover what tests need most; the complete reference is the DuckDB SQL documentation.

Basic SELECT

If you need just the data as is, get them using the usual syntax:

SELECT    *
FROM      Aero.DIM_PLANES -- DataSourceName.SheetName
ORDER BY  PLANE_ID

Don’t forget sets match expectation requires ordered sets, include ORDER BY if you use that.

You can use all common stuff you are used to, such as WHERE, GROUP BY, ORDER BY, HAVING, WITH (for common table expressions) etc. You can use expressions, logical operators, …

Case sensitivity, naming

Names of tables, columns, functions are case insensitive, keywords like SELECT, FROM are also case insensitive.

Both name of a data source and name of an MS Excel sheet (or a CSV file) can contain spaces and non-Unicode characters. This is not a problem, but you need to enclose the names in double-quotes:

SELECT    ID, FirstName, LastName, " Some weird column Name 123"
FROM      "Aero data"."Person Incremental Load"

In the example above, your data source Aero data has space in name. The same for the sheet (Person Incremental Load) and column name in the sheet (the last one). Excel@2 can also normalize the names for you — see its Normalize column names and Normalize table names settings.

Limit number of returned rows

You can use LIMIT (and even OFFSET if needed) to retrieve only part of the result set:

SELECT    FLIGHT_NUMBER
FROM      Aero.FACT_DEPARTURES
WHERE     Passengers <= 10
LIMIT     1

(Similar as SELECT TOP in MS SQL server.) This is useful for optimizing the queries.

Tip

FROM clause is optional. You can leverage this for easily declaring what data you expect to get:

Tests:
- Name:           Overbooked flights
  Description:    We want to ignore 5 known problems that occurred, but fail if others will occur
  First data source: Aero
  First query:    SELECT COUNT(*), MAX(YEAR(DATE_OF_FLIGHT)) FROM FACT.DEPARTURES WHERE Passengers > 200
  Second data source: Aero
  Second query:   SELECT 5, 2014
  Expectation:    sets match

JOINs and set operations

Joining tables

Use standard JOIN syntax:

SELECT  COUNT(*)
FROM    Aero.DIM_DESTINATIONS AS dd
        JOIN Aero.FACT_DEPARTURES AS fd ON dd.DESTINATION_ID = fd.DESTINATION_ID
WHERE   dd.DESTINATION_CITY = 'Zurich'

You can even use a simpler syntax for JOIN if the column names are equal and the values are required to be equal:

SELECT  COUNT(*)
FROM    Aero.DIM_DESTINATIONS AS dd
        JOIN Aero.FACT_DEPARTURES AS fd USING(DESTINATION_ID)
WHERE   dd.DESTINATION_CITY = 'Zurich'

All kinds are supported: INNER, LEFT, RIGHT, FULL, CROSS.

But: have you heard of conditional JOINs, SEMI and ANTI JOINS, positional JOINs, lateral JOINs, As-Of JOINs? Well, you might not need them when testing MS Excel data probably, but all are supported.

See details here: https://duckdb.org/docs/stable/sql/query_syntax/from.

Set operations

All common operations are supported: UNION, UNION ALL, INTERSECT, EXCEPT.

You can also use UNION [ALL] BY NAME to union the sets based on column name, instead of position.

See details here: https://duckdb.org/docs/stable/sql/query_syntax/setops.

Expressions

Sometimes you need to not only retrieve the values, you might need to adjust them in order to get test result. Just try what you are used to, depending on how far your database’s dialect is far from the standard, majority will work.

CASE WHEN

Examples of supported expressions (excerpts):

SELECT CASE g.GATE_TYPE
          WHEN 'Jet Bridge Gate' THEN 1000
          WHEN 'Bus Gate' THEN 2000
          ELSE  0
       END
FROM ....

Other standard forms of CASE expressions are also supported.

Casting

MS Excel data will more than often come with errors. One of problems might be data types, as MS Excel does not enforce them. What if you got text column instead of expected integer data type? Do you want to identify wrong records?

Casting functions will be your best friends in such situations:

SELECT CAST(PLANE_CAPACITY AS VARCHAR) FROM Aero.DIM_PLANES;

SELECT PLANE_CAPACITY::SMALLINT FROM Aero.DIM_PLANES;

SELECT TRY_CAST('two airplanes' AS INTEGER); -- returns NULL

See:

Other

All “intuitive” stuff works as expected, such as

  • logical operators: >, <, >=, <=, <>, !=, =

  • AND, OR, NOT, IS NULL, IS NOT NULL

  • parentheses for precedence handling

  • IN operator: GATE_NUMBER IN (1, 2, 3, 9)

  • BETWEEN: GATE_NUMBER BETWEEN 4 and 8

  • LIKE

  • collations

  • subqueries

  • …

See? It is harder to find what is not implemented…

Details: https://duckdb.org/docs/stable/sql/expressions/overview

Functions

Text functions

So were tasked to check, whether all Last Name column values in the MS Excel sheet start with a capital letter, right? Text functions are a necessity for such situation.

SELECT
  concat('Testing ', 'with ', 'CAT ', 'is ', 'cool ') as ConcatStrings,
  contains('Testing with CAT is cool.', 'cool') as SearchForString,
  format ('Testing with {} is {}', 'CAT', 'cool') as FormatStrings,
  lower('CAT') as LowerCase, -- returns cat
  upper('cat') as UpperCase, -- returns CAT
  left('CAT', 2) as LeftPartOfString, -- returns CA
  right('CAT', 2) as RightPartOfString, -- returns AT
  length('CAT') as LengthOfString, -- returns 3
  position('Testing with CAT is cool', 'with') as FirstPositionOfString, -- returns 9, if not found 0
  trim(' CAT is cool  ') as TrimmedString

The example extracts only the most common functions. There are also many regular expression functions and lots of others text functions.

Details: https://duckdb.org/docs/stable/sql/functions/text

Date and time functions

MS Excel data may contain date or time or datetime values. Some useful functions:

SELECT  current_date,
        datediff('hour', DEPARTURE_TIME, LANDING_TIME),
        datepart('year', DAY_OF_FLIGHT),
        dayname(DEPARTURE_DATE), -- Sunday, Monday, ...
        least(DEPARTURE_TIME, LANDING_TIME), -- lower of the two dates
        greatest(DEPARTURE_TIME, LANDING_TIME), -- bigger of the two dates
        make_date(2000, 1, 1), -- creates a date from parts,
        monthname(DEPARTURE_DATE) -- January, February, ...
FROM    FACT.DEPARTURES

Complete reference: https://duckdb.org/docs/stable/sql/functions/date.

Hopefully the examples might get you going without digging too much into details. If not, feel free to find in DuckDB documentation details for whatever function you need:

https://duckdb.org/docs/stable/sql/functions/overview

  • Csv@2 and Excel@2 — the providers: connection string, settings, prerequisites.
← Test CSV and Excel files
CAT in Azure DevOps Pipelines →