OPENROWSET

OPENROWSET#

Reads data that is not a registered table and exposes it as a table in the FROM clause — either by querying a source through a provider, or by bulk-reading external files.

Provider form#

OPENROWSET ( 'provider', 'connection_string', 'query' )

Runs query against the source identified by provider and connection_string.

SELECT * FROM OPENROWSET('csv', 'provider_string', 'SELECT * FROM data');

Bulk form#

OPENROWSET ( BULK 'data_file' [ , <option> [ , ...n ] ] ) [ WITH ( <schema> ) ] AS alias

Reads one or more Excel workbooks (.xlsx / .xls / .xlsb) from a registered Excel connection as a rowset; a correlation name (AS alias) is required. DATA_SOURCE names the connection (which locates the files) and WITH (...) declares the result columns — optional, since without it the schema is derived from the first workbook the statement resolves to. Read several workbooks by listing them (BULK ('a.xlsx', 'b.xlsx')) or with a glob. The worked walkthrough — worksheet and range selection, header-name binding, column ordinals, and globbing — is in Reading Excel workbooks (BULK) below.

The same bulk form reads PDF documents from a registered File connection when you add FORMAT = 'PDF'. A PDF read returns the built-in PdfDocuments columns — including the extracted Text and the detected Tables (JSON) — selected by name, with no WITH clause. See Reading PDF documents (BULK) below.

It also reads Parquet files from a File connection with FORMAT = 'PARQUET'. Parquet files carry their own schema, so the WITH clause is optional. See Reading Parquet files (BULK) below.

And it reads delimited text files from a File connection with FORMAT = 'CSV'. The schema is derived from the data - a header row names the columns and the sampled values decide their types - so the WITH clause is optional here too. A list or a glob that matches several files is read as one rowset, with each file matched onto the one schema. See Reading delimited files (BULK) below.

Supported options#

Options follow the BULK clause as NAME = value:

Option	Meaning and behavior
`DATA_SOURCE`	Names the registered connection that locates the file(s). File paths are resolved relative to the connection’s directory, and a path that escapes it is rejected.
`FORMAT`	Selects the file reader for a File connection — `'PARQUET'`, `'CSV'`, `'PDF'`, `'TEXT'`, `'XML'` or `'EMAIL'`. May be omitted for a Parquet path (`.parquet` / `.parq`), which is inferred, or for an Excel connection; `'CSV'` is never inferred and must be stated.
`FORMATFILE_DATA_SOURCE`	Carries reader options as a TOML `options` table. The option set depends on the reader — Excel (`sheet`, `on_error`) or PDF (text and table extraction; see Reading PDF documents (BULK)). Packing them into this standard parameter keeps the statement parseable by standard T-SQL client tools.
`FIRSTROW` / `LASTROW`	The 1-based first and last data rows of the read window, applied when the `sheet` expression carries no A1 range.
`HEADER_ROW`	`TRUE` treats the first row of the window as a header and binds `WITH` columns by name — or, with no `WITH` clause, names the derived columns from it. `'FIRST_FILE'` puts that one header on the first resolved workbook alone, which is the split-export shape (see below).

The WITH clause also accepts an explicit 1-based source-column ordinal after a column’s type, pinning a column to a specific sheet column (see Bind columns by header name).

Reading Excel workbooks (BULK)#

The bulk form reads .xlsx, .xls and .xlsb workbooks from a registered Excel connection. Point DATA_SOURCE at the connection and declare the result columns with WITH (...):

SELECT id, amount, qty
  FROM OPENROWSET(BULK 'sales.xlsx', DATA_SOURCE = 'excel_sales')
       WITH (id bigint, amount decimal(28,2), qty int) AS data;

The WITH columns map positionally to sheet columns A, B, C, … in declaration order, and each cell is coerced to the declared type. The read starts at the top of the sheet and treats the first row as data; skip a header row with FIRSTROW = 2, or bind to it by name with HEADER_ROW (see below).

A file reference is resolved under the connection’s directory, and a reference that escapes it is rejected, so a query cannot read arbitrary files on the server. Address a workbook by a path relative to that directory; an absolute path is accepted only when it already points inside it.

Excel is a distinct provider: this form requires an Excel connection. To read PDF, text, XML or email files, use a File connection with the FORMAT option instead — see Reading PDF documents (BULK) below.

Worksheet and range#

