how to fill in a mySql database from an excel file?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
tonguim
Forum Newbie
Posts: 21
Joined: Mon Jun 30, 2003 12:53 pm
Location: Ouagadougou, BURKINA FASO
Contact:

how to fill in a mySql database from an excel file?

Post by tonguim »

Hy
I have an excel file which have 728 records; i would like to fill in my mySql database with this excel file. It seems that it's possible, but i don't have no more informations on how to do it. Can you help me? Thanks.

[url = http://tonguim.free.fr][/url]
JPlush76
Forum Regular
Posts: 819
Joined: Thu Aug 01, 2002 5:42 pm
Location: Los Angeles, CA
Contact:

Post by JPlush76 »

mmmmmm mysql load function yummmmmy

I actually do this 4-8 times a week at my work. Basically you just have to save your excel file as a tab separated text file and then upload that text file to your webserver and do the following

LOAD DATA INFILE '/usr/local/apache/htdocs/yourdirectory/nameoffile.txt' INTO TABLE mytable FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';
User avatar
Stoker
Forum Regular
Posts: 782
Joined: Thu Jan 23, 2003 9:45 pm
Location: SWNY
Contact:

Post by Stoker »

Or you could simply use ODBC I would think?
JPlush76
Forum Regular
Posts: 819
Joined: Thu Aug 01, 2002 5:42 pm
Location: Los Angeles, CA
Contact:

Post by JPlush76 »

ODBC is yucky and unstable ;)
User avatar
Stoker
Forum Regular
Posts: 782
Joined: Thu Jan 23, 2003 9:45 pm
Location: SWNY
Contact:

Post by Stoker »

Yucky may be, that's a matter of opinion..

what is unstable?
AFAIK the myODBC Stable driver is pretty good? And I doubt that Excel dont know how to handle it...

If you refer to PHP's ODBC functionality, that may be, I've had bad experiences with (unsupported) UnixODBC and generic openlink drivers on GNU/Linux, but in this thread I was refering to an Excel-ODBC-MySQL connection without using PHP....
JPlush76
Forum Regular
Posts: 819
Joined: Thu Aug 01, 2002 5:42 pm
Location: Los Angeles, CA
Contact:

Post by JPlush76 »

I've actually never tried odbc for excel/mysql

I've tried the odbc drivers for mysql to an as/400 database it was pretty horrid
tonguim
Forum Newbie
Posts: 21
Joined: Mon Jun 30, 2003 12:53 pm
Location: Ouagadougou, BURKINA FASO
Contact:

Post by tonguim »

Thank you JPlush76 and Stoker for your instant answer.
I'll try what you suggest me.
JPlush suggest me to do this:

LOAD DATA INFILE '/usr/local/apache/htdocs/yourdirectory/nameoffile.txt' INTO TABLE mytable FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';

Should i write these lines in a program, or where should i insert these lines? Thanks
JPlush76
Forum Regular
Posts: 819
Joined: Thu Aug 01, 2002 5:42 pm
Location: Los Angeles, CA
Contact:

Post by JPlush76 »

oh sorry...
you'll want to do this in phpmyadmin SQL box or your sql input line or you can do it in a program in your mysql call. Its a mysql operation.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Topic moved to Databases as it did not belong in PHP - Advanced.

Mac
tonguim
Forum Newbie
Posts: 21
Joined: Mon Jun 30, 2003 12:53 pm
Location: Ouagadougou, BURKINA FASO
Contact:

Post by tonguim »

Thanks to Jplush76 for the solution he has given to me about my question "how to fill in a mySql database with an excel file?"; i tried the solution, and the result is what i was expecting. Thanks once more.
JPlush76
Forum Regular
Posts: 819
Joined: Thu Aug 01, 2002 5:42 pm
Location: Los Angeles, CA
Contact:

Post by JPlush76 »

glad I could help :)
User avatar
m3mn0n
PHP Evangelist
Posts: 3548
Joined: Tue Aug 13, 2002 3:35 pm
Location: Calgary, Canada

Post by m3mn0n »

Wow Jplush thanks, i was searching like crazy for a solution for Excell -> MySQL conversion

8)
Post Reply