CSV

CSV#

Querona reads delimited text files — .csv, .tsv, and files with a custom or fixed separator — from a File connection. Unlike the fixed-schema file types, each delimited file has its own columns, so each file maps to its own virtual table, with a schema detected from the data (or supplied through a Microsoft ODBC schema.ini).

SELECT * FROM customers_csv;

Delimited files are read through these virtual tables, or ad hoc - one file per statement - through OPENROWSET (BULK ..., FORMAT = 'CSV'); see OPENROWSET.

Folder and schema mapping#

The connection’s folder layout maps to schemas:

A file in the connection’s root folder maps to a table in the dbo schema.
Each direct subfolder of the root becomes a schema, and its files map to tables in that schema.

For example, a connection rooted at c:\data with customers.csv in the root and company\employees.csv in a subfolder exposes customers_csv in dbo and employees_csv in the company schema.

Key features#

very large file support (tested on files of several GB)
very wide file support (up to 32 000 columns)
automatic schema detection for schema-less files
custom schema via a Microsoft ODBC schema.ini
local files and http(s) sources - public, or authenticated with a credential (see below)
built-in parsing of dates, currency and booleans, and custom values mapped to Null / True / False

Parser and reader configuration#

Settings divide into connection and object-level settings. Every database object inherits its settings from the connection; adjusting a setting on an object overrides it for that object and stops the inheritance. An asterisk (*) marks the default.

Basic settings#

Setting	Allowed values	Description
File format	Autodetect delimiter*, Comma delimited, Semicolon delimited, Tab delimited, Custom	The delimiter used to split fields.
Allowed delimiter values	Any	The characters or strings used as candidates during separator detection.
Source URI’s	Any URI	The URIs scanned during metadata discovery (import).
First row contains column names	True*, False	The first row is a header. “Skip first # rows” is applied first, so it may not be the file’s first line.
Always detect character encoding before reading	True*, False	Detect the encoding on every open. For best performance, turn off and set a fixed Encoding.
Encoding	Any encoding from the list	The encoding used when a file is opened. If set, automatic detection does not run.
Culture	Any culture from the list	The culture used when converting string values to the target data types.
Allow quotation marks in values	True*, False	Quotation marks are allowed inside values.
Treat a line break in quoted text as an error	True, False*	A line break inside quoted text is treated as an error.
Empty strings should be returned as NULL	True*, False	An empty string is converted to Null.
Data error handling	Ignore row, Report error*	On bad data: “Report error” throws and stops the read; “Ignore row” skips the row and continues.

Advanced settings#

Setting	Allowed values	Description
Max rows to read for data type probing	0 (no limit) to 2147483647	Lines read during schema detection; more lines lower the assumed error margin.
Allow comments	True*, False	Comment lines are allowed.
Comment character	Any character	Marks a commented-out line (default `#`). Only when comments are allowed.
Escape character	Any character	Escapes a quote inside a field (default `"`).
Ignore blank lines	True*, False	Blank lines are ignored when reading.
Trimming options	No trimming*, Trim around a field, Trim inside quotes	How field data is trimmed.
Skip first # rows	0* to 2147483647	Lines skipped from the top of the file.
Values interpreted as boolean True	Any string value(s)	A whole column value matching one of these is converted to True.
Values interpreted as boolean False	Any string value(s)	A whole column value matching one of these is converted to False.
Values interpreted as Null (case-insensitive)	Any string value(s)	A whole column value matching one of these is converted to Null.

Reading from a protected http(s) location#

A delimited file addressed by an http(s) URL is read anonymously unless a credential covers it. Bind one by name on the external data source, or - for the direct-URL form of OPENROWSET (BULK ...) - name a server-level credential after the URL or a prefix of it:

CREATE CREDENTIAL [https://acct.blob.core.windows.net/cont]
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 'sv=2022-11-02&ss=b&srt=co&sp=r&sig=...';

SELECT * FROM OPENROWSET(BULK 'https://acct.blob.core.windows.net/cont/sales.csv',
                         FORMAT = 'CSV') AS sales;

The signature is applied to the outgoing request only - it never enters the location the connection stores, a log, or an error message. Delimited text is the only file type this covers today.

Note

The location must be https. A credential paired with a plain http:// location on a remote host is refused, because the secret would cross the network unencrypted; a loopback host is excepted. See Authenticated reads from remote storage.

Alternatives to the built-in File provider#

If the built-in provider is not sufficient:

a Microsoft ODBC driver — free and standards-based; see ODBC
a commercial third-party driver — for sources neither the built-in provider nor an ODBC driver can handle; see ADO.Net and third-party drivers

CSV

Contents