Reading Fixed-Width Text Files With OPENROWSET and a Format File

The export has no commas because every field owns a fixed number of characters. Reading fixed-width text files starts with that layout contract. A format file tells SQL Server where each field ends before you validate its values.

Specify Positions and Encoding Together

The identifier occupies positions one through five. The name occupies six through thirteen, and the quantity occupies fourteen through nineteen. Each record ends with a Windows carriage return and line feed.

The XML format describes sequential field lengths. Starting positions follow from those lengths, including any skipped filler fields. Don't invent a start-position attribute that the format doesn't support.

I ask for the encoding before trusting a width specification. Character count and byte count aren't interchangeable for every encoding. This example uses ASCII values, so each sample character occupies one byte.

A multibyte or wide-character export needs a matching field representation. The source contract must name both the record layout and encoding. Otherwise, a correct-looking width can split a value in the wrong place.

Create Small Fixed-Width Text Files You Can Inspect

The Windows PowerShell block creates local sample files under a placeholder directory. It includes one valid quantity and one invalid quantity for validation. Padding is explicit so the field lengths are visible. Each record sits in its own parentheses. Without them, the PowerShell comma binds before the plus signs, and both records collapse into one line.

Run it on a suitable test host, then adapt the paths for the SQL Server machine. OPENROWSET reads from the server's filesystem context, not automatically from your SSMS desktop.

The files contain invented sample values. They aren't production data or a measured import result. Keep the same layout while practicing the reader.

Before a real load, verify a few records with a byte-aware editor or local inspection script. A missing space can shift every later field. Fixed-width data is wonderfully orderly until one line decides to arrive wearing the wrong size.

# PowerShell
$sampleFolder = 'C:\SqlImportDemo'
New-Item -ItemType Directory -Path $sampleFolder -Force | Out-Null
$sampleLines = @(('00001' + 'Widget'.PadRight(8) + '10'.PadLeft(6)),
                 ('00002' + 'Gadget'.PadRight(8) + 'BAD'.PadLeft(6)))
$sampleText = ($sampleLines -join "`r`n") + "`r`n"
[IO.File]::WriteAllText((Join-Path $sampleFolder 'parts.txt'),$sampleText,[Text.Encoding]::ASCII)
$formatText = @'
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharFixed" LENGTH="5"/>
<FIELD ID="2" xsi:type="CharFixed" LENGTH="8"/>
<FIELD ID="3" xsi:type="CharFixed" LENGTH="6"/>
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="IdText" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="2" NAME="NameText" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="3" NAME="QuantityText" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>
'@
[IO.File]::WriteAllText((Join-Path $sampleFolder 'parts-format.xml'),$formatText,[Text.UTF8Encoding]::new($false))

Let the Format Describe Raw Fields

RECORD defines how bytes are read. ROW maps those fields to SQL columns. The fourth field consumes the row terminator and isn't mapped into the result. The three business fields arrive as text.

That is deliberate. Reading them directly as integers can fail on one bad value. Stage text first so that record can be classified. Stage raw input before imposing the final types.

The namespace addresses inside the XML are identifiers required by the format. They don't cause this local sample to download a schema. Keep the field order consistent with the export specification.

For filler positions, add an unmapped field of the correct width. Don't trim the whole line before reading it. Those spaces are structural until the fixed fields have been separated correctly.

Read Fixed-Width Text Files From SQL Server

OPENROWSET with BULK and FORMATFILE exposes the file as a rowset. The paths below are placeholders on the SQL Server host. Its execution context needs read access to both files and the required bulk permissions.

A mapped desktop drive isn't a reliable server path. Review the service and authentication context rather than responding to access errors by granting broad permissions everywhere.

SELECT INTO preserves the raw result in a session-local staging table. Inspect all three fields before conversion. Keep raw data available for rejected-row review and source reconciliation. The imported rowset doesn't certify record quality.

