How to fix Error 4899 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: SQL Server Error 4899 – Invalid Terminator in CSV Format File

Why This Error Happens

This error occurs when the non-XML format file used for a CSV bulk load operation contains inconsistent column terminators across source columns. SQL Server 2025 enforces a strict CSV parsing rule: every source column terminator must be identical except for the final column, which must use a distinct row terminator (such as \r\n). This typically results from a manually edited or improperly auto-generated format file, or from exporting the format file using bcp with mismatched delimiter arguments.

Step-by-Step Resolution Guide

Step 1: Inspect the Format File Structure

  • Open the .fmt file referenced in the error (the %s path) using a text editor like Notepad++ or VS Code on Windows Server 2025 / Windows 11.
  • Locate the COLUMN definitions section and check the terminator attribute for each field.
  • Confirm that all columns except the last use the exact same terminator string (e.g., ",").
  • Verify the final column uses a row terminator like "\r\n" instead of a comma.

Step 2: Regenerate the Format File Using bcp

  • Open an elevated Command Prompt or PowerShell session.
  • Run the following command to regenerate a clean format file:
  bcp YourDatabase.dbo.YourTable format nul -c -t, -r "\r\n" -f C:\BulkData\YourTable.fmt -S YourServerName -T
  • Ensure the -t flag (field terminator) and -r flag (row terminator) match the actual structure of your CSV source file.
  • Avoid manually mixing terminators (e.g., semicolons for some columns and commas for others).

Step 3: Validate the CSV Source File Consistency

  • Open the source CSV file in a plain text editor and confirm that every row uses the same delimiter throughout (no mixed commas/tabs/semicolons).
  • Check for hidden or malformed characters near the last column, as trailing whitespace or stray delimiters can trigger this error.
  • Save the file with UTF-8 or ANSI encoding depending on your BULK INSERT or bcp command specifications.

Step 4: Re-run the Bulk Load Operation

  • Execute your BULK INSERT statement or bcp in command again, referencing the corrected format file.
  • Example T-SQL syntax:
  BULK INSERT YourTable
  FROM 'C:\BulkData\YourFile.csv'
  WITH (
      FORMATFILE = 'C:\BulkData\YourTable.fmt',
      FORMAT = 'CSV'
  );
  • Monitor the output pane or terminal for confirmation that rows were successfully imported.

Alternative or Verification Steps

  • Run SELECT TOP 10 * FROM YourTable; after the load to confirm data populated correctly across all expected columns without truncation or misalignment.
  • If the error persists, bypass the custom format file entirely and use the simpler FORMAT = 'CSV', FIELDTERMINATOR = ',', ROWTERMINATOR = '\r\n' inline options directly in the BULK INSERT statement instead of referencing an external .fmt file.