Reader options specific to Excel — the worksheet and range, and the multi-file error mode — are passed as a small TOML options table inside the standard FORMATFILE_DATA_SOURCE parameter; packing them into a Microsoft-recognised parameter keeps the statement parseable by standard T-SQL client tools. sheet selects the worksheet and an optional A1 cell range; the standard FIRSTROW and LASTROW bound the rows when no range is given (an A1 range wins if both are supplied):

-- worksheet 'Q1', restricted to the range B2:D100
SELECT region, q1, q2
  FROM OPENROWSET(BULK 'sales.xlsx', DATA_SOURCE = 'excel_sales',
                  FORMATFILE_DATA_SOURCE = 'options = { sheet = "Q1!B2:D100" }')
       WITH (region nvarchar(50), q1 float, q2 float) AS data;

Worksheet names are matched case-insensitively; no other difference is ignored, so sheet = "m2" does not name a tab spelled with a superscript two. Naming a worksheet a workbook does not carry raises an error identifying the sheet and the file, even under on_error = "skip" — a mistyped sheet name is not an unreadable workbook. Over a set of workbooks the check is made as each workbook is read, so every workbook the statement reads must carry the sheet; leave sheet out to read each workbook’s first worksheet.

Bind columns by header name#

With HEADER_ROW = TRUE the first row of the read window is treated as a header, and each WITH column binds to the sheet column whose header matches its name instead of by position — so you can omit or reorder columns:

-- sheet headers: id | name | amount; read only amount and id, by name
SELECT amount, id
  FROM OPENROWSET(BULK 'sales.xlsx', DATA_SOURCE = 'excel_sales', HEADER_ROW = TRUE)
       WITH (amount decimal(28,2), id bigint) AS data;

Header matching is case-insensitive, and the header row is excluded from the data. A column whose name a workbook’s header does not carry reads NULL for that workbook’s rows rather than failing the statement, so a workbook that drops or renames a column does not fail a read the rest of the set answers — the same rule the delimited format follows. A duplicate header raises a clear error, because binding by name through it would be ambiguous. To pin a specific sheet column regardless of the header, give the column an explicit 1-based sheet ordinal after its type — for example WITH (id bigint 1, note nvarchar(200) 3) reads sheet column 1 into id and sheet column 3 into note. An ordinal below 1 names no sheet column and is refused while the statement is planned.

Warning

The other face of that rule: a misspelled column name is indistinguishable from a column the workbook does not carry, so it reads NULL instead of reporting the mistake. Check the result’s columns when one comes back entirely empty.

Which workbooks carry a header row#

Whether a workbook carries a header row cannot be read out of its cells — it is a property of how the set of workbooks was produced, so the statement states it. HEADER_ROW takes three values:

`HEADER_ROW`	Meaning	Effect per workbook
`TRUE`	every workbook carries a header row	each workbook’s window-top row is consumed as that workbook’s header, and its cells are claimed by name, so a workbook whose columns are in another order still answers the same schema
`FALSE`	no workbook carries one	nothing is consumed as a header; every workbook’s cells are claimed by position
`'FIRST_FILE'`	only the first resolved workbook carries one	the first workbook’s header row is consumed and describes the whole set; the workbooks after it are pure data, and their cells are claimed through that one header

'FIRST_FILE' is for the split-export shape — a header written once, continuation workbooks data-only:

-- sales_a.xlsx   id | amount        (the header, then its data rows)
--                1  | 10.5
-- sales_b.xlsx   2  | 20.5          (a continuation workbook, with no header of its own)
SELECT id, amount
  FROM OPENROWSET(BULK 'sales_*.xlsx', DATA_SOURCE = 'excel_sales', HEADER_ROW = 'FIRST_FILE')
       WITH (id bigint, amount float) AS data;

Under HEADER_ROW = TRUE that second workbook’s first data row would be consumed as a header and matched against nothing. With a single workbook TRUE and 'FIRST_FILE' say the same thing, which is why the distinction only appears once a statement spans several workbooks. Omitted, HEADER_ROW means that no workbook carries one; a value that is none of the three is refused rather than guessed at.

Two rules follow from that one header being the layout of the whole set:

The first resolved workbook cannot be stepped over. If it cannot be read the statement fails even under on_error = "skip", which governs the workbooks after it — whose layout is already settled. This is the rule that already governs the schema when no WITH clause is given, and under 'FIRST_FILE' it holds whether or not the statement declares one.
A continuation workbook narrower than the header — one that simply stops before a column the header names — reads NULL for the columns it does not reach, rather than failing. The anchor may also carry nothing but its header row; it then contributes no data of its own and still states the layout.