It only applies the layout. A wrong specification can separate the wrong parts of a line neatly. That is why sample inspection matters.

SELECT IdText,NameText,QuantityText
INTO #FixedRaw
FROM OPENROWSET(BULK N'C:\SqlImportDemo\parts.txt',
     FORMATFILE = N'C:\SqlImportDemo\parts-format.xml') AS r;
SELECT * FROM #FixedRaw;

My run returned both records as raw text, with the padding still inside the name and quantity fields.

Convert Without Hiding Rejections

Trim field padding only after splitting. TRY_CONVERT returns NULL for a conversion that cannot produce the requested type. NULLIF distinguishes blank numeric input from an ordinary string.

The normalized staging table keeps both raw text and typed values. The rejection report can identify the failed source field directly. It doesn't need to reconstruct that input after a partial load.

TRY_CONVERT doesn't validate every business rule. A negative quantity can be a valid integer and an invalid record. A name can be nonblank but exceed a destination limit.

Include those checks alongside conversion status. I keep rejection reasons visible instead of silently filtering bad rows out of a successful load count. Source owners need to know which records require correction and why.

SELECT IdText,NameText,QuantityText,
       TRY_CONVERT(int,NULLIF(LTRIM(RTRIM(IdText)),'')) AS PartId,
       LTRIM(RTRIM(NameText)) AS PartName,
       TRY_CONVERT(int,NULLIF(LTRIM(RTRIM(QuantityText)),'')) AS Quantity
INTO #FixedTyped
FROM #FixedRaw;
SELECT * FROM #FixedTyped
WHERE PartId IS NULL OR Quantity IS NULL OR Quantity < 0 OR PartName = '';

Only the Gadget row came back from the check. Its Quantity is NULL because BAD isn’t an integer.

Load the Accepted Set Deliberately

Create a destination with the required constraints. Insert only the validated subset, then review rejected rows separately. Duplicate identifiers need their own rule.

A valid type conversion doesn't decide whether the second occurrence replaces the first or causes a rejection. For a real load, keep a stable load identity. The transaction policy must support retries without duplicating committed rows.

The following destination belongs to the disposable test database. Inspect the resulting rows on your own server. Don't report the sample as measured production performance. Which malformed record should stop the entire load?

Agree on that policy before automation. Some imports require all-or-nothing acceptance. Others allow partial acceptance with a durable rejection file and a clear reconciliation of every source record.

CREATE TABLE dbo.FixedWidthPartsDemo
(PartId int PRIMARY KEY,PartName varchar(8) NOT NULL,Quantity int NOT NULL CHECK(Quantity >= 0));
INSERT dbo.FixedWidthPartsDemo(PartId,PartName,Quantity)
SELECT PartId,PartName,Quantity FROM #FixedTyped
WHERE PartId IS NOT NULL AND Quantity IS NOT NULL AND Quantity >= 0 AND PartName <> '';
SELECT PartId,PartName,Quantity FROM dbo.FixedWidthPartsDemo;

The destination held one row: part 1, Widget, quantity 10. The rejected Gadget record stayed in the staging table for review.

Verify Fixed-Width Text Files Before Automating

Test short records, extra trailing bytes, blank fields, and the agreed newline style. A format file for fixed-width text files expects a physical layout. Malformed boundaries can fail parsing before row-level conversion.

Keep those failures distinct in the job's report. Preserve the original file for investigation. The destination's accepted rows alone don't explain records the bulk reader never successfully separated.

For fixed-width text files, the layout is the first contract and the typed schema is the second. Keep them versioned in your ordinary file-management process and review source changes explicitly. Reconcile accepted and rejected records against the input.

The reliable pipeline knows where each byte belongs and which values the business accepts. Both checks are necessary before calling the import complete.

Related reading on this blog: Reading a JSON Lines File Into a Table With OPENROWSET and Loading Large Files Fast With BULK INSERT.

A fixed-width import is not a trimming exercise, it is a layout contract followed by value validation.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.