Bulk load: DataFileType was incorrectly specified as widechar. DataFileType will be assumed to be char because the data file does not have a Unicode signature.

While using BULK INSERT statement in SQL Server 2008 to import tab delimited file in a table you might come across the following error:

Bulk load: DataFileType was incorrectly specified as widechar. DataFileType will be assumed to be char because the data file does not have a Unicode signature.

BULK INSERT statement that I used was:

BULK 
INSERT tblCountries_temp 
FROM 'c:\utf8_tab_delimited.txt' 
WITH( 
DATAFILETYPE = 'widechar', 
FIELDTERMINATOR = '\t', 
ROWTERMINATOR = '\n' 
) 
GO

This might be because the file has a UTF-8 type and each record is terminated with new line character (‘\n’). SQL Server 2008 does not support UTF-8, expecting such data in UTF-16 encoding with each record delimited with a carriage return, newline combination (‘\r\n’).  Additionally, it needs a Byte Order Marker (BOM) at the beginning of the file.

Follow these steps to easily convert UTF-8 type to UTF-16.

  1. Open the tab delimited file in SQL Server 2008
  2. Select File -> Save <File Name> As…
  3. Select Save with Encoding… option from the save menu as shown belowSave As
  4. In the following window select Encoding as “Unicode (Big-Endian) – Codepage 1201” and Line endings as “Windows (CLRF)”
  5. You are done!
  6. Run the BULK INSERT script this time and you will see the results you have been craving for
  7. Good Luck!
Advertisements
This entry was posted in SQL Server. Bookmark the permalink.

2 Responses to Bulk load: DataFileType was incorrectly specified as widechar. DataFileType will be assumed to be char because the data file does not have a Unicode signature.

  1. animal says:

    Just want to say your article is as astonishing. The clearness to your publish is just cool and i can think you’re an expert in this subject. Fine with your permission allow me to grab your feed to keep updated with forthcoming post. Thank you one million and please continue the rewarding work.

  2. You have observed very interesting details! ps nice website.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s