Let the workbook state the schema#

Leave the WITH clause out and the result columns come from the workbook itself: with HEADER_ROW = TRUE or HEADER_ROW = 'FIRST_FILE' the window’s first row names them, and the cells beneath it decide their types — a text column being declared nvarchar(max) whatever those cells hold (see below). Without a header the columns are named c0, c1, … in sheet order.

-- sheet headers: id | name | amount - the result has those three columns, typed from the data
SELECT * FROM OPENROWSET(BULK 'sales.xlsx', DATA_SOURCE = 'excel_sales', HEADER_ROW = TRUE) AS data;

The schema is derived from the first workbook the statement resolves to, over the same window the read uses — the worksheet, the A1 range or FIRSTROW/LASTROW. A list or a glob matching several workbooks is still one rowset answering that one schema, and with HEADER_ROW = TRUE every later workbook is matched onto it by its own header name, so a workbook whose columns sit in another order still lines up; under HEADER_ROW = 'FIRST_FILE' the later workbooks carry no header and are matched through the first one’s.

Because the first workbook is the schema, it is the one file that cannot be stepped over: if it cannot be read, the statement fails even under on_error = "skip", which governs the read of the workbooks after it. Declare the columns with WITH (...) when you want the schema fixed by the statement rather than by a file.

A derived text column is declared nvarchar(max), not sized to the longest value the first workbook holds. The workbooks after it were never read while the schema was derived, so a width measured in one of them would refuse a longer value in any of the others — a statement that ran yesterday would start failing because a vendor sent a longer name. This matches what SQL Server describes for a schema it derives from data, where a text column’s width comes from the type and never from the cells.

Note

Two things a derived schema cannot promise, both of which answer NULL rather than failing:

a value in a later workbook that does not fit the type the first workbook’s cells implied — a word in a column the anchor held only numbers in, for example;
a column a later workbook does not carry at all.

Neither is reported, and neither can be told apart from a cell that is genuinely empty. Declare the columns with WITH (...) when the statement has to answer for the shape of the data rather than follow it.

Read several workbooks (globbing)#

List workbooks explicitly, or match them with a glob pattern relative to the connection’s directory:

-- an explicit list
SELECT id FROM OPENROWSET(BULK ('jan.xlsx', 'feb.xlsx'), DATA_SOURCE = 'excel_sales')
               WITH (id bigint) AS data;

-- a glob: every .xlsx in the connection directory (top level)
SELECT id FROM OPENROWSET(BULK '*.xlsx', DATA_SOURCE = 'excel_sales')
               WITH (id bigint) AS data;

* matches within a single path segment (the top level only); ** spans subdirectories, so 2024/**/*.xlsx matches every workbook under 2024/ at any depth. The matched workbooks are read and concatenated.

Workbooks are resolved by the same rules as every other BULK read, described under What a wildcard reaches below: names beginning with . or _ are not reached by a wildcard, letter case follows the server’s file system, a folder path reads that folder one level deep, the matches are taken in name order, and a pattern matching nothing returns no row with a warning - or, with no WITH clause, is rejected, because there is then no workbook to take the schema from. What stays Excel’s own is what a workbook is: a wildcard reaches only the extensions the connection declares, never Excel’s ~$ lock files. An absolute path that already points inside the connection’s directory is accepted too, and read as the path relative to it.

Add on_error to the FORMATFILE_DATA_SOURCE options payload to control how an unreadable workbook is handled — on_error = "skip" skips it and continues with the rest, while the default on_error = "fail" raises an error instead. A workbook that can be read but lacks the worksheet named in sheet is not skipped; it fails the statement under either value:

-- skip workbooks that cannot be read, and read the rest
SELECT id FROM OPENROWSET(BULK '2024/**/*.xlsx', DATA_SOURCE = 'excel_sales',
                          FORMATFILE_DATA_SOURCE = 'options = { on_error = "skip" }')
               WITH (id bigint) AS data;

See Microsoft Excel for registering an Excel connection and data-type inference.

Reading PDF documents (BULK)#

The bulk form also reads .pdf files from a registered File connection. Add FORMAT = 'PDF', point DATA_SOURCE at the connection, and select the built-in PdfDocuments columns by name — no WITH clause is needed, and a correlation name (AS alias) is required. File references are resolved under the connection’s directory, and a reference that escapes it is rejected.

