Site hosting news, tutorials, tips, How Tos and more
Did you know that SQL Server Management Studio has an excellent tool to help you export the data contained in your tables to other useful formats? Here’s a guide on how to use the SQL Server Import and Export Wizard.
First, connect to your Winhost database using SQL Server Management Studio. For this tutorial, I am using the sample AdventureWorks database provided by Microsoft. Once you have logged into your database, right click on it and select Tasks -> Export Data…
This will launch the SQL Server Import and Export Wizard.
Click on Next to continue. Now, you will need to choose a Data Source. You can leave the Data source as SQL Server Native Client 10.0. Enter the Server name. Check SQL Server Authentication and enter the User Name and Password for your database. For the Database dropdown menu, select the name of your database.
Click on Next to continue. In the Choose a Destination window, you can select a variety of formats to export to including Microsoft Access, Microsoft Excel, and another Microsoft SQL Server database. For this tutorial, I will be showing you how to export your data to a flat file (i.e. .txt and .csv).
In the Destination dropdown menu, select Flat File Destination. For the File name, click on the Browse button, select Desktop and give the file a name. You can choose either .txt or .csv format by clicking on the dropdown menu next to the file name.
You can leave the Locale as English (United States) and Code page as 1252 (ANSI – Latin I). The Format will be Delimited and check Column names in the first data row. You can set a Text qualifier if you’d like, but I’m not going to.
Click on Next to continue. In the Specify Table Copy or Query window, check Copy data from one or more tables or views. If you check the other option, you can write your own SQL query (SELECT statement) to extract the data you want from two or more tables.
Click on Next to continue. In the Configure Flat File Destination window, I will be drawing data from the Employees table. In Source table or view, select [HumanResources].[Employee]. You can leave the Row delimiter as {CR}{LF} and the Column delimiter as Comma {,}
You may also want to spend some time looking at Edit Mappings. For a flat file, you can choose not to include certain columns to be exported by selecting the Destination column name and choosing <ignore> as shown below.
This tool is actually more powerful when you are exporting to an existing file that already has column names in place as you can map a column name from the Source file to a different column name in the Destination file.
For example, you have an Excel file with a column named BEID. You can map BusinessEntityID to BEID. The data will be populated in the BEID field in Excel. Click on Next to continue. This will bring up the Run Package page.
Click on Next to continue. The Wizard is complete and you can click on Finish.
The Wizard will now run through a series of checks prior to exporting the data. If everything goes smoothly, you should get the screen below.
If you run into any errors, there will be a link in red which you can click and review the problem the wizard is having. You can use the Back button to correct the problem and go through the steps again until you are able to successfully export the data.
THis dialog lacks clarity because to the novice user it is baffling why you should select a file (you are creating one). What happens when you select an existing file name – is it overwritten, appended. I should not have to research this!
You have the option to do either. That’s why I didn’t include it. Click on the Edit Mappings button in the Configure Flat File Destination window. You have a choice of creating the destination file, deleting the rows in the destination file, or appending the rows in the destination file.
Thanks, this is clear enough. i was able to export the data smoothly thanks to you 🙂
If you need to export or import data to/from sql server, you can do it easier with dbForge Studio for SQL Server.
yeees! clear truth! after I tried dbForge Studio, it became my constant tool for working with SQL server
I need to put all tables in the database out to a csv file and this process appears to only let me export one file at a time. How do I select multiple (or all) tables using this Flat File Destination? Is it possible, if not is the another way?
Hi Leslie. Change the destination from Flat File Destination to Microsoft Excel. This will allow you to select multiple tables. Then use Microsoft Excel to save it as a CSV file.
When I try to export from SQL(2008) to Access (to a table that already exists) I cannot highlight the “append rows to the destination table” button. The export fails since the table already exists.
What if you don’t have MS Excel on your server? You should be able to export database to MS Excel format without being required to install it on your server. Am I missing something? I just want to export the data in a pipe delimited format.
Exporting to a format Excel can read doesn’t require having Excel on the server. In this tutorial you’re exporting the SQL data to a comma delimited flat file, which you can then open in Excel. To change to pipes just change the “Column delimiter” setting in the above instructions.
As Rei mentioned in a previous comment, if you’re selecting multiple tables, change the destination from Flat File to Microsoft Excel, then save as a CSV file. That doesn’t require Excel to export the file either, but of course you’ll need Excel (or an open source Excel reader) to view it.
I tried to export data from an excel spreadsheet to a SQL server table, it seemed to work but when I looked at the newly created table the rows were not in the same order as the excel spread sheet. Why?
I am using this to migrate data from Oracle to SQL Server. The number of rows in my SQL Server table is increasing by one every time I do the migration. Any idea why?
Hi! thanks a lot for your informative article. To learn more about this topic, take a look at this detailed guide describing various options to import CSV files to SQL Server, including ways to automate (I mean schedule) the process and specify FTP or file storages for CSV location https://skyvia.com/blog/3-easy-ways-to-import-csv-file-to-sql-server