Issue with Bulk import using BCP with data file name consisting non-English character

Question

I attempted to bulk import data using BCP from a file with non-English characters in its name. Both files were accessible, yet the import failed. Renaming the file to "order.txt" and "order.txt.format" resolved the issue, allowing the bulk import to succeed. Are there any limitations in BCP regarding file names?
I also tried with latest version of BCP

Answer 1

BCP is quite stuck to the command-line and the OEM code page. But recent versions of Windows includes an option to set UTF-8 as the OEM code page and with this setting you should be able to load the file. (I just tested.) Go to Control Panel->Region->Administrative:

Beware that not all software may play well with this setting.

Answer 2

Hi @Haresh Prajapati, Maybe as an alternative, you can try T-SQL BULK INSERT... It allows to specify Unicode file paths via N'<fully qualified file path>' for both data_file as well as format file. Check it out here: https://learn.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-ver16 Here is a conceptual example:

BULK INSERT dbo.myTable
FROM N'E:\Temp\source_data.csv'
WITH (
   FORMAT = 'CSV',
   FORMATFILE = N'e:\Temp\myFormatFile.xml',
   DATAFILETYPE = 'char',  
   FIELDTERMINATOR = ','
);

Answer 3

Hi @Haresh Prajapati How about Encode the file as (Extended) ASCII using Code Page 1252 (as it is exported), and do not change how it is being read into SQL Server.

Referring from this thread: Character Conversion Issue Importing From CSV File.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.