Importing CSVs into a MySQL table from the terminal

Recently I ran into a situation where I needed to import a large CSV file into a MySQL database table DreamHost. Since my new computer wasn’t properly set up I decided to convert from CSV to SQL, and PhpMyAdmin would simply 504, I decided to try an approach that only required the terminal.

Step 1: Creating the table

First thing I did was create a new table for my CSV file. I tried to keep the character lengths rather small, but the character lengths where “guesstimates.” If I had no idea how to guess, i made the field 255 to be safe. In hindsight, I do know a way to get the maximum size character of every field within this dataset, but for now I’m leaving it as is…

CREATE TABLE table_name (
    -> id INT NOT NULL AUTO_INCREMENT,
    -> Make VARCHAR(25) NOT NULL,
    -> Model VARCHAR(50) NOT NULL,
    -> Year VARCHAR(10) NOT NULL,
    -> MMY VARCHAR(100) NOT NULL,
    -> `Color Name` VARCHAR(255) NOT NULL,
    -> `Color Code` VARCHAR(125) NOT NULL,
    -> `Hex Code` VARCHAR(10) NOT NULL,
    -> Tricoat BOOLEAN,
    -> `Handle Reference` VARCHAR(255) NOT NULL,
    -> PRIMARY KEY (id)
    -> );

Step 2: Rsync the CSV onto the server

At DreamHost you’ll have to first ssh into their environments if you want to utilize mysql cli. Easy method for me to do so was rsync…

rsync -e "/usr/bin/ssh" -av ~/table-source.csv user@ps######.dreamhostps.com:/home/user/

Step 3: Import the CSV file

Now I just needed to import the data set into my database! Normally, we’d do something like this…

mysql -h mysql.example.com -u username -p dbname < databasefile.sql

We obviously cant do that with a CSV file, so we could run the following comamnd on our MySQL server…

LOAD DATA INFILE '/home/user/table-source.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

This would work provided our CSV has all the proper fields (id, Make, Model, Year, MMY, Color Name, Color Code, Hex Code, Tricoat, Handle Reference), AND MySQL knows where to expect the file. It didn’t in my case. This I decided to utilize the following…

LOAD DATA LOCAL INFILE '/home/user/table-source.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

The LOCAL modifier affects these aspects of LOAD DATA, compared to non-LOCAL operation:

https://dev.mysql.com/doc/refman/8.0/en/load-data.html#load-data-local

Once the local Modifier was added everything worked as intended.

MySQL [database]> LOAD DATA LOCAL INFILE 'user/table-source.csv'
    -> INTO TABLE table_name
    -> FIELDS TERMINATED BY ','
    -> LINES TERMINATED BY '\n'
    -> IGNORE 1 ROWS;
Query OK, 153817 rows affected, 0 warnings (1.72 sec)
Records: 153817  Deleted: 0  Skipped: 0  Warnings: 0

Please be careful utilizing the content on this site. You can consider it my personal archive of notes that I keep in public for others to utilize or correct me. Should you find this information useful and you want to connect, or you want to correct or comment on a particular component that caught your attention, you are always free to contact me here.