How do I import CSV file into a MySQL table?

user_111424 0
Viewed 1166027

I have an unnormalized events-diary CSV from a client that I'm trying to load into a MySQL table so that I can refactor into a sane format. I created a table called 'CSVImport' that has one field for every column of the CSV file. The CSV contains 99 columns , so this was a hard enough task in itself:

CREATE TABLE 'CSVImport' (id INT);
ALTER TABLE CSVImport ADD COLUMN Title VARCHAR(256);
ALTER TABLE CSVImport ADD COLUMN Company VARCHAR(256);
ALTER TABLE CSVImport ADD COLUMN NumTickets VARCHAR(256);
...
ALTER TABLE CSVImport Date49 ADD COLUMN Date49 VARCHAR(256);
ALTER TABLE CSVImport Date50 ADD COLUMN Date50 VARCHAR(256);

No constraints are on the table, and all the fields hold VARCHAR(256) values, except the columns which contain counts (represented by INT), yes/no (represented by BIT), prices (represented by DECIMAL), and text blurbs (represented by TEXT).

I tried to load data into the file:

LOAD DATA INFILE '/home/paul/clientdata.csv' INTO TABLE CSVImport;
Query OK, 2023 rows affected, 65535 warnings (0.08 sec)
Records: 2023  Deleted: 0  Skipped: 0  Warnings: 198256
SELECT * FROM CSVImport;
| NULL             | NULL        | NULL           | NULL | NULL               | 
...

The whole table is filled with NULL.

I think the problem is that the text blurbs contain more than one line, and MySQL is parsing the file as if each new line would correspond to one databazse row. I can load the file into OpenOffice without a problem.

The clientdata.csv file contains 2593 lines, and 570 records. The first line contains column names. I think it is comma delimited, and text is apparently delimited with doublequote.

UPDATE:

When in doubt, read the manual: http://dev.mysql.com/doc/refman/5.0/en/load-data.html

I added some information to the LOAD DATA statement that OpenOffice was smart enough to infer, and now it loads the correct number of records:

LOAD DATA INFILE "/home/paul/clientdata.csv"
INTO TABLE CSVImport
COLUMNS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

But still there are lots of completely NULL records, and none of the data that got loaded seems to be in the right place.

load-data-infile csv mysql
21 Answers

If you are using MySQL Workbench (currently 6.3 version) you can do this by:

  1. Right click on "Tables";
  2. Chose Table Data Import Wizard;
  3. Chose your csv file and follow the instructions (JSON also could be used); The good thing is that you can create a new table based on the csv file you want to import or load data to an existing table

enter image description here

user_55481090

I see something strange. You are using for ESCAPING the same character you use for ENCLOSING. So the engine does not know what to do when it founds a '"' and I think that is why nothing seems to be in the right place. I think that if you remove the line of ESCAPING, should run great. Like:

LOAD DATA INFILE "/home/paul/clientdata.csv"
INTO TABLE CSVImport
COLUMNS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

Unless you analyze (manually, visually, ... ) your CSV and find which character uses for escape. Sometimes is '\'. But if you do not have it, do not use it.

user_7830840

Yet another solution is to use csvsql tool from amazing csvkit suite.

Usage example:

csvsql --db mysql://$user:$password@localhost/$database --insert --tables $tablename  $file

This tool can automatically infer the data types (default behavior), create table and insert the data into the created table. --overwrite option can be used to drop table if it already exists. --insert option — to populate the table from the file.

To install the suite

pip install csvkit

Prerequisites: python-dev, libmysqlclient-dev, MySQL-python

apt-get install python-dev libmysqlclient-dev
pip install MySQL-python
user_13001700
user_4238680

I use mysql workbench to do the same job.

  1. create new schema
  2. open newly created schema
  3. right click on "Tables" and select "Table Data Import Wizard"
  4. give the csv file path and table name and finally configure your column type because the wizard set default column type based on their values.

