Create a UNIQUE index on the age column, then: LOAD DATA INFILE ‘member.csv’ IGNORE INTO TABLE tbl_member FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ ESCAPED BY ‘”‘ LINES TERMINATED BY ‘\n’ (name, age);
if you’re using innodb and bulk loading here are a few tips: sort your csv file into the primary key order of the target table : remember innodb uses clustered primary keys so it will load faster if it’s sorted ! typical load data infile i use: truncate <table>; set autocommit = 0; load data … Read more
Just went through this myself, well I still am… Case sensitive text Lack of INSERT IGNORE and REPLACE Explicit casting needed almost everywhere No backticks LOAD DATA INFILE (COPY is close, but not close enough) Change autoincrement to SERIAL Although bad form in MySQL, in Postgres, an INNER JOIN without an ON clause can’t happen, … Read more
To solve this problem, I looked up this question, found @pumpkinthehead’s answer, and realized that all we need to do is find+replace the primary key in each row with the NULL so that mysql will use the default auto_increment value instead. (your complete mysqldump command) | sed -e “s/([0-9]*,/(NULL,/gi” > my_dump_with_no_primary_keys.sql Original output: INSERT INTO … Read more
These steps can be used to emulate this functionality: Create a new temporary table. CREATE TEMPORARY TABLE temporary_table LIKE target_table; Optionally, drop all indices from the temporary table to speed things up. SHOW INDEX FROM temporary_table; DROP INDEX `PRIMARY` ON temporary_table; DROP INDEX `some_other_index` ON temporary_table; Load the CSV into the temporary table LOAD DATA … Read more
From Mysql docs: You can also discard an input value by assigning it to a user variable and not assigning the variable to a table column: LOAD DATA INFILE ‘file.txt’ INTO TABLE t1 (column1, @dummy, column2, @dummy, column3);
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 … Read more
LOAD DATA INFILE ‘/tmp/test.txt’ INTO TABLE test IGNORE 1 LINES; (reference)
try to use LOAD DATA LOCAL INFILE instead of LOAD DATA INFILE otherwise check if apparmor is active for your directory
Instead of writing a script to pull in information from a CSV file, you can link MYSQL directly to it and upload the information using the following SQL syntax. To import an Excel file into MySQL, first export it as a CSV file. Remove the CSV headers from the generated CSV file along with empty … Read more