Show HN: Convert CSV files into MySQL insert scripts

41 点作者 d4nt超过 10 年前

19 条评论

pi-rat超过 10 年前

Or just do something like:<pre><code> LOAD DATA INFILE 'data.csv' INTO TABLE mytable FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;</code></pre>

评论 #8236764 未加载

sarciszewski超过 10 年前

In my experience, this is actually not that difficult or novel, but kudos on trying new things. Keep it up :)

评论 #8236855 未加载

评论 #8237181 未加载

mathnode超过 10 年前

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 <a href="https://github.com/severalnines/loader" rel="nofollow">https://github.com/severalnines/loader</a>Mydumper is a threaded dump and load tool for MySQL/MariaDB. <a href="https://launchpad.net/mydumper" rel="nofollow">https://launchpad.net/mydumper</a>Tungsten Replicate can do batch load replication into any external datasource <a href="http://docs.continuent.com/tungsten-replicator-3.0/deployment-batchloading.html" rel="nofollow">http://docs.continuent.com/tungsten-replicator-3.0/deploymen...</a> So long as you have a script for it: <a href="https://code.google.com/p/tungsten-replicator/source/browse/branches/replicator-3.0.0/replicator/samples/scripts/batch/mysql.js" rel="nofollow">https://code.google.com/p/tungsten-replicator/source/browse/...</a>EDIT: A fun extra, <a href="https://mariadb.com/kb/en/mariadb/documentation/storage-engines/connect/connect-table-types/connect-table-types-data-files/#csv-and-fmt-table-types" rel="nofollow">https://mariadb.com/kb/en/mariadb/documentation/storage-engi...</a>

评论 #8237008 未加载

phpnode超过 10 年前

MySQL already supports loading CSVs directly, why convert them first? <a href="http://dev.mysql.com/doc/refman/5.1/en/load-data.html" rel="nofollow">http://dev.mysql.com/doc/refman/5.1/en/load-data.html</a>

评论 #8236672 未加载

评论 #8236684 未加载

评论 #8236676 未加载

agildehaus超过 10 年前

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?

评论 #8237105 未加载

评论 #8236845 未加载

评论 #8236745 未加载

评论 #8236705 未加载

eli超过 10 年前

The first python script I ever wrote does csv to SQL: <a href="https://github.com/elidickinson/csv-tools/blob/master/csv2sql.py" rel="nofollow">https://github.com/elidickinson/csv-tools/blob/master/csv2sq...</a> 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.

NicoJuicy超过 10 年前

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.

评论 #8236919 未加载

itazula超过 10 年前

I submitted the following file (note the blanks): $ cat testblanks.csv 1997, Ford, E350The 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 <a href="http://tools.ietf.org/html/rfc4180" rel="nofollow">http://tools.ietf.org/html/rfc4180</a>Cool!But the CSV format has a lot of "gotchas." See, for example, <a href="http://discuss.fogcreek.com/joelonsoftware3/default.asp?cmd=show&ixPost=99824&ixReplies=25" rel="nofollow">http://discuss.fogcreek.com/joelonsoftware3/default.asp?cmd=...</a>. Especially the rant about the Microsoft version of CSV.

评论 #8236789 未加载

vijayr超过 10 年前

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 <a href="http://kivatools.com" rel="nofollow">http://kivatools.com</a> 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.

Gonzih超过 10 年前

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.

sheetjs超过 10 年前

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: <a href="http://sheetjs.com/sexql/" rel="nofollow">http://sheetjs.com/sexql/</a>Discussion: <a href="https://news.ycombinator.com/item?id=7392665" rel="nofollow">https://news.ycombinator.com/item?id=7392665</a>

onion2k超过 10 年前

Tried it with a file containing this:<pre><code> Field1,Field2,Field3,Int,Float,Quoted This,is,a,12,1.2,"""file""" </code></pre> ...and it returned an empty textarea.

quasiben超过 10 年前

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, <a href="https://github.com/ContinuumIO/blaze/blob/master/blaze/data/sql.py#L283" rel="nofollow">https://github.com/ContinuumIO/blaze/blob/master/blaze/data/...</a>MongoDB is coming soon.

gregcsv超过 10 年前

Here's one I made a few years ago:<a href="http://utilitymill.com/utility/csv_to_create_table/" rel="nofollow">http://utilitymill.com/utility/csv_to_create_table/</a>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.

stef25超过 10 年前

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

abluecloud超过 10 年前

I've found phpMyAdmin's CSV insert feature has been very useful.Allows for quite a few options.

pessimizer超过 10 年前

<a href="http://neilb.bitbucket.org/csvfix/manual/csvfix16/sql_insert.html" rel="nofollow">http://neilb.bitbucket.org/csvfix/manual/csvfix16/sql_insert...</a>

squidmccactus超过 10 年前

So does this work with CSV?

digital-rubber超过 10 年前

What is this for ridiculous product/ attempt?HACKER news, not noobs-that-cant-do-shit.

评论 #8237270 未加载