2

So I decided for the fun of it to read a text file and store the contents into a NVARCHAR using TSQL and the Microsoft SQL Server Management Studio 2008 R2. I found an example for doing this at https://www.simple-talk.com/sql/t-sql-programming/the-tsql-of-text-files/

So I tried this with my ABC.txt file and its contents are:

ABCDEFGHIJKLMNOPQRSTUVWXYZ

abcdefghijklmnopqrstuvwxyz

When I first tried to store the contents of this file into@myString I used this code:

declare @myString nvarchar(max); 

Select @myString  = BulkColumn
from OPENROWSET(Bulk 'C:\Users\<myComputer'sNameHere>\Documents\How2\FilesForTestingStuff\ABC.txt', SINGLE_BLOB) as x 
print @myString;

I got this as my output when I printed the string:

䉁䑃䙅䡇䩉䱋乍偏剑呓噕塗婙਍扡摣晥桧橩汫湭灯牱瑳癵硷穹

I changed nvarchar to varchar and I got the correct contents of the file.

Anyone know why this happend? I didn't think that there's a conversion difference other than nvarchar has more space available than varchar and is able to hold unicode characters.
Also how do you normally attempt reading from a file and inserting the contents into a nvarchar?

Community
  • 1
  • 1
user3003304
  • 288
  • 1
  • 6
  • 18

1 Answers1

3

I suppose it depends on the encoding of the input file.

You used SINGLEBLOB and according to MSDN it causes data to be returned as varbinary(MAX). Your file was probably saved using a non-unicode encoding, so when it was imported data into nvarchar column, SQL interpreted it incorrectly. Changing the type allowed characters to be read correctly. Please try to encode the file with UTF-16 and try to import data into a nvarchar(MAX) variable.

Update

I tried to recreate the issue You described. I've saved a text file with ANSI encoding, run the import script and got the output similar to the one You posted in Your question. Then, I converted the file to UCS-2 Little Endian encoding and after running the script I got correct output.

To sum it up, if You want to use importing with SINGLEBLOB option, just convert the file with data to use UCS-2 Little Endian encoding and it should work correctly with nvarchar SQL type.

Reference links:

Lukasz M
  • 5,635
  • 2
  • 22
  • 29
  • Ok I understand the file reason part, but I do not know how to check the file's encoding and I can't find any TSQL examples of converting files. – user3003304 Feb 03 '14 at 21:47
  • You can use a text editor to check the encoding of a file. You can also save file with appropriate encoding using *SQL Server Management Studio*. Just click a button to create a new script file, enter the data, click on *Save as...*, then click a little arrow next to *Save* button and select *Save with encoding...*. From the *Encoding* list select ***Unicode (1200)*** and save the file. – Lukasz M Feb 03 '14 at 21:58
  • @user3003304 - you may find useful : http://www.joelonsoftware.com/articles/Unicode.html . To view file's encoding, you can use a hex editor e.g. Visual Studio has one built in, or I like to use : "xvi32". – Moe Sisko Feb 04 '14 at 00:12
  • 1
    So I changed my code to use SINGLE_NCLOB instead of SINBLEBLOB, and I changed the encoding to Unicode CodePage 1200. That makes it print out correctly with NVARCHAR. Thanks LukasZ M. – user3003304 Feb 04 '14 at 16:09
  • It should work with `SINGLEBLOB` as well. I'm glad my it helped :). – Lukasz M Feb 04 '14 at 18:33