MS SQL :Export data from query output to excel which has a column with huge data

Question

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

Answer 1

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.

Answer 2

Welcome to hell. I've found that the most reliable way to get data from SQL Server to Excel is to use the querying capability from within Excel itself. The location of this function has moved around in recent releases, but right now, it's under Data, Get Data, From Database. This usually gets the best results for various data types, long data, etc.

Answer 3

One thing I learned about saving SQL query results into Excel format or CSV format is that it’s best to use the SQL Import and Export Wizard rather than trying to right-click on the results and saving them.

Exporting data from SQL is a fairly long process and involves several steps, but once you do it a few times, it’s not bad.

You can always right-click on Results and choose Save Results As, however, the CSV file that you get will not contain the column headers.

To export SQL data the correct way, right-click on the database (not the table) and choose Tasks, Export Data.

In SQL Server Import and Export Wizard:

• You need to choose the Data Source. If you right-click on the database name, then everything should come up automatically.

• Now you need to choose the Destination. Go ahead and pick Microsoft Excel from the drop down list. After that, you will need to click Browse and choose the location for the output Excel file. Also, be sure to keep the “First row has column names” box checked.

• you can either choose export an entire table/view to Excel or to write your own query. In my case, I will write a specific query that I want to export.

• Type in your query and then click Parse to make sure that everything is correct.

• Finally, click Next and then click Finish. Your SQL data will be outputted into an Excel file and it will include all the column headers too.

More info check this: Copy a large data from sql query result

Answer 4

The best way is to install SSMS plugin - SSMSBoost add-in and script data in excel

note: the plugin is free - community edition and has a prof. license as well. I have used the community edition and it works just fine - only thing is that you have to re-register the community edition after 30 days.

Answer 5

I agree that the problem is probably embedded line breaks in the data.

I also agree that exercising full control when formatting your CSV file is your best option. The CSV format that generally works best is:

• commas separating fields
• all non-numeric fields within double quotes, with any embedded double quotes doubled (i.e., John becomes "John", and David "RD" Francis becomes "David ""RD"" Francis")

By double-quoting your text, you can even have embedded line breaks handled properly.

You can modify a query to output data that's ready for a CSV file without too much difficulty:

Instead of:

SELECT first_name
      ,last_name
      ,company
      ...

Change to:

SELECT '"' + REPLACE(first_name, '"', '""') + '"' as first_name
      ,'"' + REPLACE(last_name, '"', '""') + '"' as last_name
      ,'"' + REPLACE(company, '"', '""') + '"' as company
      ...

This will change:

    John           Smith          JSCo
    Larry "Bud"    Melman         Worldwide Pants
    Mike           Jones          Split
level name

Note that the thirs company name has a line break in it.

to:

    "John"             "Smith"          "JSCo"
    "Larry ""Bud"""    "Melman"         "Worldwide Pants"
    "Mike"             "Jones"          "Split
level name"

Exported as CSV, this will open in Excel with that third company name in a single cell.