Thursday, December 29, 2016

Creating a CSV file from SQL Server 2012 Data

Often you may require exporting data from SQL Server to other locations with a different structure such as XML, JSON or CSV.

From the earliest versions of SQL Server, the Data Transformation Wizard was a part of the tools in converting data in the SQL Server tables to other formats.

Herein I describe how to transform data in the Northwind Database tables to a delimited text file using the wizard in a couple of easy steps.

In Step 1 you need to bring up the wizard. It is easy and you can do it from SQL Server Management Studio as shown in this figure below.


You click on Export Data... to invoke the wizard who will guide you through the process. Actually there is a welcome scree after which this screen is displayed. You need to choose a data source from which you want to export data. The program finds a server already installed and it also uses the default authentication, namely Windows Authentication. If you have followed SQL Server 2012 installation in my blog you would notice that this was the type of authentication configured at installation time. Using the Refresh screen the Northwind database is also chosen as the database chosen from which data will be exported.


Click Next brings up the next step in the wizard as shown. The Choose a Data Destination window is displayed. since we plan on exporting data into a delimited text format, create an empty file (herein FFD.txt) with extension text(CSV) and save it to a location of choice. For Destination choose Flat File Destination. Browse and locate the empty file you created. You may accept other defaults unless you want to change.


Click Next to get to the next step as shown. We will use a query to specify the data to be transformed.


Click Next. We need to provide a query to get the data from the database. Here is the query that is going to be used.
SELECT Products.ProductName, [Order
Details].UnitPrice, [Order Details].Quantity, Orders.OrderDate, Orders.RequiredDate
FROM     [Order Details] INNER JOIN
                  Orders ON [Order Details].OrderID = Orders.OrderID INNER JOIN
                  Products ON [Order Details].ProductID = Products.ProductID
Where [Order Details].UnitPrice>100

The next window needs a query to be processed.


Insert the previously shown query. You can hit the Parse button to verify that the query is good. You can also Browse and get the query from a saved file.

Click Next to display a screen to configure the destination as shown.

Step 7.png

Click Next. The summary screen is displayed as shown.

Step 8.png

You are almost done. Hit the Finish button. The program processes your request and comes up with success or failure. In this case success.

Step 9.png

You are done. You have successfully exported data to a delimited text format from SQL Server Tables.

PowerShell Pro Tools is a Visual Studio Community 2017 extension that you can try free

I see there is at least one extension, PowerShell Pro Tools that you can download. You can find it in the Visual Studio Community 2017...