The FORMAT option is required on a File connection and selects the reader: PDF, TEXT, XML or EMAIL. Excel workbooks (.xlsx / .xls / .xlsb) are not a File format — they are read through an Excel connection with no FORMAT; see Reading Excel workbooks (BULK) above.

SELECT Path, NumberOfPages, Text
  FROM OPENROWSET(BULK 'report.pdf', DATA_SOURCE = 'files', FORMAT = 'PDF') AS docs;

Two columns carry extracted content: Text (the document text, via PdfPig) and Tables (tables detected in the document, as a JSON array). Each is produced only when selected, and the table engine’s Java runtime starts lazily on the first read of Tables. The full PdfDocuments schema is in PDF.

-- metadata for every PDF in the connection directory
SELECT Path, Title, Author, NumberOfPages
  FROM OPENROWSET(BULK '*.pdf', DATA_SOURCE = 'files', FORMAT = 'PDF') AS docs;

-- detected tables as JSON
SELECT Path, Tables
  FROM OPENROWSET(BULK 'invoice.pdf', DATA_SOURCE = 'files', FORMAT = 'PDF') AS docs;

Extraction options#

Extraction is steered by options passed as a TOML options table in FORMATFILE_DATA_SOURCE. Each option also has a connection-level default that applies to every read (including the built-in PdfDocuments table); a per-query value overrides it.

Option	Applies to	Values (default in bold)
`text_mode`	Text	blocks, words, letters
`segmenter`	Text	default, docstrum, xycut — page-segmentation algorithm for `blocks`
`reading_order`	Text	none, rendering, unsupervised
`dedup_letters`	Text	true, false — drop duplicated overlapping glyphs
`margin_left` / `margin_right` / `margin_top` / `margin_bottom`	Text	points cropped from each page edge (default 0)
`engine`	Tables	auto (ruled first, stream fallback), lattice (ruled only), stream (borderless)
`area`	Tables	`[top, left, bottom, right]` in PDF points, top-left origin (default: whole page)
`pages`	Text and Tables	1-based page range, e.g. `"1-3,5"` (default: all pages)
`password`	Text and Tables	password for an encrypted PDF (default: none)
`on_error`	file selection	fail, skip — how a multi-file read handles an unreadable PDF

Examples#

-- text as space-separated words instead of reading-order blocks
SELECT Text
  FROM OPENROWSET(BULK 'report.pdf', DATA_SOURCE = 'files', FORMAT = 'PDF',
                  FORMATFILE_DATA_SOURCE = 'options = { text_mode = "words" }') AS docs;

-- only pages 1-3 and 5
SELECT Text
  FROM OPENROWSET(BULK 'report.pdf', DATA_SOURCE = 'files', FORMAT = 'PDF',
                  FORMATFILE_DATA_SOURCE = 'options = { pages = "1-3,5" }') AS docs;

-- an encrypted document
SELECT Text
  FROM OPENROWSET(BULK 'secured.pdf', DATA_SOURCE = 'files', FORMAT = 'PDF',
                  FORMATFILE_DATA_SOURCE = 'options = { password = "s3cret" }') AS docs;

-- ruled tables only (no stream fallback)
SELECT Tables
  FROM OPENROWSET(BULK 'grid.pdf', DATA_SOURCE = 'files', FORMAT = 'PDF',
                  FORMATFILE_DATA_SOURCE = 'options = { engine = "lattice" }') AS docs;

-- borderless tables via stream detection
SELECT Tables
  FROM OPENROWSET(BULK 'columns.pdf', DATA_SOURCE = 'files', FORMAT = 'PDF',
                  FORMATFILE_DATA_SOURCE = 'options = { engine = "stream" }') AS docs;

-- restrict extraction to a rectangle (top, left, bottom, right in PDF points, top-left origin)
SELECT Tables
  FROM OPENROWSET(BULK 'invoice.pdf', DATA_SOURCE = 'files', FORMAT = 'PDF',
                  FORMATFILE_DATA_SOURCE = 'options = { engine = "lattice", area = [72, 40, 760, 560] }') AS docs;

-- combine text and table options in one read
SELECT Path, Text, Tables
  FROM OPENROWSET(BULK 'report.pdf', DATA_SOURCE = 'files', FORMAT = 'PDF',
                  FORMATFILE_DATA_SOURCE = 'options = { pages = "2-4", engine = "lattice", text_mode = "words" }') AS docs;

