[Fwd: csv -> mysql]

[Sebastian Mendel]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

> -------- Original-Nachricht --------
> Betreff: csv -> mysql
> Datum: Mon, 12 Jun 2006 09:00:32 +0200
> Von: Michael Borchers <list@...>
> Organisation: Tridem Internet Services GmbH
> An: mysql <mysql-de@...>
>
> gibt es ne mögl. zb via phpmyadmin eine csv datei so zu importieren,
> dass automatisch auch die struktur mit übernommen wird?
>
> bei 43 feldern spar ich mir die arbeit lieber:)

in english: is it possible to automatically create a table from CSV
file? in other words: auto detect field type by content?



- --
Sebastian Mendel

www.sebastianmendel.de
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.3 (MingW32)

iD8DBQFEjR7YX/0lClpZDr4RAtOyAJ9xXdJDR5dgKaqQIaufYP9GNBn1twCgtqTf
2C6iHDMqqw5VMSP91arnCgk=
=1/rS
-----END PGP SIGNATURE-----


_______________________________________________
Phpmyadmin-devel mailing list
Phpmyadmin-devel@...
https://lists.sourceforge.net/lists/listinfo/phpmyadmin-devel

[Michal Čihař]

Hi

On Mon, 12 Jun 2006 09:59:20 +0200
Sebastian Mendel <lists@...> 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

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.

--
        Michal Čihař | http://cihar.com | http://blog.cihar.com



_______________________________________________
Phpmyadmin-devel mailing list
Phpmyadmin-devel@...
https://lists.sourceforge.net/lists/listinfo/phpmyadmin-devel

[Sebastian Mendel]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Michal ?iha? schrieb:

> Hi
>
> On Mon, 12 Jun 2006 09:59:20 +0200
> Sebastian Mendel <lists@...> 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
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.3 (MingW32)

iD8DBQFEjTNlX/0lClpZDr4RAuKWAKCea+Ivl/jeykLwB3hvq6YiiNkHrwCgomeM
wa5kSuTO9BpEgU7wBx377hw=
=Ze8z
-----END PGP SIGNATURE-----


_______________________________________________
Phpmyadmin-devel mailing list
Phpmyadmin-devel@...
https://lists.sourceforge.net/lists/listinfo/phpmyadmin-devel

[Garvin Hicking-2]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi!

> 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

This idea sounds quite good, IMHO. It's a bit of work when user interaction is
possible there, but IMHO it could be a really useful feature for some people.
Great idea. :-)

Best regards,
Garvin


- --
++ Garvin Hicking | Web-Entwickler [PHP]    | www.garv.in | ICQ 21392242
++ Developer of   | www.phpMyAdmin.net      | www.s9y.org

++ Make me happy  | http://wishes.garv.in

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFEjTV5UZolOPYrUhYRArOeAJ9HHrZ+s/vpPT25fH2VbImRUIk+pACghFYE
hZVbifEBwZS2xzNqTJ0jGcw=
=9pVR
-----END PGP SIGNATURE-----



_______________________________________________
Phpmyadmin-devel mailing list
Phpmyadmin-devel@...
https://lists.sourceforge.net/lists/listinfo/phpmyadmin-devel

[Sebastian Mendel]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Garvin Hicking schrieb:

> Hi!
>
>> 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
>
> This idea sounds quite good, IMHO. It's a bit of work when user interaction is
> possible there, but IMHO it could be a really useful feature for some people.
> Great idea. :-)

first line could also be treated optionally as field names - like
excel?/access? it does - whether commented out or not

//'fiedl1'; 'field2';
01; 'text';

or

'fiedl1'; 'field2';
01; 'text';


- --
Sebastian Mendel

www.sebastianmendel.de
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.3 (MingW32)

iD8DBQFEjTgJX/0lClpZDr4RAjRfAJ4sHORCkZ37RChDH+LorPikOLVSrgCgoaLK
7ySqu8MYlwBJ4Yat8pS42kY=
=R3vY
-----END PGP SIGNATURE-----


_______________________________________________
Phpmyadmin-devel mailing list
Phpmyadmin-devel@...
https://lists.sourceforge.net/lists/listinfo/phpmyadmin-devel

[Arnór Heiðar]

Sebastian Mendel wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Garvin Hicking schrieb:
  

Hi!

    

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
      

This idea sounds quite good, IMHO. It's a bit of work when user interaction is
possible there, but IMHO it could be a really useful feature for some people.
Great idea. :-)
    

first line could also be treated optionally as field names - like
excel?/access? it does - whether commented out or not

//'fiedl1'; 'field2';
01; 'text';

or

'fiedl1'; 'field2';
01; 'text';

  

Yes, and make it create appropriate column types detected by the data in the columns :) (as far as that goes)

best regs, Arnor


_______________________________________________
Phpmyadmin-devel mailing list
Phpmyadmin-devel@...
https://lists.sourceforge.net/lists/listinfo/phpmyadmin-devel

[Marc Delisle]

Michal Čihař a écrit :

> Hi
>
> On Mon, 12 Jun 2006 09:59:20 +0200
> Sebastian Mendel <lists@...> 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?

This reminds me: in MySQL 5.0.3+, a VARCHAR can be 65532 bytes! So for
this feature we probably could use VARCHAR instead of TEXT if the MySQL
version is recent enough.

Marc

>
> - 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
>
> 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.
>
>
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> Phpmyadmin-devel mailing list
> Phpmyadmin-devel@...
> https://lists.sourceforge.net/lists/listinfo/phpmyadmin-devel

_______________________________________________
Phpmyadmin-devel mailing list
Phpmyadmin-devel@...
https://lists.sourceforge.net/lists/listinfo/phpmyadmin-devel