如何将分号分隔文本文件导入SQL Server？遇bcp及BULK INSERT报错求解决

Hey there! Let’s walk through the most common fixes for the errors you’re hitting when importing semicolon-separated files using bcp and BULK INSERT in SQL Server.

First: Fixing bcp XML Format File Generation Errors #

If bcp is throwing errors when creating your XML format file, these are the key checks:

• Specify the semicolon field terminator explicitly: bcp uses tabs as the default field separator, so you have to tell it to use ; with the -t flag. Here’s the corrected command template:

  bcp YourDatabase.dbo.YourTargetTable format nul -c -x -f C:\temp\your_format.xml -t ";" -S YourServerInstance -U YourUsername -P YourPassword
  

  Double-check that -t ";" is included—this is the #1 mistake with non-default separators.
• Match table and file column counts/data types: Make sure your text file has exactly the same number of columns as your SQL table, and that data types are compatible. For example, if your table has a DATE column, ensure the text file’s date values follow a format SQL Server recognizes (like YYYY-MM-DD).
• Handle embedded special characters: If any fields in your text file contain semicolons or line breaks, wrap those fields in quotes (e.g., "Doe; John") and add the -q "\"" flag to your bcp command to define the quote character.

Next: Resolving BULK INSERT Errors #

Once you have a valid format file, if BULK INSERT is still failing, try these steps:

• Verify your BULK INSERT syntax includes critical parameters: Ensure you’re explicitly setting the field terminator and pointing to the correct format file. Example:

  BULK INSERT YourDatabase.dbo.YourTargetTable
  FROM 'C:\temp\your_datafile.txt'
  WITH (
      FIELDTERMINATOR = ';',
      ROWTERMINATOR = '\n', -- Adjust to '\r\n' if your file uses Windows line endings
      FORMATFILE = 'C:\temp\your_format.xml',
      KEEPNULLS -- Add this if your text file uses 'NULL' strings for empty values
  );
  

• Check the XML format file for accuracy: Open the generated XML file and confirm every <FIELD> entry uses TERMINATOR=";", and that the <COLUMN> mappings match your table’s column order and data types. For example, a varchar column should have a MAX_LENGTH that’s large enough for your text values.
• Rule out permission issues: SQL Server’s service account (not your local user account) needs read access to both the data file and format file. If you’re getting "access denied" errors, grant read permissions to the SQL Server service account on those files/folders.
• Test with a small dataset: Create a copy of your data file with just the first 5-10 rows. If this imports successfully, you know the issue is with a specific row in your full dataset—gradually add rows back to find the problematic entry (often bad data like unclosed quotes or mismatched column counts).

Quick Final Checks #

• Make sure there are no extra blank lines at the end of your text file—these can cause "unexpected end of file" errors.
• If you’re using a remote SQL Server instance, ensure the file paths in BULK INSERT are accessible from the server (use a network share path like \\YourMachine\SharedFolder\data.txt instead of local paths).

内容的提问来源于stack exchange，提问作者Brian Battles