Characters getting corrputed copying from nvarchar to text
SQL Server 2005. I was asked to get the contens of an Excel spreadsheet into a SQL Server table my manager had set up that had a couumn with datatype text (temp_catalogue_master). After a few false starts I ended up using the SSIS Import Wizard which created a table (UG_Programs) where the column in question (catalog_text) had a data type of nvarchar(max). I wrote a simple query to populate temp_catalogue_master from ug_programs insert into dbo.TEMP_CATALOG_MASTER select convert(char(10),CRS_CDE), convert(text,CATALOG_TEXT) from dbo.UG_Programs That worked fine but there is a problem with the data in the catalog_text column in temp_catalog_master. wherever the was the combination of letters "fi" on the ug_Programs side the is a question mark "?" on the temp_catalog_master side. Can anybody explan this and, better yet, tell me how I can tweak my Insert query so that the data will be populated coorectly in the column with data type Text? Thanks, Dick Rosenberg
I completely understand that. what I don't understand is why the combination of the letter "f" followed by the letter "i" (both valid ascii values, not in some esoteric alophabet that Unicode was designed to handle) on the nvarchar side are showing up as question marks on the text side (all the other "f"s and "i"s are fine. Dick
I'll provide some more information. I thought, "What if I save my spreadsheet in a text format and tried to work with the text file?". Well, doing that puts the question marks in where the fi combinations were (at least when viewed by Notepad).
Oh, and another thing in response to ThomasRushton's answer. The ultimate destination of the data in question is a database provided by the vendor of our system, so I have no control over the datatype in that table. It has to be text. Thanks, Dick
ThomasRushton put something in an email the system sent me (which I can't see from here. I'm new to this system). His comment was The only thing I can think of for that "fi" thing is if there's some language / word out there that has a separate character that something has autocorrected from "fi"... In the original text, can you select the "f" & "i" individually? I believe he is correct. when I open up the Excel spreadsheet I cannot select the "f" and the "i" individually. I kind of reached the same realization. In my text column I converted all of the question marks to "fi". I then selected from each table and saved the results as .csv files. I then used a file compare utility to compare them. It showed all the lines that originally had the question mark as being different than the lines that came from the Unicode table. And I couldn't separate select the "f" from the "i" in that utility program either. So the problem is solved (or at least explained). Thank you ThomasRushton and I will try and give you a positive vote. Dick