Just a couple caveats about phpMyAdmin's import tool:
Your CSV file needs to be pretty small < 2MB.
PHP has a limit on the Max file size you can upload.
If your spreadsheet is huge, this could be an issue.
PHP also has a max_execution_time() value that affects
phpMyAdmin.
Again, this will only get you if you've got a big file to
import, but it will crap out.
Some alternatives:
1.) If you were using Access instead of Excel, you could use
Access's Link Table feature.
You'd need to install the Connector/ODBC driver, and setup
a connection to your MySQL database.
Look in the API section here: http://dev.mysql.com/downloads/
Link tables is also cool if you want to use access to
update/maintain your MySQL database directly.
It's not without it's quirks, but it's a nice way to make
data-entry interfaces that might be too clunky/slow to make browser-based.
2.) I prefer the CSV method when I can use it; however, with
big files, I start with a CSV file and turn it into SQL INSERT
statements with a text editor.
"fname","lname","address","city","state","zip"
becomes:
INSERT INTO table VALUES
("fname","lname","address","city","state","zip");
or if your columns in the spreadsheet are in a different
order than your DB
INSER INTO table VALUES
(fname,lname,address,city,state,zip) VALUES
("fname","lname","address","city","state","zip");
Use something that supports macros (www.textpad.com), so
you can record the first line of changes, then apply it to the whole
document.
Then you can just upload the file to the webserver and use
the commandline mysql tool to import your data like:
mysql -u username -p database < importdata.txt
Of course, you need shell access...
-Jeromie
>Yes, it is certainly possible, but it can take a bit of trial-and-error.
>Using Excel, you can save a file as a .csv file (CSV stands for 'comma
>separated values' I believe), which is essentially just a plain text file.
> If you are using phpMyAdmin or some such tool, you can use the "Insert
>data from a textfile into table" link at the bottom of the table
>'structure' view pane. Set the settings for what you need (this is the
>part that can take a little trial-and-error, learning just what your
>end-of-line marker is and such.) and it will perform all the insert
>queries for you.
>
>It is best to make an empty 'test table' with the same structure as the
>intended target table and try inserting the values there first, so that
>you can fiddle with the end-of-line settings and such until you have it
>right. The good part is, once you figure out how MySQL and Excel will
>interact on your particular flavors of local OS, Web server OS, etc., you
>can transfer Excel 'databases' into MySQL database tables to your hearts
>delight!
>
>
>Michael Roush
>[EMAIL PROTECTED]
>
>"The power of the Web is in its universality. Access by everyone regardless of
>disability is an essential aspect."
>-- Tim Berners-Lee, W3C Director and inventor of the World Wide Web
>
>
>
>__________________________________
>Do you Yahoo!?
>Yahoo! Small Business - Try our new resources site!
>http://smallbusiness.yahoo.com/resources/
>
>
>The PHP_mySQL group is dedicated to learn more about the PHP_mySQL web
>database possibilities through group learning.
>Yahoo! Groups Links
>
>
>
>
>
>
>
>
>
>
The PHP_mySQL group is dedicated to learn more about the PHP_mySQL web database
possibilities through group learning.
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/php_mysql/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