-- every PDF under 2024/ at any depth, skipping any that cannot be read
SELECT Path, Text
  FROM OPENROWSET(BULK '2024/**/*.pdf', DATA_SOURCE = 'files', FORMAT = 'PDF',
                  FORMATFILE_DATA_SOURCE = 'options = { on_error = "skip" }') AS docs;

See PDF for the full PdfDocuments schema and guidance on choosing the extraction options, and Files and data services for the File provider and connection setup.

Reading Parquet files (BULK)#

The bulk form reads .parquet and .parq files from a registered File connection. Point DATA_SOURCE at the connection and add FORMAT = 'PARQUET'; a correlation name (AS alias) is required, and file references are resolved under the connection’s directory. Because a Parquet file carries its own schema, the WITH clause is optional — omit it to return every column:

SELECT id, customer, amount
  FROM OPENROWSET(BULK 'sales.parquet', DATA_SOURCE = 'files', FORMAT = 'PARQUET') AS p;

When the path ends in .parquet or .parq, the format is inferred and FORMAT may be omitted:

SELECT * FROM OPENROWSET(BULK 'sales.parquet', DATA_SOURCE = 'files') AS p;

Add WITH (...) to project and order a subset of columns; on Parquet it binds by column name, so the listed columns may be a subset in any order:

SELECT amount, id
  FROM OPENROWSET(BULK 'sales.parquet', DATA_SOURCE = 'files', FORMAT = 'PARQUET')
       WITH (amount decimal(18,2), id bigint) AS p;

A WITH name must match the file’s column name exactly, case included: WITH (AMOUNT decimal(18,2)) over a column named amount is a column the file does not contain. A WITH column the file does not contain is returned as all-NULL (not an error), and a column that is present is read as the declared type: a declared bigint widens a 32-bit column, a shorter declared fractional scale truncates, and a declared time or datetimeoffset recovers a value the Parquet schema cannot describe. A declared type the file’s own type cannot be read as is refused with an error naming both and suggesting a type that works. Nested-field projection with a '$.path' is not supported for Parquet yet; declare top-level columns only.

Read several files as one rowset by listing them or matching a glob — * within a folder, ** across subfolders. The matched files must share the same schema:

-- an explicit list
SELECT id FROM OPENROWSET(BULK ('jan.parquet', 'feb.parquet'), DATA_SOURCE = 'files') AS p;

-- every Parquet file under 2024/ at any depth
SELECT id FROM OPENROWSET(BULK '2024/**/*.parquet', DATA_SOURCE = 'files') AS p;

A folder named key=value is a folder, not data: a partitioned layout such as year=2024/month=01/ adds no year or month column to the result, and a WITH column named after a folder key reads as all-NULL.

See Parquet for the Parquet type mapping and the imported virtual-table form.

Reading delimited files (BULK)#

The bulk form reads delimited text files from a registered File connection. Point DATA_SOURCE at the connection and add FORMAT = 'CSV' (never inferred); a correlation name (AS alias) is required, and every file reference is resolved under the connection’s directory. The schema is derived from the data: a header row names the columns and the sampled values decide their types, so the WITH clause is optional:

SELECT id, name, price
  FROM OPENROWSET(BULK 'sales.csv', DATA_SOURCE = 'files', FORMAT = 'CSV') AS sales;

A glob only reaches files the connection calls delimited or text - the extensions configured on the File connection for delimited files and for text files. That keeps a broad pattern such as * from handing a PDF or an archive to the delimited reader. A connection that declares no such extensions rejects a glob outright: declare the extensions, or name the file.

SELECT * FROM OPENROWSET(BULK 'exports/*.txt', DATA_SOURCE = 'files', FORMAT = 'CSV') AS data;

A literal path is never filtered: it names one exact file and FORMAT already states how to read it, so an explicitly named file is read whatever its extension.

SELECT * FROM OPENROWSET(BULK 'exports/quarterly.dat', DATA_SOURCE = 'files', FORMAT = 'CSV') AS data;

What a wildcard reaches#

A * matches within one folder or file name and never crosses a /; a ** segment spans folders. A name beginning with . or _ is not reached by a wildcard - that is the convention the tools writing partitioned folders follow, so _SUCCESS, _delta_log and .crc entries stay out of a data read. A pattern segment that spells the character out reaches them: _* matches _SUCCESS.

