SQL Server Export Wizard: Export large amount of data to CSV

dream resort tropical
Share on FacebookTweet about this on TwitterShare on Google+Share on LinkedInShare on RedditShare on Tumblr

Tools used: Microsoft SQL Server 2014 Express and SQL Server Export Wizard
Related article: SSIS Package How to export data to CSV file

Sometimes while working with databases, it is necessary to import or export large amounts of data.
Those operations are called Bulk Import/Export operations and are an efficient way to copy data between a SQL Server and a data file.

Export large amount of data to CSV

This article explains how to export large amounts of data from one table to CSV file using SQL Server Export Wizard.
Exporting data to CSV is an easy process but sometimes it can be painful. For my own experience as SQL Developer exporting data types like STRING and DATETIME can cause lots of problems.
Here are some tips/best practises:
– String data has to be quoted. If not comma will separate the string as different columns in CSV.
– The first two characters of the file have to be different than uppercase letters “I” and “D”, otherwise you get an error while trying to open the file “SYLK: File format is not valid”. Read more about this topic.
– DateTime value has to use Microsoft Excel formats.

In this article I will use a table with 4 columns. Each column is a different data type.

CREATE TABLE [DummyData]
(
[IntValue] INT PRIMARY KEY IDENTITY(1,1),
[NvarcharValue] NVARCHAR(200) NULL,
[FloatValue] FLOAT NULL,
[DateTimeValue] DATETIME NULL
)
GO
INSERT INTO [DummyData]
VALUES('string, with, comma,',24.743,GETDATE()),
(NULL,76.234,GETDATE()),
('string, with, comma,',NULL,GETDATE()),
('string, with, comma,',12523.453,NULL)

SELECT * FROM [DummyData]

SQL Server Export Wizard

1. Open SQL Server Management Server and connect to a MsSQL instance. From Object Explorer right click on the database that contains the table you want to export. Select Task > Export Data… and SQL Server Import Export Wizard opens.
Export Data from SSMS
There is an easier way to open this tool by going to Start and search/type Import and Export Data – choose 32 bit or 64 bit version.
Import and Export Data (64-bit) tool

2. Choose a Data Source. In this example the source is a database and the destination is a data file (CSV). Select Data Source, Server Name and Table. Press Next to choose a destination.
Choose Data Source

3. From Destination dropdown select Flat File Destination. Select an empty CSV file. Check Column names in the first data row. Click Next and Specify Table Copy or Query.
Choose Destionation
Show CSV files
checked

4. Because our data need to be formatted select Write a query to specify the data to transfer.
Specify Table Copy or Query

5. Write the query here or load one (Browse). Click Next to continue.

SET NOCOUNT ON
SELECT
[IntValue],
QUOTENAME([NvarcharValue], '"') AS [NvarcharValue],
[FloatValue],
CONVERT(VARCHAR(19), [DateTimeValue], 120) AS [DateTimeValue]
FROM
[DummyData]

Source Query

Configure Flat File Destination

6. I use SQL Server Express Edition and because of this I can’t save the package, but if you use Standard, Enterprise, Developer or Evaluation, at this step you can save this configuration as a SSIS package.
Run Package

Successful execution

… the execution was successful 🙂

  • Antanas

    im getting error that dummydata not found//