MySQL Equivalent of Oracle's SQL loader

John D K -

In this post:

  • MySQL SQL loader equivalent - LOAD DATA INFILE
  • MySQL SQL load csv file
  • MySQL load by tools - HeidiSQL
  • More info and examples

Here you can watch video of this tutorial: mysql equivalent of oracle's sql loader

MySQL SQL loader equivalent

SQL loader is pretty good tool for loading data in Oracle. It's fast, reliable and easy to use. MySQL has similar functionality which allows you to load variety of file types in the DB.

MySQL SQL loader equivalent - LOAD DATA INFILE

If the data file and the table columns are mapped already then you can use simply:

LOAD DATA INFILE 'C:\\Users\\user\\Desktop\\file.txt' INTO TABLE mytable FIELDS TERMINATED BY '|' ;

If you need to customize the load you can use the following syntax. It's pretty close to SQL*Loader and provides the same freedom of chosing:

  • separator
  • columns
  • ignore lines
  • enclosed by
LOAD DATA LOCAL INFILE 'file.csv' INTO TABLE mytable
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
       (col1, col2, col3, col4, col5...);

MySQL SQL load csv file

If you want to load an csv file you can try to use the script below:

LOAD DATA LOCAL INFILE "/home/Desktop/a.csv"
INTO TABLE aboys 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS

Let say that you have this csv file: a.csv

id,name,relation
1,Brady,5
2,Mike,1
3,Alex,3
4,Daniel,\N
5,James,\N

If you notice there is an addination parameter - LOCAL. This one is to prevent the error:

/* SQL Error (1290): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement */

You can prevent it also by:

  1. putting the file in the folder returned by this query:
SHOW VARIABLES LIKE "secure_file_priv";
  1. set secure-file-priv to false
    2.1 For MySQL 5.7:
  • find file my.cnf
  • add secure-file-priv = "" (just below the mysqld config group)

[mysqld]
secure-file-priv = ""

MySQL load by tools

You can use free tools like: HeidiSQL or DBeaver for importing text, csv and many other formats.

This is how to do it with HeidiSQL (which has a portable version):

  1. Open HeidiSQL
  2. Connect to your DB
  3. Tools
  4. Import CSV file...
  5. Select options
  • select econding
  • field separator
  • ignore lines - for examples column names
  • select columns

More info and examples

MySQL LOAD DATA INFILE Syntax