-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
Michal ?iha? schrieb:
Hi
On Mon, 12 Jun 2006 09:59:20 +0200 Sebastian Mendel lists@sebastianmendel.de wrote:
in english: is it possible to automatically create a table from CSV file? in other words: auto detect field type by content?
IMHO it could be possible, however there could be several problems:
what length should be char fields? text or varchar?
detecting field type from whole file can take very long, so you need
to scan only few lines and then somehow handle error when your expectation fails later
just scan first few lines and insert line by line - if error occurs alter table to fit new line, or just before insert check field content length and alter table than insert also check content type for example if first 1000 lines only numbers and than comes some text you need to alter table field to text - of course this could take a long time with large cvs files
or just scan first few lines (let user decide how much lines) and than (optionally) recommend the table structure to the user and let him decide to change or use the recommendation - if we cannot save the file in a temp dir the user needs to upload the file twice
- --- import CVS ----------------------------------- | | File: ________ [...] - --------------------------------------------------
- --- [ ] Auto-Create new Table from CVS ----------- | | Scan lines: _____100 | [ ] use temporary table to store/analyze data | [ ] Review Table structure before create - --------------------------------------------------
We might simply choose to import everything to text fields and then let MySQL create 'optimal' structure by SELECT * FROM `table` PROCEDURE ANALYSE (). But it tends to create ENUMs for small set of values.
- -- Sebastian Mendel
www.sebastianmendel.de