MSSQL: How Import/Export extended characters and newlines to/from csv/tab delimited?

I need to be able to import and export data from tables in MSSQL 2000 and MSSQL 2005. That data may contain extended (utf-8) international characters. It may also contain carriage returns.

I need to be able to export such data to a csv and/or tab delimited text file and then to be able to re-import the data back using the same process without losing the extended characters or newlines. The data was entered using a simple web based form and text area.

When I export such data and look at it in a text file, it seems like the newline characters are missing (it makes sense that I'm not going to see a record on multiple lines as csv and tab delimited are one record per line, but I would have thought the newline data would be encoded). When I re-import the newlines appear to be gone.

I know I could manually write a routine to transform chr(10) and chr(13) into another string, but I want to be able to take advantage of SQL server import and export mechanisms as they are quicker than processing in a scripting language.

Am I missing something? DOES SQL server encode the new lines? If so, how? What is the most elegant solution to be able to export and import data including utf-8 i18n data and newlines without losing any information and in a way that I can best interop with other programs?

Any thoughts/help much appreciated. If you have the perfect answer for new lines but don't know about i18n, that's worth some points . . .
Who is Participating?
As far as I can figure out, you would have to convert char(10) and char(13) to something else, and then do an UPDATE after importing them back into SQL Server, to replace the converted characters back into char(10) and char(13). By definition, I don't see any way a CSV file could contain a carriage return or linefeed in the data, as that would indicate an end of line and probably raise some kind of error when you tried to import the CSV file into another application (or back into SQL Server.
As far as utf-8 encoding, I do believe a CSV format will support extended character set without any special formatiing (make sure your data type in SQL Server is NCHAR or NVARCHAR.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.