Exporting VARCHAR MAX and NVARCHAR MAX to CSV

By /

Recently, I had to export some query results to CSV and Excel. One of the columns was extremely large query text which was defined as NVARCHAR(MAX). Here are some of the issues I faced with this request and how I over came them.

Initial setup and thought process

The request was to pull query text and performance numbers from the Microsoft Database Experimentation Assistant outside of the provided UI. For those unfamiliar with the Database Experimentation Assistant, this tool takes a load captured in one version of SQL Server and replays the load in a different version of SQL Server. The comparison results are stored within SQL Server itself and displayed using the Microsoft provided UI. Within the UI, the end users sees a detailed report of queries that improved, degraded, or saw no change. The application provides results in a user friendly format, but my users wanted the actual data, outside of the UI. The results include the actual query text, which includes commas, line breaks, tabs, and other special characters.

I thought that simply running the query and saving the results as a CSV file would do the trick, but that was not the case.

Save results to file
Results to File

Line breaks in VARCHAR and NVARCHAR MAX

The first hurdle I had to over come was dealing with line breaks inside of the VARCHAR and NVARCHAR fields. I didn’t notice this issue at first since the first 200 or so records contained relatively small text. However, about 2,000 records in, I experienced line breaks in my file. This was because the text of one of the columns contained line breaks within the text itself. As you may already know, the carriage return and line feed are represented by the character codes 

CHAR(13) - Carriage Return 
CHAR(10) - Line Feed

My soulution was fairly straightforward. I simply replaced the carriage return and line feed with a space. This kept the script in one line and would not cause any syntax issues. Here is the script to replaced the carriage return and line feed with spaces. 

-- Replace carriage returns and line feeds with spaces
UPDATE dbo.example
SET outText = REPLACE(REPLACE(outText, CHAR(13), ' '), CHAR(10), ' ')
WHERE outText LIKE '%' + CHAR(13) + '%'
   OR outText LIKE '%' + CHAR(10) + '%';

VARCHAR(MAX) Truncation

Now, with the text exported, I ran into another issue: SQL Server was truncating the VARCHAR(MAX) output. There is a setting to increase the column output length in results sets sent to text.

However, several rows in my data set had extremely long character strings. Some of them went well over 8,000 characters. After doing some online research, the consensus was to utilize SSIS to export the query text. I didn’t want to open up Visual Studio and create a new project for a one-time export. So instead of utilizing the full SSIS client, I utilized the Import / Export wizard built into SQL Server Management Studio. Behind the scenes, the Import / Export wizard is running a small SSIS project, and this a great way to get the benefits of SSIS without creating a new project.

Newer Versions of SQL Server Managment Studio will allow you to export more data. Be sure to upgrade to the latest version for powerful new features.

Commas in Text Results

The next issue I experienced was Excel was not respecting my custom delimiter. I’ve set my delimiter to the tilde (~) since the tilde is rarely used on it’s own. However, Excel was still adding additional columns in my output because some of the VARCHAR(MAX) records contained commas.

My first course of action was to prefix and append quotation marks around the VARCHAR(MAX) output. But that didn’t work as expected. Excel was not respecting the custom delimiter or the quotation marks.

After doing more research, several users reported that Excel treats commas in CSV outfiles as a delimeter. Excel ignores the delimiters regardless of the quotation marks. Again, I had to cheat a bit with REPLACE. This time I replaced the commas within the VARCHAR(MAX) columns with dashes. 

-- Replace all commas with dashes in the outText column
UPDATE dbo.example
SET outText = REPLACE(outText, ',', '-')
WHERE outText LIKE '%,%';

Since this data wasn’t for production purposes, I felt comfortable changing the commas to dashes.

Summary

Though SQL Server has many powerful export tools built in, we sometimes have to massage the data a bit before export. Be sure to add the REPLACE command and the Import / Export wizard to your daily toolbox.

Related Posts