Import .CSV files into MariaDB

This article is going to show you, how to upload .csv files into mariaDB. Here we are using mariaDB 10.3 on ubuntu18.0

We need to install pip3, and csvsql tool kit as well as mysql-connector and some other dependencies.

once Installation finishes we will follow below steps to import the data using csvsql

1. Install csvsql toolkit and its dependencies
2. Create a new schema
3. Create table according your csv file (data type)
4. Import the .csv file to mariaDB

1.Install pip3 & csv toolkit:

apt install python3-pip

pip3 install csvkit

pip3 install mysql-connector-python

2. Create a schema:

CREATE SCHEMA ‘csvfiles’ DEFAULT CHARACTER SET utf8mb4 ;

3.We have taken some sample data as show below

Name,Sex,Age,Height,Weight
Alex,M,41,74,170
Bert,M,42,68,166
Carl,M,32,70,155
Dave,M,39,72,167
Elly,F,30,66,124
Fran,F,33,66,115

so we need to create the table according to the csv file header data type, we are going to create a table called biostats as show below

CREATE TABLE biostats2 (
Name VARCHAR(255),
Sex CHAR(1),
Age INTEGER(10),
Height INTEGER(10),
Weight INTEGER(10)

);

table created

4. Importing csv file using csvsql command as show below

csvsql — db mysql+mysqlconnector://root:raghu123@localhost/csvfiles — insert — tables biostats2 — no-create biostats.csv

table before importing data

csvsql command execution

Now connect to mysql shell and check the data

command output

We have successfully imported .csv file to mariaDB

References:

1. https://csvkit.readthedocs.io/en/1.0.2/scripts/csvsql.html