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 a CSV file using MsSQL Server Export Wizard.
Exporting data to CSV is an easy process but sometimes it can be painful. From my own experience as SQL Developer exporting data types like STRING and DATETIME can cause lots of problems.
Here are some tips/best practices:
- 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
- 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.
- 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.
- 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.
- 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.
- Because our data need to be formatted select Write a query to specify the data to transfer.
- 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]
- 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.
… the execution was successful 🙂