Automatically create MySQL table from input/file

Question

Possible Duplicate:
Generate table schema inspecting Excel(CSV) and import data

I'm working on a tool to automatically create a MySQL table based on input.

For example.

input

Y|abc|123|1.12|2012-12-20 00:00:00

output

CREATE TABLE MyTable (
 is_enabed char(1),
 call_sign varchar(8),
 facility_id tinyint(4),
 bounds decimal(4,2)
 created_time datetime
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1

I have a quasi working prototype right now in Python. Before I delve deeper I would like to see if there are similar projects (free, open-source) already in existence. If so, my efforts would probably be better spent researching that project rather than tirelessly re-inventing the wheel.

I removed the tag [tag:data-mining], because there is no data mining involved in this question. You might want to search for "SQL schema inference", although I believe this is mostly done from XML data that have much more complex schemas. Also look at `numpy.genfromtext` which does type inference from CSV data. — Has QUIT--Anony-Mousse, Oct 21 '12 at 08:08

score 1 · Answer 1 · answered Oct 20 '12 at 21:31

1

I don't think so. At least I've never heard about a tool to do that. Having said that, I don't know if this is a good idea. I understand that it's oriented to users with little mysql (sql) knowledge but they could have more problems using your tool that reading a good tutorial. For example, what about if they need a timestamp instead a datetime. Or maybe they need decimals with more precission. Moreover, maybe they need case sensitive collates...

Anyway, sounds like a nice project and I'm sure that this is only an example and there is more behind it. I hope to read about your tool in a near future. Good luck.

answered Oct 20 '12 at 21:31

David Moreno García

4,423
8
49
82

1

Opps, I accidently hit enter and went past my 5 minute edit window. So anyhow, yes, this is the tip of the iceberg. The tool would be geared towards data mining users. Often times I have my 3-5 easily parseable raw data sets (csv, json, xml, pipe/tab separated) but it ends up taking more time to create the tables manually or through phpmyadmin than actually writing the parser. It's somewhat of a trial and error process. I could use this extra time on ... well, analysis. :) – Justin Oct 20 '12 at 21:46
Sound really interesting. I'm actually working in a datamining platform so I understand you perfectly ^^ – David Moreno García Oct 20 '12 at 21:58

score 1 · Accepted Answer · edited May 23 '17 at 10:24

Generate table schema inspecting Excel(CSV) and import data

This question (and the top reply, mentioning xlrd) already discuss this subject.

And in fact, MySQL apparently has such a function built-in:

https://dev.mysql.com/doc/refman/5.0/en/procedure-analyse.html

ANALYSE() examines the result from a query and returns an analysis of the results that suggests optimal data types for each column that may help reduce table sizes.

Automatically create MySQL table from input/file

2 Answers2