Fix CSV Data Type Errors When Importing into Azure Database
Question details
The user is experiencing data type mismatch errors when attempting to import a CSV file into an Azure database.
- Product
- Azure Database / SQL Server Import Tools
- Device & OS
- not provided
- Scenario
- Importing a CSV file into an Azure-hosted database using an import wizard where column types are incorrectly mapped.
- Observed behavior
- The import wizard reports that columns are compatible, but the process fails because a source field is interpreted as DT_NTEXT while the destination schema expects DT_TEXT.
Ensure you have the exact database schema definitions on hand and administrative access to the database import tool, such as the SQL Server Import and Export Wizard.
Adjust Data Type Mappings in the Import Wizard
Manually override the default column mapping in your import wizard to ensure the CSV data type matches the destination schema exactly.
Import wizards often sample the first few rows of a CSV file to guess the data type. If it detects extended characters, it may default to a Unicode string type (DT_NTEXT) instead of standard text (DT_TEXT). Modifying this mapping before execution resolves the mismatch.
Launch the SQL Server Import and Export Wizard and select your CSV file as the Flat File Source.
On the Data Source page, select the 'Advanced' tab on the left-hand menu to view the properties of individual columns.
Select the problematic category column. In the properties pane, change the 'DataType' from 'Unicode text stream [DT_NTEXT]' to 'string [DT_STR]' or 'text stream [DT_TEXT]' as required by your database.
Proceed to the 'Select Source Tables and Views' step and click 'Edit Mappings' to verify that the Source and Destination data types now align perfectly.
Complete the wizard steps and execute the package to ensure the data imports without type conversion failures.
Standardize CSV Encoding and Formatting Before Import
Clean and format the CSV file in a spreadsheet editor to remove anomalous characters and enforce a standard encoding that database tools can easily read.
Seek Official Microsoft Technical Support
If standard mapping adjustments do not resolve the issue, escalate the problem to Microsoft's specialized developer channels.
Prepare CSV Data for Database Imports with WPS Office
While executing Azure database imports requires dedicated Microsoft database tools, preparing and cleaning your CSV files beforehand is faster with a reliable spreadsheet editor. WPS Office offers a free, lightweight, and familiar environment for scrubbing data, fixing encodings, and saving standard CSV formats before your database import.
- 1. Install WPS Office: Download and install the free WPS Office suite on your computer.
- 2. Open the CSV File: Launch WPS Spreadsheet and drag-and-drop your raw CSV file into the workspace.
- 3. Clean and Export: Remove any anomalous characters, then use 'Save As' to export a clean, standard CSV ready for database ingestion.
Frequently Asked Questions
Why does the SQL Server Import Wizard read my text column as DT_NTEXT?
The import wizard samples the first few rows of your CSV to determine data types. If it encounters Unicode characters or particularly long strings in those rows, it defaults to DT_NTEXT (Unicode text stream) to prevent accidental data truncation.
What is the difference between DT_TEXT and DT_NTEXT?
DT_TEXT is used for standard, non-Unicode string data, while DT_NTEXT is used for Unicode string data. If your destination database column is strictly defined as standard text but the wizard pushes Unicode text, the import will fail due to a type mismatch.
Can changing the CSV file's encoding fix data type import errors?
Yes. Sometimes, saving a CSV file in ANSI or standard UTF-8 encoding rather than a specialized Unicode format helps the import wizard correctly interpret the columns as standard text (DT_TEXT), reducing the chances of conversion errors.
How can I force a specific data type for a CSV column during import?
In the SQL Server Import and Export Wizard, before you proceed to the actual import step, you can go to the 'Advanced' tab on the Data Source page. From there, you can manually define the exact data type (e.g., DT_STR or DT_TEXT) and column width for each individual column.
