Need to Export the results to Excel

Question

Need to Export the results to Excel

bubby

SSCertifiable

Points: 5638
More actions
December 29, 2014 at 8:30 am

#293866

Hi!,
Help is appreciated, I am pulling a data for 1 year bit turns to be very huge, just want to export the data into Excel with Headers,
I am trying in 20082,
Thanks in Advance,
Bubby

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

JWOL Hall of Fame Points: 3471 More actions · Answer 1

Have you tried the SQL Server import and Export Wizard? Right-click on the database that holds the data you want to export and click Tasks > Export Data.

bubby SSCertifiable Points: 5638 More actions · Answer 2

I am not exporting the data, I am trying to run the query and want to save the results as csv files with headers, the data is huge

ManicStar SSCoach Points: 17992 More actions · Answer 3

ManicStar

SSCoach

Points: 17992

December 29, 2014 at 11:46 am

#1766830

Why can't you use SSIS?

JWOL Hall of Fame Points: 3471 More actions · Answer 4

If the data is in SQL and you want it in a csv then that is exporting the data. The wizard will also allow you to save the export as an SSIS package.

ManicStar SSCoach Points: 17992 More actions · Answer 5

bubby (12/29/2014)
I am not exporting the data, I am trying to run the query and want to save the results as csv files with headers, the data is huge

errr... that is the definition of exporting the data. exporting includes making copies of it in various file formats. just saying.

Lowell SSC Guru Points: 323504 More actions · Answer 6

sending data to a file is an export.

SQL, natively, does not support file operations; it's always up to an external application, so you use an applciation to connect and save, connect and export, etc,

The import export wizard is the first place to do this, as suggested. it's fast and intuitive.

next up would be to use SSMS, and click control+shift_f, then runnign a query; that will allow you to save to csv.

next up would be using darker magic like sqlcmd.exe or bcp.exefeaturing queryout most likely, which is executed from command window.

There are CLR functions (sqlclrexport.codeplex.com) you can use to export as well, but that's going to require enabling CLR, vetting the source code yourself before finally installing the assemblies.

bcp doesn't do headers very nicely, according to my notes so it gets involved when doing that: here's an example

--BCP to get headers

--Version 1: global temp table

--not i'm making sure my headcers are large enough for my data i insert later....

--otherwise CITYNAME wouldbe varchar(8), and I need maybe 100

SELECT

identity(int,1,1) AS BCPORDERID,

CONVERT(varchar(100),'CITYTBLKEY') AS CITYTBLKEY,

CONVERT(varchar(100),'CITYNAME') AS CITYNAME,

CONVERT(varchar(100),'COUNTYTBLKEY') AS COUNTYTBLKEY,

CONVERT(varchar(100),'COUNTYNAME') AS COUNTYNAME,

CONVERT(varchar(100),'COUNTYFIPS') AS COUNTYFIPS,

CONVERT(varchar(100),'STATETBLKEY') AS STATETBLKEY,

CONVERT(varchar(100),'STATECODE') AS STATECODE,

CONVERT(varchar(100),'CITYDESCRIP') AS CITYDESCRIP

INTO ##BCP_Results

INSERT INTO ##BCP_Results(CITYTBLKEY,CITYNAME,COUNTYTBLKEY,COUNTYNAME,COUNTYFIPS,STATETBLKEY,STATECODE,CITYDESCRIP)

SELECT

CITYTBLKEY,

CITYNAME,

COUNTYTBLKEY,

COUNTYNAME,

COUNTYFIPS,

STATETBLKEY,

STATECODE,

CITYDESCRIP

FROM VW_CITYCOUNTYSTATE

EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT CITYTBLKEY,CITYNAME,COUNTYTBLKEY,COUNTYNAME,COUNTYFIPS,STATETBLKEY,STATECODE,CITYDESCRIP FROM ##BCP_Results ORDER BY BCPORDERID" queryout MyFileName.txt -c -T'

--drop table ##BCP_Results

--Version TWO:

--export two files, then combine them together.

EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT ''CITYTBLKEY'',''CITYNAME'',''COUNTYTBLKEY'',''COUNTYNAME'',''COUNTYFIPS'',''STATETBLKEY'',''STATECODE'',''CITYDESCRIP''" queryout MyFileNameT1.txt -c -T'

EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT CITYTBLKEY,CITYNAME,COUNTYTBLKEY,COUNTYNAME,COUNTYFIPS,STATETBLKEY,STATECODE,CITYDESCRIP FROM VW_CITYCOUNTYSTATE" queryout MyFileNameT2.txt -c -T'

set @sql ='copy c:\MyFileNameT.txt + c:\MyFileNameT2.txt c:\MyFileName.txt'

EXEC master..xp_cmdshell @sql

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

MMartin1 One Orange Chip Points: 27768 More actions · Answer 7

Is this a one time event? If so I can offer a quick and dirty method :

1. In SSMS go to Tools > Options > Query Results>SQL Server> Results to Grid ...and put a check next to "Include column headers when copying or savings the results."

2. This will apply to new windows only, so open a new tab in SSMS.

3. Run your query in this new tab (or just open the .sql file if you had it saved already and run it in the tab that opens)

4. In the results pane click the upper left box where the columns and rows intersect. This will select all the data that resulted.

5. Right click and select Copy or Copy with Headers, either will work

Finally just paste this to Excel with CTRL+V.

Just be careful that Excel can only handle up to about a million rows per tab, or less if you are using an older version. Here you would do a save as in Excel and save it as a CSV file. See if this works for you.

----------------------------------------------------