News for hackers. Stuff that matters.
Re:Show HN: Convert CSV files into MySQL insert scripts by pi-rat on Thursday August 28, 2014 @07:34AM (#8236724)
Or just do something like:

  LOAD DATA INFILE 'data.csv' INTO TABLE mytable
    FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    LINES TERMINATED BY '\r\n'
    IGNORE 1 LINES;
Re:Show HN: Convert CSV files into MySQL insert scripts by akx on Thursday August 28, 2014 @07:50AM (#8236764)
If you're fortunate enough to be able to put `data.csv` on the MySQL server, that is.
Re:Show HN: Convert CSV files into MySQL insert scripts by masklinn on Thursday August 28, 2014 @07:56AM (#8236781)
Add LOCAL (as in, "LOAD DATA LOCAL INFILE") and the path is looked up in the client's filesystem, with the file uploaded through the connection before the command is run.
Re:Show HN: Convert CSV files into MySQL insert scripts by akx on Thursday August 28, 2014 @09:10AM (#8237007)
Cool. I swear that didn't exist the last time I looked at the docs for LOAD DATA INFILE...
Re:Show HN: Convert CSV files into MySQL insert scripts by jeremysmyth on Thursday August 28, 2014 @07:53AM (#8236774)
...or if you can use the LOAD DATA LOCAL INFILE syntax on that server/client.

http://dev.mysql.com/doc/refman/5.6/en/load-data-local.html

Re:Show HN: Convert CSV files into MySQL insert scripts by sarciszewski on Thursday August 28, 2014 @08:15AM (#8236820)
In my experience, this is actually not that difficult or novel, but kudos on trying new things. Keep it up :)
Re:Show HN: Convert CSV files into MySQL insert scripts by lifeisstillgood on Thursday August 28, 2014 @08:26AM (#8236855)
Absolutely. I did come here to do the "so what" dance but this is the simplest and most respectful comment here.

Keep on making new (to you) things, keep a good log of what you have done (GitHub) - eventually you will make new (to anyone) things.

Re:Show HN: Convert CSV files into MySQL insert scripts by clxl on Thursday August 28, 2014 @09:54AM (#8237181)
I'm surprised by how many things that aren't that difficult or novel end up on the front page. CSV related projects come to the fore every week.
Re:Show HN: Convert CSV files into MySQL insert scripts by sarciszewski on Thursday August 28, 2014 @09:58AM (#8237194)
Heh, maybe I should start submitting some of my more trivial projects and rack up the HN karma ;)

i.e. "A testing utility for database migrations"

Re:Show HN: Convert CSV files into MySQL insert scripts by mathnode on Thursday August 28, 2014 @08:27AM (#8236863)
I am not putting 1TB+ per table dumps of data into a strangers website what can be done with a bit of sed or a script in ${LANGUAGE}.

Loader from Several Nines, is a tool for doing parallel loads of CSV into MySQL and MariaDB https://github.com/severalnines/loader

Mydumper is a threaded dump and load tool for MySQL/MariaDB. https://launchpad.net/mydumper

Tungsten Replicate can do batch load replication into any external datasource http://docs.continuent.com/tungsten-replicator-3.0/deploymen... So long as you have a script for it: https://code.google.com/p/tungsten-replicator/source/browse/...

EDIT: A fun extra, https://mariadb.com/kb/en/mariadb/documentation/storage-engi...

Re:Show HN: Convert CSV files into MySQL insert scripts by digital-rubber on Thursday August 28, 2014 @09:10AM (#8237008)
think it would be actually funny if you did, bogus data that is of course.

But uploading your files to some site that reformats plain text, this does not belong on hacker news imho.

Re:Show HN: Convert CSV files into MySQL insert scripts by phpnode on Thursday August 28, 2014 @07:09AM (#8236664)
MySQL already supports loading CSVs directly, why convert them first? http://dev.mysql.com/doc/refman/5.1/en/load-data.html
Re:Show HN: Convert CSV files into MySQL insert scripts by jeremysmyth on Thursday August 28, 2014 @07:12AM (#8236672)
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

Re:Show HN: Convert CSV files into MySQL insert scripts by blowski on Thursday August 28, 2014 @07:20AM (#8236684)
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.

Re:Show HN: Convert CSV files into MySQL insert scripts by jeremysmyth on Thursday August 28, 2014 @07:41AM (#8236735)
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);
Re:Show HN: Convert CSV files into MySQL insert scripts by michaelmior on Thursday August 28, 2014 @08:17AM (#8236825)
Cool! I wasn't aware you could do that sort of thing with LOAD DATA INFILE. Thanks :)
Re:Show HN: Convert CSV files into MySQL insert scripts by blowski on Thursday August 28, 2014 @11:25AM (#8237652)
I didn't know that either - thanks for sharing.
Re:Show HN: Convert CSV files into MySQL insert scripts by eli on Thursday August 28, 2014 @07:49AM (#8236763)
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.
Re:Show HN: Convert CSV files into MySQL insert scripts by corobo on Thursday August 28, 2014 @07:17AM (#8236676)
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
Re:Show HN: Convert CSV files into MySQL insert scripts by agildehaus on Thursday August 28, 2014 @07:24AM (#8236694)
This submits to your server. If I have sensitive data, I sure as hell don't want that going somewhere where it might be stored.

Why isn't this just done client-side in Javascript?

Re:Show HN: Convert CSV files into MySQL insert scripts by easytiger on Thursday August 28, 2014 @08:23AM (#8236845)
why isn't this a trivial bash script more like it.
Re:Show HN: Convert CSV files into MySQL insert scripts by agildehaus on Thursday August 28, 2014 @02:00PM (#8238722)
Indeed, but that's already been done.
Re:Show HN: Convert CSV files into MySQL insert scripts by modoc on Thursday August 28, 2014 @07:43AM (#8236745)
Because the author prefers to code in something else?
Re:Show HN: Convert CSV files into MySQL insert scripts by jbrooksuk on Thursday August 28, 2014 @07:27AM (#8236705)
Probably because it'd be awfully slow.
Re:Show HN: Convert CSV files into MySQL insert scripts by phpnode on Thursday August 28, 2014 @07:32AM (#8236718)
that's unlikely to be the case, JS is pretty fast
Re:Show HN: Convert CSV files into MySQL insert scripts by jbrooksuk on Thursday August 28, 2014 @11:43AM (#8237757)
Yeah, but what if you had a few hundred MB CSV file?
Re:Show HN: Convert CSV files into MySQL insert scripts by phpnode on Thursday August 28, 2014 @01:30PM (#8238535)
it would probably still be fast enough, the difference between JS perf and native perf is surprisingly slim.
Re:Show HN: Convert CSV files into MySQL insert scripts by eli on Thursday August 28, 2014 @07:47AM (#8236752)
The first python script I ever wrote does csv to SQL: https://github.com/elidickinson/csv-tools/blob/master/csv2sq... It's very rough, but I actually still use it on occasion. The Python csv library is quite good. There's a lot that can go wrong that LOAD DATA can't handle.
Re:Show HN: Convert CSV files into MySQL insert scripts by NicoJuicy on Thursday August 28, 2014 @08:43AM (#8236916)
I actually have some code (.Net) that loads xml, csv, ... into objects.

There is also a visual drawing to help you connect the dots between the properties of the uploaded file vs. the properties of the objects.

This way, all information gets loaded into the db from a single import, in the correct table.

I could seperate it in a opensource project i suppose, if anyone is interested.

Re:Show HN: Convert CSV files into MySQL insert scripts by kstenson on Thursday August 28, 2014 @08:46AM (#8236919)
I would be interested in looking at that if you opensource it.
Re:Show HN: Convert CSV files into MySQL insert scripts by NicoJuicy on Thursday August 28, 2014 @09:53AM (#8237178)
You're a .Net developer then?
Re:Show HN: Convert CSV files into MySQL insert scripts by itazula on Thursday August 28, 2014 @07:53AM (#8236773)
I submitted the following file (note the blanks): $ cat testblanks.csv 1997, Ford, E350

The following was returned: CREATE TABLE testblanks (Column_1 NUMERIC(32, 16),Column_2 VARCHAR(5) CHARACTER SET utf8,Column_3 VARCHAR(5) CHARACTER SET utf8); INSERT INTO testblanks VALUES (1997,' Ford',' E350');

That was in accordance with http://tools.ietf.org/html/rfc4180

Cool!

But the CSV format has a lot of "gotchas." See, for example, http://discuss.fogcreek.com/joelonsoftware3/default.asp?cmd=.... Especially the rant about the Microsoft version of CSV.

Re:Show HN: Convert CSV files into MySQL insert scripts by masklinn on Thursday August 28, 2014 @07:59AM (#8236789)
> But the CSV format has a lot of "gotchas."

In the wild, CSV is less a format and more a meta-format (and a subset of the *SV family) with plenty of variability between dialects.

Re:Show HN: Convert CSV files into MySQL insert scripts by vijayr on Thursday August 28, 2014 @09:13AM (#8237018)
Nice idea.

Can I suggest another idea? Take JSON files, and convert them into MySQL, postgres etc insert/update scripts. There is a need for this. For example, I run this hobby site http://kivatools.com that imports tons of data from kiva.org - their data dumps are in json/xml format. so I've a script that refreshes the db every night, with a couple of gigs of data. It would be very useful if there was a service that can take a url (or file), and convert json/xml into mysql scripts.

Re:Show HN: Convert CSV files into MySQL insert scripts by Gonzih on Thursday August 28, 2014 @07:28AM (#8236712)
I think it can be done in few lines of ruby/python/whatever code. Without submitting sensitive data to remote unknown server. IMHO it would be much better idea to release code as opensource.
Re:Show HN: Convert CSV files into MySQL insert scripts by sheetjs on Thursday August 28, 2014 @09:42AM (#8237123)
You should be able to do this entire thing in-browser. Going further, leveraging WebSQL or SQL.js, it should be possible to pump the queries into a client-side database and run queries.

Shameless plug: I built an in-browser tool to generate SQL queries from Excel XLS/XLSX/XLSB files: http://sheetjs.com/sexql/

Discussion: https://news.ycombinator.com/item?id=7392665

Re:Show HN: Convert CSV files into MySQL insert scripts by onion2k on Thursday August 28, 2014 @07:19AM (#8236680)
Tried it with a file containing this:

    Field1,Field2,Field3,Int,Float,Quoted
    This,is,a,12,1.2,"""file"""
...and it returned an empty textarea.
Re:Show HN: Convert CSV files into MySQL insert scripts by quasiben on Thursday August 28, 2014 @08:24AM (#8236850)
This is great! People should have better and faster ways of handling CSV -> DB interactions. We recently added native CSV loading into blaze for MySQL, PostgreSQL, and SQLITE, https://github.com/ContinuumIO/blaze/blob/master/blaze/data/...

MongoDB is coming soon.

Re:Show HN: Convert CSV files into MySQL insert scripts by gregcsv on Thursday August 28, 2014 @09:51AM (#8237167)
Here's one I made a few years ago:

http://utilitymill.com/utility/csv_to_create_table/

You just paste the content from excel or a csv.

It actually tries to figure out the data types and creates a table for you as well.

Re:Show HN: Convert CSV files into MySQL insert scripts by stef25 on Thursday August 28, 2014 @09:52AM (#8237172)
Tested it out with GeoIPCountryWhois.csv from Maxmind, seems to work ok. However selecting the output is almost impossible, locks up Chrome. This isn't your apps fault of course. Usually I use Sequel Pro for this, which allows me to map csv fields to db columns
Re:Show HN: Convert CSV files into MySQL insert scripts by abluecloud on Thursday August 28, 2014 @07:10AM (#8236667)
I've found phpMyAdmin's CSV insert feature has been very useful.

Allows for quite a few options.

Re:Show HN: Convert CSV files into MySQL insert scripts by pessimizer on Thursday August 28, 2014 @07:38AM (#8236729)
Re:Show HN: Convert CSV files into MySQL insert scripts by squidmccactus on Thursday August 28, 2014 @08:35AM (#8236885)
So does this work with CSV?
Re:Show HN: Convert CSV files into MySQL insert scripts by digital-rubber on Thursday August 28, 2014 @09:06AM (#8236997)
What is this for ridiculous product/ attempt?

HACKER news, not noobs-that-cant-do-shit.

Re:Show HN: Convert CSV files into MySQL insert scripts by digital-rubber on Thursday August 28, 2014 @10:14AM (#8237270)
Quite amusing, people down voting my 'minimal worded critique', apparently really like this tool and see a benefit that is non-existing. Apparently can't think beyond a solution to a problem.

Reformatting of ascii/text data, a csv, is not rocket science. Nor 'hacker (news) quality'.

And i hope that nobody afterwards realises that they should not have uploaded their csv file to your website hosted by nsa friendly google. Also adding a warning that people are submitting their data to (google, 3rd party servers you don't control) servers, would be nicer, and makes people perhaps aware that their data goes further then only their own browser.

Re:Show HN: Convert CSV files into MySQL insert scripts by easytiger on Friday August 29, 2014 @07:41AM (#8242071)
you were not elegant or polite, but are in fact correct