This article shows three ways of how to move your data from SQL Server table or query to Excel or CSV file.
Export SQL table to Excel using Sql to Excel Utility
Perhaps the simplest way to export SQL table to Excel is using Sql to Excel utility that actually creates a CSV file that can be opened with Excel. It doesn’t require installation and everything you need to do is to connect to your database, select a database and tables you want to export:
After you press “Generate CSV” it will create a separate CSV file for each table in the selected folder.
Export SQL table to Excel using SSMS
There are two options for exporting the data from SQL Server Management Studio to a file.
1. Quick and easy
This option will work fast for you if you have both Excel and SSMS installed on the same machine. If you don’t have it or cannot access Excel for whatever reason, but you still need the data exported, go to the next option.
After writing your simple query that outputs the contents of a table or a more complicated query, you can save the result set into a .csv or .xls file. To do it you need to click on the top left rectangle which will select all of the data resulting from your query.
Then right-click on the result set and select either Copy or Copy with Headers, which will also select the column names, so you know what data represents in your column.
Then you simply go to an Excel file and paste the results into the spreadsheet.
2. Safe and secure
This option works great when you don’t have access to both SQL Server Management Studio and Excel on the same machine.
Open SSMS, right-click on a database and then click Tasks > Export Data.
After clicking Export Data, a new window will appear where you will have to select the database from which you want to export data.
After selecting the Data Source press Next and get to a window where you will have to select the Destination.
You will see a drop-down menu, like the one below where you will have to select Excel as the destination type.
The next step would be to browse to the location of where the file will be created and specify its name. Also, you need to select the version of Excel you want the file to be created for.
Then press Next and get to the next Wizard window where you have the option of either running a custom query to output custom data from your tables or just selecting all data from more tables.
Choose whatever option works best for you, but for now, let’s just say you want to export all data from a specific table and thus we will choose the first option.
The next window you will see in this case asks you to select the table or tables you want to fetch data from. Select the table you want to export data from and either press Next or Edit Mappings.
Press Next to get to the next step of the wizard. You will get to a window where you have the option of running the query “Right Now” and also creating an SSIS package.
After you’ve selected the options, press Next. You will get to the last stage where you will have to press Finish.
This will create a file in the file path you specified and with the file name you have selected in previous steps. If you open the file, you will see that the data is in that specific format.
Hai,
I got an error while exporting the table to excel sheet, since i load the trace file into table, after i export into excel sheet. I got error in review datatype mapping page while exporting.
tell me the suggestion.
Hi Rajkumar,
Can you please explain what trace file you are talking about? Maybe upload a screenshot of the error you’re getting, post it on an image-share website and then write a comment with the link?
Radu
Really nice way to export the data to an excel sheet. There is only an issue. I can’t use the filter option or pivot option. Both are disabled. Is there a solution to enable these options?
Hi Rudy,
Thank you for reading our blog. When do you encounter this issue? After exporting the Excel file?
I have .bak file consisting patients records, want to transfer into .csv or .xlsx file , can you please guide me how to do.
Hi Dr. Parii,
Thank you for reading our blog. We will write an article showing you how to retrieve records from a .bak file and export them to an Excel file.
Keep reading our blog for the solution to your issue, we will write an article soon.
@Dr Parii
First, you have to restore the .bak file in your MS Sql Server Management Studio Databases.
To do that:
Open SSMS, right click on a Databases and then click Restore Database. Then a window will appear, browse your .bak file from device and locate on it.
After you have successfully restored the .bak/database file, you can now follow the article/steps above:
2. Safe and secure
That’s all..
Hello Friend
i have retrieved my data from Sql using SQl Server management Studio . But After retrieving the data i want to save my data as .CSV for further quick access and performing some algorithms but i encounter with another problem which is notepad++ showing a message as data is too big to open for notepad++.
Any Solution???????
Regards
Hi Dr. Parii,
You can find on our blog a full tutorial on how to get data out of a .bak file and into Excel. The article can be found at: https://blog2.sqlbak.com/export-data-from-a-bak-file-to-excel/
Please post a comment if you are still encountering issues.
Good post, thanks for the tip, it’s very useful..
Greetings from Mexico
Hi Mr.Gheorghiu
In Save And Run Package Windows
Why we choose File System? Why Sql Server Not?
Thanks.
Hi. I build a c# Programm for import excel file to sql server table,my first row in excel is my sql table field name,
When I import data from excel to sql, my second excel row( my first data row) will be lost!, and I must insert my excel data’s from 3st row(1st is my field name, second is lost!!!) Pleas help me
Thanks.
I trust that it’s acceptable that I share this with
some of my clients, this will help their familiarity with options considerably.
I can’t get the export data on the excel format . How can export and get my data created by SQL to excel format. Please can you help me on this issues I need the excel format data ASAP it is very urgent.
Thanks
Hi,
Have you tried to do it via Sql to Excel Utility?
my export has following error message: Error 0xc0204016: SSIS.Pipeline: The “output column “MessageText” (131)” has a length that is not valid. The length must be between 0 and 4000.”
seems that the original SQL may be wrong, but I have no idea how (nor inclination) to change it.
Thoughts welcome.
DECLARE @cmd varchar(1000)
DECLARE @qry varchar(1000)
set @qry = ‘select * from sys.sysdatabases’
–SET @cmd = ‘bcp “select * from sys.sysdatabases” queryout “D:\test\database2.txt” -c -UTF8 -T -Slocalhost’ –# with literal text query
–print @qry
SET @cmd = ‘bcp ‘ + ‘”‘ + @qry + ‘”‘ + ‘ queryout “D:\test\database2.txt” -c -UTF8 -T -Slocalhost’ –# with declared variable query
–print @cmd
EXEC master..xp_cmdshell @cmd
Let me know if you have any questions.
do I need EXCEL on my server system ?
Thanks, for sharing such information.