How to import a csv file to Mysql?

Question

I am starting my studies in Mysql. I decided to install Mariadb 10.1.14 in Centos. I want to create a database from a file . csv with about 166 million lines. I looked for tutorials on the internet, but it seems that they all assume that the user knows something a priori about databases, which is not my case. Also, I’m finding the official documentation of Mariadb a little complicated, with few examples.

This put, suppose I have the test.csv file and its content is as follows:

UF,ValorParcela,MêsCompetência
PE,185.00,01/2015
AM,147.00,01/2015
PR,232.00,01/2015
PE,310.00,01/2015
PB,463.00,01/2015
CE,182.00,01/2015
AL,112.00,01/2015
MG,112.00,01/2015
BA,112.00,01/2015

Let’s assume that I want to import the contents of this test.csv into a database bag. So far, I’ve found that I need to do the following:

CREATE DATABASE bolsa;
USE bolsa;
LOAD DATA LOCAL INFILE teste.csv;
INTO TABLE bolsa
FIELDS TERMINATED by ','
LINES TERMINATED BY '\n';

Only this code is not correct. The error messages are not clear to me. I would really appreciate it if someone could shed some light on how to solve this.

Answer 1

Come on, you need to store these values in a table. Your mistake is to want to store them in a Database or Database. The Database serves to store the tables, after you have created your database with command:

CREATE DATABASE bolsa;

You need to follow with what you have already done, we will use the Database Exchange with command:

USE bolsa;

After this, let’s go to the step that was missing for you that is to create a TABLE, with command: NOTE: The columns have to be equivalent to the ones in the CSV.

CREATE TABLE estados (
id INT NOT NULL AUTO_INCREMENT, 
UF VARCHAR(255) NOT NULL, 
valor_parcela VARCHAR(255) NOT NULL, 
mes_competencia DATE NOT NULL 
);

After creating the table, then you will enter the values as below:

LOAD DATA LOCAL INFILE '/home/marcus/teste.csv' 
INTO TABLE users 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n' 
IGNORE 1 ROWS 
(UF, valor_parcela, @mes_competencia)
SET mes_competencia = STR_TO_DATE(@mes_competencia, '%m/%y');

I will explain every detail above:

• The command LOCAL is where the file is expected to be found. Your program will read the file, through your host and send to the server, to know more indicor you read the documentation Reference Documentation.

• Since the virgular is the delimiter, we use FIELDS TERMINATED BY '.

• Each CSV line is closed with a new line character LINES TERMINATED BY 'n'

• We already have the description of the columns in the table created, so we ignore the first row of the CSV file which are the table names IGNORE 1 ROWS.

• The default date format for a database table is YYYY-MM-DD. As you are using different date format in your CSV, being month/year, different than expected, so we need to change the format using the function STR_TO_DATE().

I hope I’ve helped.

I took a test in a simulator and it worked.

OBS: The test was done in an Online Editor and Compiler, so it is not possible to consider lyrically the error presented, even because I do not have this file also.

Source: How to import a CSV file into a Mysql database?

Answer 2

Vitor Nascimento’s response is correct. But in many cases the Mysql server does not authorize the use of the "LOAD DATA INFILE" command To check this out: Connectar using a Terminal, and type:

 mysql -u root -p
 show variable like "%local%";

This will show the value of the local_infile parameter that must have the value 'on' The other thing to check is if the user has the right "FILE" in the table (direct other than INSERT for example) For that reason:

  select user,File_priv from mysql.user;

Will give a table with each user’s right (y = yes, n = no)

Another option if you don’t know how to use Terminal, and use Phpmyadmin or Adminer (personally, I no longer use Phpmyadmin because it is heavy) Take a look at the user’s "Privilege", to see if he has the direct 'FILE' and at the level of the Server parameters, check the value of the local_infile variable

Answer 3

Try ignoring the first line and passing the full csv file path:

LOAD DATA INFILE 'c:/tmp/teste.csv';
INTO TABLE bolsa
FIELDS TERMINATED by ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

Source: http://www.mysqltutorial.org/import-csv-file-mysql-table/

Answer 4

The error is in ";" after the file path. If taking it will work it is a beauty

CREATE DATABASE stock exchange; USE POUCH; LOAD LOCAL DATA INFILE "c:/test.csv" INTO TABLE BAG FIELDS TERMINATED by ',' LINES TERMINATED BY ' n';