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

Posted on 2007-08-11
Medium Priority
Last Modified: 2008-09-13
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 . . .
Question by:freshstartusa
  • 2
LVL 15

Accepted Solution

dbbishop earned 500 total points
ID: 19678214
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.
LVL 15

Expert Comment

ID: 19678218
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.
LVL 19

Assisted Solution

weellio earned 500 total points
ID: 19678245

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question