Fixing “Invalid Cell Value” when SQL returns non-printable ASCII characters.

When importing records from SQL Server into Excel, users often encounter refresh crashes, blank cell replacements, or the error: Invalid Cell Value. In severe cases, Excel allows the file to save, but reopening it triggers an alert stating: We found a problem with some content in ‘Workbook.xlsx’. Do you want us to try to recover as much as we can?

These failures occur when database records contain non-printable ASCII control characters. The OpenXML spreadsheet specification that governs modern Excel files (.xlsx) strictly prohibits most characters in the ASCII 0 through 31 range. When SQL queries retrieve text from web scrapers, legacy systems, barcode scanners, or binary-converted fields, these invisible control bytes pass through connection drivers and corrupt the XML structure of the Excel worksheet.

Quick Fix: Sanitize Non-Printable ASCII Characters

In your SQL statement, wrap text columns with REPLACE(ColumnName, CHAR(0), '') to remove null terminators. In Power Query, select the affected column and go to Transform > Format > Clean. To guarantee complete removal of all illegal control characters below ASCII 32, use the M-code function: Text.Select([Column], each Character.ToNumber(_) >= 32 or _ = "#(lf)" or _ = "#(cr)" or _ = "#(tab)").

---

Why Non-Printable ASCII Breaks Excel

The underlying file architecture of modern Excel workbooks explains why these characters cause failures:

1. The OpenXML Character Set Rules: A .xlsx file is actually a zipped collection of XML files. Under the W3C XML 1.0 standard, characters with ASCII byte values from 0 to 31 are strictly prohibited, with exactly three exceptions:
   • Horizontal Tab: CHAR(9) or #(tab)
   • Line Feed: CHAR(10) or #(lf)
   • Carriage Return: CHAR(13) or #(cr)
2. The Null Terminator Trap (CHAR(0)): In programming languages like C and C++, the null character (CHAR(0)) marks the end of a string. When database fields contain CHAR(0), Excel’s internal memory manager may either truncate the string at that byte or write a literal null byte into the sheet XML. When Excel reopens the file, the XML parser fails immediately.
3. Legacy and Raw Inputs: Database columns configured as VARCHAR or NVARCHAR accept non-printable control characters without error. Common sources include:
   • Barcode scanners that append start/stop control bytes like CHAR(2) (Start of Text) or CHAR(3) (End of Text).
   • User input copy-pasted from terminal emulators or formatted PDF documents.
   • Data migrated from legacy mainframes containing form feeds (CHAR(12)) or vertical tabs (CHAR(11)).

When Excel encounters these invalid characters during a workbook load, it flags an error. For related file recovery scenarios, see Fixing “DataFormat.Error: Invalid cell value” in Excel imports..

---

Diagnostic Steps: Finding the Illegal Characters

Before removing characters, identify which column and specific ASCII codes are breaking the import:

[SQL Query / Table Data]
           │
           ▼
[Check for OpenXML Violations]
           │
           ├── ASCII 0: Null Terminator ──────► Causes hard string truncation/XML crash
           ├── ASCII 1-8, 11-12, 14-31 ───────► Triggers "Invalid Cell Value" or Repair Prompt
           └── ASCII 9, 10, 13 (Tab, LF, CR) ──► Valid in Excel (Standard whitespace)

Step 1: Run a SQL Detection Script

Execute the following query in SQL Server Management Studio to locate records containing control characters in a suspected text column:

SELECT
    RecordID,
    NotesField
FROM Production.CustomerNotes
WHERE
    -- Check for presence of any prohibited ASCII characters between 0 and 31
    NotesField COLLATE Latin1_General_BIN LIKE '%' + CHAR(0) + '%'
    OR NotesField COLLATE Latin1_General_BIN LIKE '%['
        + CHAR(1) + CHAR(2) + CHAR(3) + CHAR(4) + CHAR(5)
        + CHAR(6) + CHAR(7) + CHAR(8) + CHAR(11) + CHAR(12)
        + CHAR(14) + CHAR(15) + CHAR(16) + CHAR(17) + CHAR(18)
        + CHAR(19) + CHAR(20) + CHAR(21) + CHAR(22) + CHAR(23)
        + CHAR(24) + CHAR(25) + CHAR(26) + CHAR(27) + CHAR(28)
        + CHAR(29) + CHAR(30) + CHAR(31) + ']%';

