Hacker News Reader

Comment by phpnode | Hacker News Reader

MySQL already supports loading CSVs directly, why convert them first? http://dev.mysql.com/doc/refman/5.1/en/load-data.html

View on HN

It also has the CSV storage engine which actually uses a CSV file as its data back-end, no conversion required.

http://dev.mysql.com/doc/mysql/en/csv-storage-engine.html

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.

LOAD 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);

Cool! I wasn't aware you could do that sort of thing with LOAD DATA INFILE. Thanks :)

I didn't know that either - thanks for sharing.

Much 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.

Yeah.. I'd be interested in something that does the opposite of this tool actually. LOAD DATA INFILE is a lot faster than SQL query files for heavier data

Reply on news.ycombinator.com