MSSQL: How Import/Export extended characters and newlines to/from csv/tab delimited?
Posted on 2007-08-11
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 . . .