The Easiest Way to LOAD DATA into MySQL is Using MySQL Workbench!

Being a bit of a noob with this MySQL stuff, I was banging my head against a brick wall for like an hour trying to work out how to get the damn - MySQL LOAD DATA - syntax to work. Finally, I realized “why don’t I do it the easy way and use this tool I’ve got installed called 'MySQL Workbench'!”

I felt like such a plonker. Still I would love to know why my LOAD DATA was failing. I will find out someday!

This is where I got to with the MySQL command line. I did not understand why I was getting access denied. Maybe it was something to do with my SQL user ‘dwh’ not being able to access the test.csv filepath...?

mysql> LOAD DATA INFILE 'C:\\Temp\\test.csv'

    -> INTO TABLE test

    -> COLUMNS TERMINATED BY ','

    -> LINES TERMINATED BY '\r\n'

    -> IGNORE 1 ROWS;

ERROR 1045 (28000): Access denied for user 'dwh'@'%' (using password: YES)

MySQL Workbench saved the day!

With MySQL Workbench, it is very easy to right click the table you want to load the data into and go to the ‘Table Data Import Wizard’ which is very easy to use.

Image: MySQL Workbench saved the day - Table Data Import Wizard

I also learnt more about creating tables today. And creating indexes to make searching the tables faster.

CREATE TABLE IF NOT EXISTS `your_table` (

 id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,

 someInteger INT,

 someName VARCHAR(256),

 anotherName VARCHAR(256)m

 INDEX idx_someName (`someName`),

 INDEX idx_anotherName (`anotherName`)

);