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 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 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 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 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');
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/
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 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.
http://dev.mysql.com/doc/refman/5.6/en/load-data-local.html
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.
i.e. "A testing utility for database migrations"
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...
But uploading your files to some site that reformats plain text, this does not belong on hacker news imho.
http://dev.mysql.com/doc/mysql/en/csv-storage-engine.html
That said, I can't see how the OP actually helps me with that.
Why isn't this just done client-side in Javascript?
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.
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.
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.
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.
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
MongoDB is coming soon.
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.
Allows for quite a few options.
HACKER news, not noobs-that-cant-do-shit.
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.