Image retained from the original article.
Originally published 27 July 2023; rewritten and reviewed on 15 October 2026.
The import appears successful, but a customer code has lost its leading zeros and several dates have changed meaning. Getting rows into SQL Server is only part of the task. The real objective is preserving the values the workbook represents.
Choose the actual input format
For a workbook, the SQL Server Import and Export Wizard can use an appropriate Excel provider, subject to installation and platform requirements. Review its inferred column types instead of accepting them without checking the data.
BULK INSERT reads supported data-file formats; it does not parse an .xlsx workbook directly. Export the relevant sheet to CSV first if that is the chosen route. Record the encoding, delimiter, quoting, header and date conventions.
Import into a staging table
On SQL Server versions supporting CSV format options, a starting pattern is:
BULK INSERT dbo.CustomerImportStage
FROM 'C:\Imports\customers.csv'
WITH (
FORMAT = 'CSV',
FIRSTROW = 2,
FIELDQUOTE = '"',
CODEPAGE = '65001'
);
This assumes a prepared staging schema, a UTF-8 file and the relevant SQL Server features. The path is read from the server’s environment, and its service identity needs appropriate file access. Verify permissions and supported syntax for your deployment.
Reconcile before promoting records
Compare source and imported row counts, inspect conversion failures and check representative records. Include identifiers with leading zeros, empty values, quoted commas and non-ASCII names. Validate duplicate keys and business rules before copying accepted rows into the live schema.
Keep the original file and a record of the import outcome so a rejected batch can be investigated. A repeatable staging process is easier to support than discovering silent conversions after users begin relying on the data.