If this query returns rows, those records contain illegal characters that must be cleaned before Excel can safely store them.

Step 2: Inspect Workbook XML Errors

If an imported file displays the “Repaired Records” prompt upon opening:

1. Click View in the recovery dialog box.
2. Excel will display an XML error log pointing to a location like /xl/worksheets/sheet1.xml with an error message such as An invalid character was found in text content.
3. This confirms that an unescaped control character made it past the database driver and into the sheet structure.

If long text fields are also experiencing length-based cuts, check Fixing “Truncated Data” errors when importing SQL VARCHAR(MAX) columns..

---

Step-by-Step Remediation Strategies

Strategy 1: Clean Characters Inside the SQL Extraction Query

The cleanest approach is to strip the invalid characters before the data is transmitted to Excel.

To eliminate CHAR(0) null characters and common scanner artifacts:

SELECT
    RecordID,
    -- Strip Null characters, Form Feeds, and Vertical Tabs
    REPLACE(
        REPLACE(
            REPLACE(NotesField, CHAR(0), ''),
        CHAR(11), ''),
    CHAR(12), '') AS CleanNotes
FROM Production.CustomerNotes;

For fields with unpredictable control characters, use SQL Server’s TRANSLATE function or a scalar sanitization function to strip the entire 0-31 byte range while preserving tabs (CHAR(9)) and line breaks (CHAR(10), CHAR(13)).

Strategy 2: Sanitize Data in Power Query

If you cannot edit the database query, clean the data inside Power Query using built-in transformation tools.

Using the Built-In Clean Function

1. In Power Query, select the problematic text column.
2. Go to the Transform tab.
3. Click Format > Clean.

The Text.Clean M-function automatically strips ASCII characters 0 through 31 from the text string.

Using Advanced Character Filtering in M-Code

While Text.Clean removes standard control codes, it occasionally misses certain non-standard Unicode zero-width control bytes. To explicitly whitelist only valid printable characters, use a custom M expression:

1. In Power Query, select Add Column > Custom Column.
2. Enter the following formula:

Text.Select([NotesField], each
    Character.ToNumber(_) >= 32 or
    _ = "#(lf)" or
    _ = "#(cr)" or
    _ = "#(tab)"
)

This ensures that only standard printable characters (ASCII 32 and above) along with legal tabs and line breaks reach the worksheet.

Strategy 3: In-Sheet Cleaning Using the CLEAN Function

If the records are already loaded onto a worksheet and formulas are throwing #N/A or #VALUE! errors due to hidden characters:

1. Insert a helper column next to the data.
2. Enter the formula:
   =CLEAN(A2)
3. Copy the formula down the column.
4. Copy the helper column, then use Paste Special > Values over the original data.

For scenarios where hidden characters cause lookup formulas to fail silently, consult #N/A because of Hidden Non-Printing Characters (CLEAN function fix).

---

Verification and Downstream Testing

To verify that your dataset is completely clean:

1. Test File Re-Opening: Save the workbook as .xlsx, close Excel completely, and reopen the file. If the file opens instantly without prompting for XML repairs or displaying “We found a problem with some content,” the character stream is valid.
2. Character Code Audit: Run a verification formula in an empty cell to search for remaining control codes:
   =SUMPRODUCT(--(CODE(MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)) < 32))(Note: This formula returns 0 if no characters below ASCII 32 exist, excluding legal line breaks).

If your data pipeline handles multi-language text that produces garbled characters rather than control codes, see Handling “Special Characters” (UTF-8 vs ANSI) in text imports.. For wider data mapping guidance, review SQL-to-Excel Data Mapping: Fixing Type Mismatches and Schema Breaks..