Stack Overflow
Stack Overflow

Questions tagged [load-data-infile]

A mySQL function for loading external data directly into the database.

LOAD DATA INFILE is a mySQL function for loading external data directly into the database.

More information available from the mySQL manual: http://dev.mysql.com/doc/refman/5.1/en/load-data.html

605 questions
326
votes
19 answers

How do I import CSV file into a MySQL table?

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…
Iain Samuel McLean Elder
  • 16,665
  • 10
  • 59
  • 76
173
votes
7 answers

MySQL load NULL values from CSV data

I have a file that can contain from 3 to 4 columns of numerical values which are separated by comma. Empty fields are defined with the exception when they are at the end of the row: 1,2,3,4,5 1,2,3,,5 1,2,3 The following table was created in…
Spiros
  • 2,046
  • 2
  • 14
  • 25
117
votes
11 answers

MYSQL import data from csv using LOAD DATA INFILE

I am importing some data of 20000 rows from a CSV file into Mysql. Columns in the CSV are in a different order than MySQL table's columns. How to automatically assign columns corresponding to Mysql table columns? When I execute LOAD DATA…
MANJEET
  • 1,533
  • 2
  • 11
  • 20
113
votes
11 answers

access denied for load data infile in MySQL

I use MySQL queries all the time in PHP, but when I try LOAD DATA INFILE I get the following error #1045 - Access denied for user 'user'@'localhost' (using password: YES) Does anyone know what this means?
Brian
  • 24,052
  • 51
  • 126
  • 164
96
votes
13 answers

Import CSV to mysql table

What is the best/fastest way to upload a csv file into a mysql table? I would like for the first row of data be used as the column names. Found this: How to import CSV file to MySQL table But the only answer was to use a GUI and not shell?
lcm
  • 1,627
  • 5
  • 16
  • 35
69
votes
5 answers

mysql load data infile can't get stat of file Errcode: 2

I have looked all over and found no solution, any help on this would be great. Query: LOAD DATA INFILE '/Users/name/Desktop/loadIntoDb/loadIntoDB.csv' INTO TABLE `tba`.`tbl_name` FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE 1…
Phill Pafford
  • 77,927
  • 86
  • 256
  • 378
62
votes
4 answers

How to skip columns in CSV file when importing into MySQL table using LOAD DATA INFILE?

I've got a CSV file with 11 columns and I have a MySQL table with 9 columns. The CSV file looks like: col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11 and the MySQL table looks like: col1, col2, col3, col4, col5, col6, col7,…
Camsoft
  • 10,713
  • 18
  • 78
  • 119
61
votes
3 answers

LOAD DATA LOCAL, How do I skip the first line?

I'm trying to Load a CSV file into my MySQL database, But I would like to skip the first line. I fact It contains the name of my columns and no interesting data. Here is the query I'm using: LOAD DATA LOCAL INFILE '/myfile.csv' INTO TABLE…
Roch
  • 20,993
  • 27
  • 74
  • 118
59
votes
9 answers

Importing a csv into mysql via command line

I'm trying to import a very large .csv file (~4gb) into mysql. I was considering using phpmyadmin, but then you have a max upload size of 2mb. Someone told me that I have to use the command line. I was going to use these directions to import it:…
de1337ed
  • 2,545
  • 9
  • 29
  • 48
50
votes
2 answers

MySQL LOAD DATA INFILE with ON DUPLICATE KEY UPDATE

For loading huge amounts of data into MySQL, LOAD DATA INFILE is by far the fastest option. Unfortunately, while this can be used in a way INSERT IGNORE or REPLACE works, ON DUPLICATE KEY UPDATE is not currently supported. However, ON DUPLICATE KEY…
Jan
  • 2,794
  • 2
  • 18
  • 32
44
votes
10 answers

mysqldump table without dumping the primary key

I have one table spread across two servers running MySql 4. I need to merge these into one server for our test environment. These tables literally have millions of records each, and the reason they are on two servers is because of how huge they are.…
Zee Spencer
  • 3,328
  • 6
  • 27
  • 31
35
votes
6 answers

Switching from MySQL to PostgreSQL - tips, tricks and gotchas?

I am contemplating a switch from MySQL to PostgreSQL. What are your tips, tricks and gotchas for working with PostgreSQL? What should a MySQLer look out for? See also: How different is PostgreSQL to MySQL? See also: Migrate from MySQL to…
Toby Hede
  • 35,582
  • 27
  • 127
  • 161
32
votes
3 answers

MySQL load data infile - acceleration?

sometimes, I have to re-import data for a project, thus reading about 3.6 million rows into a MySQL table (currently InnoDB, but I am actually not really limited to this engine). "Load data infile..." has proved to be the fastest solution, however…
DBa
  • 1,183
  • 3
  • 10
  • 16
27
votes
9 answers

Easy way to export a SQL table without access to the server or phpMyADMIN

I need a way to easily export and then import data in a MySQL table from a remote server to my home server. I don't have direct access to the server, and no utilities such as phpMyAdmin are installed. I do, however, have the ability to put PHP…
Jrgns
  • 22,631
  • 17
  • 67
  • 75
26
votes
3 answers

MYSQL LOAD DATA INFILE ignore duplicate rows (autoincrement as primary key)

I ran into some trouble using LOAD DATA INFILE command as i wanted to ignore the lines that was already in the data base..say if i have a table with data as follows, id |name |age -------------------- 1 |aaaa |22 2 |bbbb |21 3 |bbaa …
Hasitha Shan
  • 2,541
  • 6
  • 37
  • 78
1
2 3
…
40 41