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. 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 a delimited text file from a File connection with FORMAT = 'CSV'. The schema is derived from the file itself - a header row names the columns and the sampled values decide their types - so the WITH clause is optional here too. 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 SQL Server 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 (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 SQL Server 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;

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. An unmatched column name, or a duplicate header, raises a clear error. 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.

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. 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:

-- 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 column the file does not contain is returned as all-NULL (not an error), and a column that is present keeps the file’s own type — the declared type fixes the column’s name and order, not a conversion. 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;

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

Reading delimited files (BULK)#

The bulk form reads one delimited text file 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 the file reference is resolved under the connection’s directory. The schema is derived from the file: 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 statement reads exactly one file. A list or glob that matches several files is rejected while the statement is planned; narrow the pattern (or name the file) until it matches one.

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;

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): whether the first row of the read window names the columns. Omitted, the connection’s own Column names in first row setting decides — unlike SQL Server’s cloud services, where an omitted `HEADER_ROW` is `FALSE` — 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; 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`.
`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 file’s encoding, as a code page number or an encoding name. A byte-order mark in the file overrides a stated encoding — the mark is the file’s own statement of what it is.
`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;

The WITH clause#

Add WITH (...) to project and order a subset of columns; it binds to the file’s columns by name, so the listed columns may be a subset in any order, and each 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 the file is rejected while the statement is planned — the read binds by name, so there is nothing such a column could be bound to. (This is stricter than the Parquet form, which returns such a column as all-NULL.) An explicit column ordinal after the type is likewise rejected; bind by name instead.

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