Matching follows the server’s own file system, so it ignores letter case on Windows and respects it on Linux, exactly as naming a file does. A ? and a [ are ordinary characters in a file name, not wildcards.

A path that ends in /, and a path that names a folder, read that folder’s files one level deep - the same names skipped, and no recursion. The connection’s Find recursive setting governs how deep a wildcard may reach: with it off, a wildcard sees only the files directly in the connection’s folders.

When nothing matches, the statement succeeds and returns no row, with a warning naming the expression. The one exception is a statement with no WITH clause over a format whose schema comes from a file: there is then no schema to describe, and the statement is rejected.

Reading several files as one rowset#

A list of paths, or a glob that matches more than one file, is read as one rowset: every matched file contributes its rows to the same result, in file order.

The files are resolved in a fixed, host-independent order - the paths in the order the statement wrote them, and each glob’s matches in name order. A file reached twice, named explicitly and matched by a glob as well, is read once.

That order is also what decides the result schema when the statement declares none:

Statement shape	Result schema
`WITH (...)` present	the declared columns are the schema
no `WITH`	the columns of the first resolved file, as though you had written them as `WITH`

Naming a file ahead of the glob therefore pins which file the schema comes from. The anchor resolves first, and resolving again through the glob does not read it twice:

-- 'sales-2026-01.csv' resolves first, so the result schema is that file's schema;
-- the glob then contributes the remaining monthly exports
SELECT * FROM OPENROWSET(BULK ('sales-2026-01.csv', 'sales-*.csv'),
                         DATA_SOURCE = 'files', FORMAT = 'CSV') AS sales;

Only the files a pattern matches are opened, and they are opened one at a time as the read reaches them: a month-scoped glob never touches the rest of the folder, and a TOP-limited read stops opening files as soon as it has the rows it was asked for.

Because a file is opened only when the read reaches it, a file removed or made unreadable after the statement started fails that statement with an error naming the file.

How each file is matched onto the schema#

There is one schema and one matcher. Every file, the first one included, is matched onto the result schema in its own right, so files that disagree about field order, about width, or about whether they carry a header row all answer the same schema. Which files carry a header row is stated by HEADER_ROW - see Which files carry a header row below. For each column of the schema, against each file, in this order:

The column	Reads from
states a source ordinal in `WITH`	that field of the file, counting from 1
otherwise, and a header row is in force for that file	the field whose header name equals the column’s name, compared case-insensitively
otherwise (no header row for that file)	the field at the column’s own position in the schema
matched nothing, or matched a field past the file’s last one	`NULL` for that file’s rows

Two consequences follow, and together they are what keeps a statement stable over a file set that changes. A field no column of the schema claims is ignored, so a vendor that adds a column does not change what an existing statement returns. And a column a file does not supply reads as NULL for that file’s rows, so an older, narrower file does not fail the statement either.

Note

A mismatch is answered with NULL, never with an error, and that NULL is indistinguishable from a value the file supplies as empty. So a file whose fields match no column of the schema yields rows of NULL, silently, and a source column someone renamed reads as NULL rather than raising anything. Under SELECT * the schema is the first resolved file’s, which for date-named files is the oldest and narrowest one, so columns added later stay invisible. Declare the columns with WITH whenever the shape of the result matters: it is the mechanism that enforces one.

Header, first row and separators#

Per-statement options layer over the connection’s delimited-file settings — a stated option wins, an absent one leaves the connection’s setting in force:

Option	Meaning and behavior
`HEADER_ROW`	`TRUE`/`FALSE` (or `1`/`0`, quoted or bare), or `'FIRST_FILE'`: which files of the read carry a header row naming the columns - see Which files carry a header row below. Omitted, the connection’s own Column names in first row setting decides, so the same folder answers alike whether it is read through its virtual tables or through `OPENROWSET`. Without a header the columns are named `c0`, `c1`, …
`FIRSTROW`	The 1-based number of the first row to read, applied in every file of the read; the rows before it are skipped as physical lines, so a preamble whose shape differs from the data is fine. With `HEADER_ROW` in force the header is expected at `FIRSTROW`. See Leading rows in each file below.
`FIELDTERMINATOR`	The field separator (`'\t'` for tab). A separator that collides with the quote, escape or comment character, or carries a line break, is rejected.
`CODEPAGE`	The encoding, as a code page number or an encoding name, binding every file of the read. A byte-order mark in a file overrides a stated encoding — the mark is that file’s own statement of what it is. See Encoding, file by file below.
`FIELDQUOTE`	Accepted only as the double quote (`"`), the RFC 4180 quote character the reader parses with; any other value is rejected.

An option this read cannot honour is rejected rather than ignored — ROWTERMINATOR, LASTROW, DATAFILETYPE, MAXERRORS, ERRORFILE, ROWS_PER_BATCH, FORMATFILE and the SINGLE_BLOB/SINGLE_CLOB/SINGLE_NCLOB forms all refuse with a clear error, so a statement never quietly returns something other than what it asked for.

-- a two-line export banner precedes the header
SELECT id, name
  FROM OPENROWSET(BULK 'export.csv', DATA_SOURCE = 'files', FORMAT = 'CSV',
                  FIRSTROW = 3, HEADER_ROW = TRUE) AS sales;

-- semicolon-separated, Central European encoding
SELECT *
  FROM OPENROWSET(BULK 'orders.csv', DATA_SOURCE = 'files', FORMAT = 'CSV',
                  FIELDTERMINATOR = ';', CODEPAGE = '1250') AS orders;

Which files carry a header row#

Whether a file carries a header row cannot be read out of its bytes - it is a property of how the file set was produced, so the statement states it. HEADER_ROW takes three values:

`HEADER_ROW`	Meaning	Effect per file
`TRUE`	every file carries a header row	each file’s first row is consumed as that file’s header, and its fields are claimed by name, so a file whose columns are in another order still answers the same schema
`FALSE`	no file carries one	nothing is consumed as a header; every file’s fields are claimed by position
`'FIRST_FILE'`	only the first resolved file carries one	the first file’s header row is consumed and describes the whole set; the files after it are pure data, and their fields are claimed through that one header

'FIRST_FILE' is for the split-export shape - a header written once, continuation files data-only:

-- sales-1.csv   id,name
--               1,widget
-- sales-2.csv   2,gadget      (a continuation file, with no header of its own)
SELECT id, name
  FROM OPENROWSET(BULK 'sales-*.csv', DATA_SOURCE = 'files', FORMAT = 'CSV',
                  HEADER_ROW = 'FIRST_FILE') AS sales;

Under HEADER_ROW = TRUE that second file’s only data row would be consumed as a header, matched against nothing, and the file would read as all-NULL. With a single file TRUE and 'FIRST_FILE' say the same thing, which is why the distinction only appears once a statement spans several files. Omitted, HEADER_ROW falls back to the connection’s Column names in first row setting, which answers TRUE or FALSE and never 'FIRST_FILE'; a value that is none of the three is refused rather than guessed at.

Leading rows in each file#

FIRSTROW and the connection’s Skip first # rows setting both apply per file - each file loses its own leading rows - and they act at different points in the read:

FIRSTROW is pre-parser: it hides physical lines, counting blank and comment lines, and survives a preamble the parser cannot read - which is its reason to exist.
Skip is post-parser: it consumes parsed records, so comment and blank lines do not count, and the skipped rows must be parseable under the data’s column count (a ragged preamble aborts it).
Stating FIRSTROW zeroes Skip for the read.

The two combine with the header convention rather than replacing it: the leading-row skip applies in every file, while the header is consumed wherever HEADER_ROW says one is. Under 'FIRST_FILE' the first file loses its preamble and its header, and the files after it lose their preamble only.

Encoding, file by file#

The encoding is resolved when each file is opened, not once for the whole read, so a set whose files were produced by different servers is read correctly without splitting the statement. Detection runs per file, and each file’s own byte-order mark has the last word over what was detected.

CODEPAGE states the encoding for every file of the read - a stated encoding is an instruction, not a hint, and it turns detection off. A byte-order mark still overrides it, per file, because the mark is that file’s own statement of what it is.

Where the connection’s file format asks for the delimiter to be auto-detected, the separator is detected per file exactly as the encoding is; stating FIELDTERMINATOR fixes one separator for every file of the read, the way CODEPAGE fixes one encoding.

The WITH clause#

Add WITH (...) to state the result columns yourself. The declared columns are the schema of the whole read, and each file is matched onto them by the rules above - by a stated ordinal wherever one is stated, else by name, case-insensitively, where a header row is in force, else by the column’s own position in the list - so the list may name a subset of the columns in any order, and each column keeps the type derived from the data:

SELECT price, id
  FROM OPENROWSET(BULK 'sales.csv', DATA_SOURCE = 'files', FORMAT = 'CSV')
       WITH (price decimal(18,2), id int) AS sales;

A WITH column that names no column of a file reads as NULL for that file’s rows and keeps the type the statement declared for it, matching the Parquet form. Note that this is indistinguishable from a column the file supplies as empty: a renamed source column therefore reads as NULL rather than as an error.

The WITH clause also accepts an explicit 1-based source-column ordinal after a column’s type. It pins the column to that field of every file and overrides the by-name matching:

SELECT label, id
  FROM OPENROWSET(BULK 'sales.csv', DATA_SOURCE = 'files', FORMAT = 'CSV')
       WITH (label varchar(50) 2, id int 1) AS sales;

An ordinal below 1 is rejected while the statement is planned. An ordinal past a file’s last field is not: it reads as NULL for that file’s rows, exactly as an unmatched name does, because how wide a file is differs from file to file.

See CSV for the connection’s delimited-file settings and the imported virtual-table form.

File metadata functions#

Two functions answer, per row, which file the row came from. They are called on the correlation name of an OPENROWSET (BULK ...) source and are available for every format a File connection reads - 'CSV', 'PARQUET', 'TEXT', 'XML', 'PDF' and 'EMAIL':

Call	Returns
`alias.filename()`	the file’s own name, without its folders
`alias.filepath()`	the file’s full path on the Querona server
`alias.filepath(n)`	the text the `n`-th wildcard of the `BULK` path matched, counted from 1

Every one of them returns nvarchar(1024), and the two names are written in lower case: FILENAME() is an ordinary function call, not this one.

An Excel connection answers them as well, per workbook: filename() is the workbook’s name, and a filter on filepath(n) is decided per workbook in the same way, except under HEADER_ROW = 'FIRST_FILE', where the first workbook carries the header every later one is read through.

SELECT r.filepath(1) AS [year], r.filepath(2) AS [month], r.filename() AS [file], COUNT_BIG(*) AS [rows]
  FROM OPENROWSET(BULK 'events/year=*/month=*/*.parquet', DATA_SOURCE = 'files') AS r
 WHERE r.filepath(1) = '2025'
 GROUP BY r.filepath(1), r.filepath(2), r.filename();

This is how a partitioned folder layout is queried: the folder names are not columns (see above), and these functions are what brings them into the result.

The values are ordinary columns from there on, so they may be selected, filtered, grouped, ordered, wrapped in other functions, used inside a view and inserted into a table. They are not included by SELECT *, which returns the file’s own columns only.

What each wildcard captures#

Each * is one wildcard and captures the characters it consumed within one folder or file name; a ** segment is one wildcard and captures the folders it spanned, joined by /, or the whole remainder when it ends the pattern. Wildcards are counted left to right across the whole path.

Pattern	Matched file	`filepath(1)`, `filepath(2)`, …
`year=/month=/*.csv`	`year=2024/month=01/part-a.csv`	`2024`, `01`, `part-a`
`sales__.csv`	`sales_2023_q1.csv`	`sales_2023`, `q1` (each wildcard takes as much as it can, left first)
`events/**`	`events/2024/01/part-a.csv`	`2024/01/part-a.csv`
`data/**/part-a.csv`	`data/b/c/part-a.csv`	`b/c` (empty when it spanned no folder)

An ordinal past the number of wildcards the statement declares is rejected while the statement is planned. When a path list mixes patterns of different wildcard counts, a row from a pattern with fewer answers NULL for the ordinals that pattern does not have. A literal path and a folder path declare no wildcard at all, so filepath(n) over them is rejected; filename() and filepath() still answer.

Differences from SQL Server#

filepath() returns the file’s full path on the server, with or without a wildcard in the pattern. SQL Server reads its files from object storage and returns the path as the statement wrote it for a literal file; a local folder has no equivalent there, so Querona answers one path for every query rather than a relative one for some and an absolute one for others.

Querona also captures the ** forms it accepts beyond SQL Server’s final /**, as described above. SQL Server rejects those patterns outright, so no query that runs on both products answers differently.

The functions are answered for the text, XML, PDF and e-mail reads too, where one file is one row. SQL Server has no such formats, so nothing a script can run on both products answers differently there either. The same holds for an Excel connection’s workbooks.