SQL Server – How to Export Query Output to Excel with Large Data Columns

sql server

I have a problem where when the data being exported from query output to excel, data is extending to multiple rows in the excel sheet as the number of characters are more, Is there anyway to handle this.

I have tried exporting the data using the export option in SSMS and save as CSV file option as well along with simple copy paste option. All having the same output .Ie single column data per row in SQL will be extended to multiple rows in the excel , instead of single row in same column.
This could be due to the spaces in the SQL data as well,
Any leads will be helpfull

Best Answer

I wonder if your "multiple rows" are not due to column data size but to invisible new-line symbols in the data. These will be confused with the new-line symbol inserted in CSV data after each row.

I have a similar effect in a current project, although I'm just displaying a result table in Management Studio and copy/pasting it into Excel. I fixed my issue by treating a column like this to change new-line to the symbols < br > .

REPLACE(IR.reportedBy, CHAR(10), '<br>')

In your case, new-line might be represented by CHAR or NCHAR(13), by CHAR(10), or by CHAR(13) followed by CHAR(10).

Another trick (which may not work in CSV) to avoid Excel treating data as a non-text formula is to output as,

'=' + QUOTENAME(column_name, '"') -- that's singlequote doublequote singlequote

https://www.theregister.co.uk/2016/08/25/excel_hell_messes_up_20_per_cent_of_genetic_science_papers/ explains how genetic scientists whimsically assigned names of genes like MARCH1 and SEP2 which Excel cheerfully converts to calendar dates. Without checking what I'm saying, I think my method will produce output such as ="MARCH1" which Excel will treat as a formula evaluating to the text MARCH1. Note that QUOTENAME is really a tool for object names in the database, so it truncates the output at around 125 characters (which is 128 counting the ="" part). Shorter if the string actually contains " which is converted to "", using up more.

Related Question