MySQL Bulk Load Command Line Tool

MySql bulk load command line tool

mysqlimport.

takes the same connection parameters as the mysql command line shell. Make sure to use the -L flag to use a file on the local file system, otherwise it will (strangely) assume the file is on the server.

There is also an analogous variant to the load data infile command, i.e., load data local infile, according to which the file will be loaded from the client rather than the server, which may accomplish what you want to do.

R bulk upload data to MYSQL database

If you are inserting many rows from the same client at the same time, You can use INSERT statements with multiple VALUES

INSERT INTO test2(col1, col2, col3, col4) 
VALUES 
('val1', 'val2', val3, val4),
('val1', 'val2', val3, val4),
('val1', 'val2', val3, val4)

Here an example on how to create your query. I am using data.table here:

dat <- matrix(seq(4*3), 3, 4)
library(data.table)
DT <- data.table(t(dat))
query <- paste('INSERT INTO test2(col1, col2, col3, col4)\nVALUES\n',
                gsub('c','',(DT[,paste(.SD,collapse='\n')])))

   cat(query)
INSERT INTO test2(col1, col2, col3, col4)
VALUES
 (1, 4, 7, 10)
(2, 5, 8, 11)
(3, 6, 9, 12)

then you can execute it using dbGetQuery:

  dbGetQuery(con, query)

Importing a csv into mysql via command line

Try this command

 load data local infile 'file.csv' into table table
 fields terminated by ','
 enclosed by '"'
 lines terminated by '\n'
 (column1, column2, column3,...)

The fields here are the actual table fields that the data needs to sit in. The enclosed by and lines terminated by are optional and can help if you have columns enclosed with double-quotes such as Excel exports, etc.

For further details check the manual.

For setting the first row as the table column names, just ignore the row from being read and add the values in the command.

MySQL bulk insert from CSV data files

You should read about mysqlimport, which is a command-line tool provided with MySQL. This tool is the fastest way to bulk-load CSV data.

The tool has two options, --replace and --ignore to handle duplicate key conflicts.

Regarding security and avoiding putting the password in plain text in the script, you can also use the MYSQL_PWD environment variable or the .my.cnf file (make sure that file is mode 400 or 600). See End-User Guidelines for Password Security.

Importing larger SQL files into MySQL

You can import large files this command line way:

mysql -h yourhostname -u username -p databasename < yoursqlfile.sql

---

Related Topics