CSV Database Client
qStudio includes csvjdbc. It allows easily querying CSV files as tables.
Features
CsvJdbc accepts all types of CSV files defined by RFC 4180.
CsvJdbc accepts only SQL SELECT queries from a single table and does not support INSERT, UPDATE, DELETE or CREATE statements.
SQL sub-queries are permitted but joins between tables in SQL SELECT queries are not yet supported.
SQL SELECT queries must be of the following format.
Each column is either a named column, *, a constant value, NULL, CURRENT_DATE, CURRENT_TIME, a sub-query, or an expression including functions, aggregate functions, operations +, -, /, *, %, ||, conditional CASE expressions and parentheses.
Supported comparisons in the optional WHERE clause are <, >, <=, >=, =, !=, <>, NOT, BETWEEN, LIKE, IS NULL, IN, EXISTS.
Use double quotes around table names or column names containing spaces or other special characters.
| Function | Description |
|---|---|
| ABS(N) | Returns absolute value of N |
| COALESCE(N1, N2, ...) | Returns first expression that is not NULL |
| DAYOFMONTH(D) | Extracts day of month from date or timestamp D (first day of month is 1) |
| HOUROFDAY(T) | Extracts hour of day from time or timestamp T |
| LENGTH(S) | Returns length of string |
| LOWER(S) | Converts string to lower case |
| LTRIM(S [, T]) | Removes leading characters from S that occur in T |
| MINUTE(T) | Extracts minute of hour from time or timestamp T |
| MONTH(D) | Extracts month from date or timestamp D (first month is 1) |
| NULLIF(X, Y) | Returns NULL if X and Y are equal, otherwise X |
| REPLACE(S, FROM, TO) | Replaces all occurrences of string FROM in S with TO |
| ROUND(N [, D]) | Rounds N to the specified number of decimal places D (0 by default) |
| RTRIM(S, [, T]) | Removes trailing characters from S that occur in T |
| SECOND(T) | Extracts seconds value from time or timestamp T |
| SUBSTRING(S, N [, L]) | Extracts substring from S starting at index N (counting from 1) with length L |
| TRIM(S, [, T]) | Removes leading and trailing characters from S that occur in T |
| UPPER(S) | Converts string to lower case |
| YEAR(D) | Extracts year from date or timestamp D |
For queries containing ORDER BY, all records are read into memory and sorted. For queries containing GROUP BY plus an aggregate function, all records are read into memory and grouped. For queries that produce a scrollable result set, all records up to the furthest accessed record are held into memory. For other queries, CsvJdbc holds only one record at a time in memory.
Query Shortcuts
