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
Cannot Select from Update Returning Clause in Postgres
SQL Query with Distinct and Sum
Entity Framework Skip/Take Is Very Slow When Number to Skip Is Big
Referencing Current Row in Filter Clause of Window Function
Adding 'Go' Statements to Entity Framework Migrations
Connecting to Oracle Database Using SQL Server Integration Services
Insert Data and Set Foreign Keys with Postgres
Linq to SQL: How to Stop the Auto Generated Object Name from Being Renamed
Sql: Aggregating Strings Together
Subtract Hours from the Now() Function
Capturing Multiple Error Messages from a Single Statement Inside Try Catch
Oracle: Function Based Index Selective Uniqueness
Select Closest Numerical Value with MySQL Query
Store and Reuse Value Returned by Insert ... Returning
Postgresql Selecting Most Recent Entry for a Given Id
Role of Selectivity in Index Scan/Seek
Concat Function Is Not Working - Invalid Number of Arguments