Fixing “Truncated Data” errors when importing SQL VARCHAR(MAX) columns.

Importing text-heavy SQL Server records into Excel often triggers unexpected data cuts or stops with driver warnings like [Microsoft][ODBC SQL Server Driver]String data, right truncation or Error: Truncated Data. These errors occur when large text fields stored in VARCHAR(MAX) or NVARCHAR(MAX) columns exceed the internal buffer size of your database driver or surpass the strict character limits of the Excel worksheet grid.

While a SQL Server VARCHAR(MAX) column can hold up to 2 gigabytes of text (approximately 2 billion single-byte characters) [4], an individual Excel cell has an absolute, non-configurable ceiling of 32,767 characters [5]. Understanding where your text is being cut—whether inside the network driver or at the cell boundary—determines the necessary fix.

Quick Fix: Resolve SQL VARCHAR(MAX) Truncation

If truncation occurs at 255 or 4,000 characters, update your connection string to use the modern Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL) or add DataTypeCompatibility=80; to your ODBC configuration. If the text string exceeds 32,767 characters, split the column in SQL using SUBSTRING() or parse it into separate rows/columns using Power Query before loading to the worksheet.

---

Where Truncation Occurs: Driver vs. Cell Grid

Truncation happens at two distinct architectural layers:

[SQL Server VARCHAR(MAX)] (Up to 2 GB of text)
              │
              ▼
   [Database Driver Layer] ──► Fails at 255, 4,000, or 8,000 characters
              │                Cause: Legacy ODBC buffer limits / OLE DB mapping
              │
              ▼
    [Power Query Engine] ────► Stores up to 32,767+ chars in internal cache
              │
              ▼
    [Excel Worksheet Grid] ──► Fails at 32,767 characters per cell
                               Cause: Hard cell architecture limit (Excel spec)

Layer 1: Database Driver Truncation (255 to 8,000 Characters)

Older connection drivers (such as the legacy SQLSRV32.DLL ODBC driver shipped with older Windows versions) do not handle MAX data types natively. When encountering VARCHAR(MAX) or NVARCHAR(MAX), these drivers treat the field as a legacy TEXT or IMAGE type, truncating content at:

• 255 characters: Default fallback for unmapped string pointers.
• 4,000 characters: Standard boundary for NVARCHAR in SQL Server.
• 8,000 characters: Standard boundary for VARCHAR in SQL Server.

Layer 2: Excel Worksheet Cell Truncation (32,767 Characters)

If the driver successfully delivers the full payload into Power Query, attempting to load a cell containing more than 32,767 characters into an Excel sheet table will either truncate the string at character 32,767 or generate an import failure. Formulas referencing these truncated cells may also produce a #CALC! in Text Manipulation: Exceeding character limits (32,767).

---

Diagnostic Procedures

To pinpoint the component causing the truncation, run these checks:

1. Measure the String Length in SQL

Execute this query in SQL Server Management Studio to see if the text exceeds Excel’s cell capacity:

SELECT
    RecordID,
    LEN(NotesField) AS CharacterCount,
    DATALENGTH(NotesField) AS StorageBytes
FROM Production.WorkOrders
ORDER BY CharacterCount DESC;

• If CharacterCount > 32,767: The data cannot fit inside a standard worksheet cell. You must split, truncate, or parse the field before placing it on the worksheet grid.
• If CharacterCount <= 8,000 but Excel truncates at 255 or 4,000: The issue is caused by your connection driver or ODBC buffer settings.

2. Check the Power Query Editor Preview

Open your query in Power Query. Highlight a row and click inside the white space of the data cell to inspect the record preview at the bottom of the screen.

• If the full text is visible in the bottom preview pane but truncates when output to the sheet, the grid barrier is the root cause.
• If the text is already truncated in the Power Query preview pane, the driver dropped the data during transmission.

---

Solutions for Driver-Level Truncation

Solution 1: Switch to the Modern MSOLEDBSQL Driver

Legacy drivers like SQL Server or SQL Server Native Client 11.0 (deprecated) often mismanage large string buffers. Reconnect using the modern Microsoft OLE DB Driver for SQL Server:

1. In Excel, go to Data > Get Data > From Database > From SQL Server Database.
2. In the connection options, ensure advanced properties specify the modern provider:
   Provider=MSOLEDBSQL19;Data Source=YourServer;Initial Catalog=YourDB;Integrated Security=SSPI;
3. This driver correctly handles large object (LOB) data streams up to the full MAX size without premature truncation.

Solution 2: Cast the Field in the SQL Statement

If updating drivers is restricted by IT policies, cast the VARCHAR(MAX) field to a defined length in your extraction query:

SELECT
    RecordID,
    -- Cast to the maximum safe size that fits in your driver buffer
    CAST(NotesField AS VARCHAR(8000)) AS SafeNotes
FROM Production.WorkOrders;

This prevents the driver from falling back to arbitrary 255-character limits when negotiating metadata types.

---

Solutions for Cell-Limit Truncation (>32,767 Characters)

When handling audit logs, JSON payloads, or extensive support tickets that exceed 32,767 characters, choose from these solutions:

Solution 1: Split into Multiple Columns in SQL

Break the text into segments that fit comfortably inside individual cells:

SELECT
    RecordID,
    SUBSTRING(AuditLog, 1, 30000) AS LogPart1,
    SUBSTRING(AuditLog, 30001, 30000) AS LogPart2,
    SUBSTRING(AuditLog, 60001, 30000) AS LogPart3
FROM Security.AuditLogs;

Each column remains under the 32,767-character limit, allowing Excel to display the complete log across adjacent columns.

Solution 2: Split Columns in Power Query

You can automate column splitting dynamically within Power Query without altering the source SQL query:

1. Select the VARCHAR(MAX) column.
2. Go to Transform > Split Column > By Number of Characters.
3. Set the character count to 30000.
4. Select Repeatedly.
5. Click OK.

let
    Source = Sql.Database("DBServer", "Operations"),
    TableData = Source{[Schema="dbo",Item="AuditLogs"]}[Data],
    SplitByChars = Table.SplitColumn(TableData, "AuditLog",
        Splitter.SplitTextByRepeatedLengths(30000),
        {"Log_Part1", "Log_Part2", "Log_Part3"})
in
    SplitByChars

Solution 3: Filter Out Non-Printable Characters

Sometimes text truncation is simulated by a hidden ASCII null character (CHAR(0)), which tells Excel’s rendering engine that the string has ended, hiding the remaining content. If your database contains binary-converted text or raw web scrapes, clean the string:

SELECT
    RecordID,
    REPLACE(NotesField, CHAR(0), '') AS SanitizedNotes
FROM Production.WorkOrders;

For more details on non-printing ASCII bytes interfering with grid cells, see Fixing “Invalid Cell Value” when SQL returns non-printable ASCII characters..

---

Verification and Next Steps

To verify that your text imported completely:

1. Calculate Length: In an adjacent column, use =LEN(A2) against your imported text cell.
2. Compare Lengths: Verify that the cell length matches the LEN() calculation from your SQL diagnostic query.
3. Check Trailing Data: Confirm that the end of the text string matches the database source rather than cutting off mid-word.

For wider table considerations where text fields cause row-buffer problems, refer to Fixing “Row size limit exceeded” when importing wide SQL tables (>1024 columns).. For detailed methods on handling text bloat and string limits across different Power Query pipelines, consult Troubleshooting “Truncated Data” errors in long text columns (>32k characters). and SQL-to-Excel Data Mapping: Fixing Type Mismatches and Schema Breaks..