1 Introduction
2 Reading csv-files
- 2.1 via SQL Query
- 2.2 via SSMS
3 Write to csv
4 Conclusion

1 Introduction

Before we get into the interesting things like Data Wrangling, Aggregation and Manipulation, I would like to briefly explain the import and export of files in SSMS.

For this post I created a TestCSV.csv and used the dataset flight from the statistics platform “Kaggle”. You can download them from my GitHub Repository.

2 Reading csv-files

Since we are working with Server Management Studio (SSMS) from Microsoft, I will show how to load data in two ways with this setup.

2.1 via SQL Query

To be able to load a table into SSMS via the bulk command, we must first create it on the database. The column names and their order must be identical.

So in the first step we create a database and a corresponding table.

How I create the database and the tables can be read in my post Create a Database.

CREATE DATABASE Read_And_Write;

USE Read_And_Write;



CREATE TABLE Test_CSV_Table
    (ID_testCSV INT NOT NULL,
    First_Name VARCHAR(100) NOT NULL,
    Last_Name VARCHAR(100) NOT NULL,
    Gender VARCHAR(100) NOT NULL)
    ;

SELECT * FROM Test_CSV_Table

Here we go!

Now we use the bulk command to load the TestCSV.csv into our created table. I have placed the TestCSV in a folder (‘datasets’) on my desktop for this purpose.

BULK INSERT Test_CSV_Table
    FROM 'C:\Users\Michael Fuchs\Desktop\datasets\testCSV.csv'
    WITH (FIRSTROW = 2, FIELDTERMINATOR = ';', ROWTERMINATOR = '\n');

SELECT * FROM Test_CSV_Table

Has worked.

Of course, in real life you usually have to deal with tables that have far more columns. In order to avoid having to create them individually in order to be able to load data, there is another more convenient way to load data in SSMS.

2.2 via SSMS

In the Object Explorer we see our created (and now also filled) table.

Let’s now try to load the data set flight from “Kaggle”. This dataset is also stored in the ‘datasets’ folder on my desktop.

To do this, right-click on the database (‘Read_And_Write’) - Tasks - Importing Flat File…

Navigate to the location where the file is stored.

Now you get a short overview of the dataset to be loaded.

You can also make some modifications to the columns at this point:

One last summary check:

Perfect, our new record is loaded:

Caution!

When importing data as just shown, it is important to pay attention to the format in which the data is stored, for example in a .csv file. In my case I have downloaded the German version of SSMS and set up the database accordingly.

With a data set with the following formatting SSMS (via the Importing Flat File function) would get problems:

The data should therefore be changed as follows (comma to semicolon and dot to comma):

3 Write to csv

Now we have loaded two tables into our database in two different ways. Let’s say we have enriched one of them with more data. Here, for example, the Test_CSV_Table:

INSERT INTO Test_CSV_Table (ID_testCSV, First_Name, Last_Name, Gender) VALUES (4, 'Michael', 'Fuchs', 'male')

SELECT * FROM Test_CSV_Table

Now we want to extract the completed table and save it as a csv file (for example, to continue working with it in another programme). SSMS also offers a convenient solution for this:

To do this, right-click on the database (‘Read_And_Write’) - Tasks - Export Data…