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

Posted on 2007-08-11
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
    LVL 15

    Accepted Solution

    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

    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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    Viewers will learn how the fundamental information of how to create a table.

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now