Note: take a look at mysql workbench's log file for any errors by using "tail -f [mysqlworkbenchpath]/log/wb*.log"

user_10106190

How to import csv files to sql tables

Example file: Overseas_trade_index data CSV File

Steps:

  1. Need to create table for overseas_trade_index.

  2. Need to create columns related to csv file.

    SQL Query:

    ( id int not null primary key auto_increment,
series_reference varchar (60),
period varchar (60),
data_value decimal(60,0),
status varchar (60),
units varchar (60),
magnitude int(60),
subject text(60),
group text(60),
series_title_1 varchar (60),
series_title_2 varchar (60),
series_title_3 varchar (60),
series_title_4 varchar (60),
series_title_5 varchar (60),
 );

  3. Need to connect mysql database in terminal.

    =>show databases;
=>use database;
=>show tables;

  4. Please enter this command to import the csv data to mysql tables.

    load data infile '/home/desktop/Documents/overseas.csv' into table trade_index fields terminated by ',' lines terminated by '\n' (series_reference,period,data_value,status,units,magnitude,subject,series_title1,series_title_2,series_title_3,series_title_4,series_title_5);

  5. Find this overseas trade index data on sqldatabase:

    select * from trade_index;
user_115267480

I know that my answer is late, but I'd like to mention a few other ways to do it. The easiest one is using command line. The steps will be the following:

  1. Accessing the MySQL CLI by entering the below command:

mysql -u my_user_name -p

  1. Creating a table in the database
use new_schema;

CREATE TABLE employee_details (
id INTEGER,
employee_name VARCHAR(100),
employee_age INTEGER,
PRIMARY KEY (id)
);
  1. Importing the CSV file into a table. We can either mention the file path or store the file in the default directory of the MySQL server.
LOAD DATA INFILE 'Path to the exported csv file'
INTO TABLE employee_details
FIELDS TERMINATED BY ','
IGNORE 1 ROWS;

It's the only one of many solutions, I found it in this tutorial If loading CSV files into MySQL database is your daily task, then it'll be better to automate this process. In this case you can use some 3rd-party tools that allows you to load data in schedule.

user_155712750

PHP Query for import csv file to mysql database

$query = <<<EOF
            LOAD DATA LOCAL INFILE '$file'
             INTO TABLE users
             FIELDS TERMINATED BY ','
             LINES TERMINATED BY '\n'
             IGNORE 1 LINES
            (name,mobile,email)
    EOF;
if (!$result = mysqli_query($this->db, $query))
   {
        exit(mysqli_error($this->db));
   }

**Sample CSV file data **

name,mobile,email
Christopher Gritton,570-686-3439,ChristopherKGritton@inbound.plus
Brandon Wilson,541-309-5149,BrandonMWilson@inbound.plus
Craig White,516-795-8065,CraigJWhite@inbound.plus
David Whitney,713-214-3966,DavidCWhitney@inbound.plus
user_39662090

I did it in simple way using phpmyadmin. I followed the steps by @Farhan but all data were eltered in single column. How I did:

  1. Created a CSV file and deleted the header row with column names. Kept only data.
  2. I created a table with column names matching the csv columns.
  3. Remember to assign appropriate types to each column.
  4. I just selected the import and went to import tab.
  5. In browse I selected the CSV file and kept all options as it is.
  6. To my surprise all the data got imported successfully in their appropriate columns.
user_17893930

When executing MySQL Query to import CSV I was getting error 'Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement'

So I moved file to secure file location

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/Orders.csv'   
INTO TABLE orderdetails.orders 
FIELDS TERMINATED BY ','  
 ENCLOSED BY '"'
 LINES TERMINATED BY '\n'
 IGNORE 1 ROWS

Where location of file is 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/Orders.csv' this is because, I moved my CSV file to 'secure_file_priv' location otherwise I was getting above error

You can get your secure_file_priv using query SHOW VARIABLES LIKE "secure_file_priv";

Source: Import CSV file to MySQL (Query or using Workbench)

user_35594620
Related