3 ms·
There are occasions where you can't just do a straight insert. Perhaps you need to conditionally create a foreign record, or you want to format a value on inser
by blowski 12y ago
There are occasions where you can't just do a straight insert. Perhaps you need to conditionally create a foreign record, or you want to format a value on insert instead of doing it directly in the CSV.
That said, I can't see how the OP actually helps me with that.
- jeremysmyth 12y agoLOAD DATA INFILE can't conditionally create a foreign record, but you can certainly format a value on insert by using the SET clause, for example: LOAD DATA INFILE 'people.txt' INTO TABLE Person (@first, @last, date_of_birth) SET full_name = CONCAT(LEFT(@first, 1), '. ', @last); You can even do funky stuff like lookups: LOAD DATA INFILE 'projects.txt' INTO TABLE Projects(@first, @last, project) SET person_id = (SELECT id FROM people WHERE name = CONCAT(@first, ' ', @last) LIMIT 1);
- michaelmior 12y agoCool! I wasn't aware you could do that sort of thing with LOAD DATA INFILE. Thanks :)
- blowski 12y agoI didn't know that either - thanks for sharing.
- eli 12y agoMuch more frequently I run into the issue that my csv isn't perfectly formed. Some people have funny ideas about how to handle newlines or escape quotes/commas.