freshstartusa
asked on
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 . . .
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 . . .
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
D B
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.