Import and export CSV files

CSV is one of the most widely used formats in programming. It is easy to understand, edit, import, and export. It's a plain text file that can contain numbers and letters only, and structure data that is contained in a tabular or table form. It is harder to find a text editor that does not support CSV files than vice versa. Most of the Server Data Tools also support the import/export of CSV files.

The majority of open data is published in CSV format. So being familiar with CSV files gives you the opportunity to analyze data from any source, by simply importing them into your database and doing whatever you want to do. With that knowledge, you will be able to predict who would survive in the titanic disaster (kaggle titanic) or make your own research on criminal incidents in the USA (somerville-criminalincidents).

In this topic, we will learn some basics about CSV, for instance, how to import and export data. Let's begin!

What is CSV format and what does it stand for?

source for the icon

A CSV file is a comma-separated value, hence the abbreviation. It is a very popular format that is commonly used for data storage and manipulation tasks. The peculiarity of the CSV format is its simplicity. CSV format is mainly used to store tabular data.

How does it help businesses? CSV files are easy to create, read and write. Consequently, they provide a convenient way for business owners to manipulate these files in a variety of ways. This type of file provides a convenient and efficient way to import and export business-specific data, such as customer information, to and from your database.

You can use CSV Files in almost any text editor. After all, an Editor that can open a text file can also open a CSV file.

Import CSV file into MySQL table

Firstly, you need a MySQL database with the rights to drop, create and update tables. If you do not know how to create a database in MySQL—use this guide.

Secondly, you are required to have a tool to work in the MySQL database. There are countless numbers of them, in this topic, we will use DataGrip as the most useful and productive app.

We will use two ways to import a CSV file:

1) Query

2) DataGrip tools

We will import and then export data that is published in Kaggle case.

Query

First of all look at the CSV file. Check if the file structure is correct.

After that, we need to create a table where the CSV files will be imported.

CREATE TABLE test.test_data (
    PassengerID INT NOT NULL AUTO_INCREMENT,
    Class VARCHAR(255) NOT NULL,
    Name VARCHAR(255) NOT NULL,
    SibSp DECIMAL(10 , 2 ) NULL,
    Parch DECIMAL(10 , 2) NULL,
    Ticket DECIMAL (10, 2) NULL,
    Fare DECIMAL (10, 2) NULL,
    Cabin VARCHAR (255) NOT NULL,
    Embarked VARCHAR (20) NOT NULL,
    PRIMARY KEY (PassengerId)
);

Next, we use a command that will import data from a file into our table.

LOAD DATA LOCAL INFILE  'D:/data/test.csv'
INTO TABLE test.test_data
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

First row shows the path to our file that we will import.

Second row shows in which table our data will be imported.

Third explains what separates columns ( even if it is called CSV it is not necessary to be separated only by commas, it can be ";" or just tabs etc.)

Fourth row "\n – newline" means end of one line of text and start of another.

We ignore first row because it is name of the columns.

Last, but not least, a check-up;

SELECT * FROM test.test_data;

If the query gives you the result table, you did everything right, if it's not, then you should look for mistakes. We will talk about them later.

Using DataGrip tools

The best way for lazy people.

1. First, you click the schema you wish to import data to and choose "Import From File…" from the context menu.

2. Next, you select the CSV file where your data is stored.

3. Then in the dialog window you select the delimiter if the first row is the header (the separate format options are available for it), and specify if you have quoted values in the file.

4. After that, you click on the Import button, and watch the process of import.

5. Check-up.

SELECT * FROM test.data_test;

If the query gives you the result table, you did everything right, if does not, then you should look for mistakes.

Export CSV file into MySQL table

As in the previous task, we will try to use two ways:

1) Query

2) DataGrip tools

Query

SELECT * FROM data_test
WHERE gender='female'
INTO OUTFILE '/tmp/test_export.csv'
FIELDS ENCLOSED BY '"' 
TERMINATED BY ';'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n';

The CSV file contains lines of rows in the result set. Each line is terminated by a sequence of carriage return and a line feed character specified by the LINES TERMINATED BY '\r\n' clause. Each line contains values of each column of the row in the result set.

First two rows are our query.

Third row shows the path that will contain the result set.

Fourth row prevents us from the issue when the value may contain a comma (,) (will be interpreted as the field separator).

DataGrip tools

You can export your SQL query.

For this, you should click on the context menu on a statement, then choose "Execute to file" and then "Comma-separated(CSV)".

You can export your result table.

Click the "Export button".

Select the extractor, choose the path to the file where the result query will be exported, add columns header (it is optional), and click "Export to file".

Well done!

If you still have trouble with that, check JetBrains instruction.

Conclusion

Congratulations! You have completed the theoretical part of this topic! Let's make a little review of what we have achieved:

• CSV stands for comma-separated-value and the popularity of the format can be attributed to the fact that it can be created, read, and written with ease.

• We have learned how to import and export CSV files into MySQL tables (through query and through the DataGrip tool).

Now let's go to practice.