Link to home
Start Free TrialLog in
Avatar of al4629740
al4629740Flag for United States of America

asked on

Export long results in SQL to Excel

I have a problem.  I exported 3 columns of data in a query with 1300 records but the last column is a narrative which has a lot of data in it.  Is there a way to export this clean into an excel sheet from a SQL query?

When I try to Save Results, its a real mess so that does not work.  Even if I copy and paste it into excel it still is a hot mess.
ASKER CERTIFIED SOLUTION
Avatar of Tomas Helgi Johannsson
Tomas Helgi Johannsson
Flag of Iceland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
how's your data looks like and how do you import it into Excel?

data should be displayed correctly if it's in table format.

Is there a way to export this clean into an excel sheet from a SQL query? 
you can create a data query file which linked to your data source, and when you execute it (double click, for example), it should extract the data and display nicely in Excel.

test.dqy

XLODBC
1
DRIVER=SQL Server;SERVER=yourServer;UID=UserID;PWD=Password;APP=Microsoft Office;WSID=;DATABASE=yourDB
Select * from yourTable

Open in new window


Avatar of al4629740

ASKER

I am simply executing the query in the SQL management studio and wondered if there was a way to call the Excel program to place the output via SQL management studio.
I am simply executing the query in the SQL management studio and wondered if there was a way to call the Excel program to place the output via SQL management studio.

You mean to import Excel data into MS SQL Server?

if yes, you can read my article below as there are couple of ways can do that.

How to load Excel's data into SQL Server in different ways

https://www.experts-exchange.com/articles/34011/How-to-load-Excel's-data-into-SQL-Server-in-different-ways.html

Sorry if I confused.  No.  The opposite.    I need to Export TO Excel


I need to Export TO Excel

Ok, then you can try the first comment I have posted.
Pardon my ignorance, but am I suppose to take the code you gave and run it in the query window in sql management studio?  Because when I do, it does not work
you mean you want to export data to Excel via sql management studio?
Yes...sorry I didn't make that clear earlier.  My fault.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
you can use SSIS or Excel to directly linked up to data source for the extraction.
The problem is that when I have a column with a lot of data it looks like a mess and ends up pasting data on the next line and its absolutely useless.
The problem is that when I have a column with a lot of data it looks like a mess and ends up pasting data on the next line and its absolutely useless.  
If your source file is in tabular format and it's formatted properly, then you shouldn't have the issue on displaying it in Excel.

you need to know what's the delimiter your source file is using and use it to configure the output when import into Excel.
what do you mean source file?
how's your data looks like?

I have 3 columns.  the last one is called narrative.  data type is nvarchar(Max)

It’s basically a long narrative of sentences.  That’s the column that comes out looking messed up. There really is no delimiter
as mentioned, if you want to export to Excel, try use the existing wizard in Excel and it should import the data nicely, not try to do that via SSMS alone.