Hello,
Michael Roush's comments are right on the mark. Especially the trial and
error part. I have just gone through this process for a new client and here
are few gotcha's.
If your Excel data fields have fields that contain spaces or commas, like
"123 Main St., Suite 201", when you create a CSV file (at least with Excel
2000), the data will be comma separated, but only the fields with space will
be enclosed in double quotes. MySql will require double quotes. if you have
fields without data, MySQL wants to see those fields as ,NULL, - no quotes.
I have found the easiest way around this was to import the Excel worksheet
into Access as a new table and then export it as CSV file. All fields came
out with double quotes.
OK, then depending on the server that is running the MySQL, things work
differently. This client's server PHPMyAdmin would not allow "insert data
from text file", even though it was there on the page. So I had to create a
text file with all of the INSERT statements and use the "Or Location of the
textfile :" and browse to my file.
I created this file by importing my CSV file into Word and doing a bunch of
replace commands like replacing the paragraph mark "^p" with ");^pINSERT
into MEMBERS values (" and replacing any ",," with ",NULL," (have to do that
twice).
Once you gone through it once it falls into place after that.
Good luck,
Don Smart
-----Original Message-----
From: [email protected] [mailto:[EMAIL PROTECTED]
Sent: Monday, March 21, 2005 9:21 AM
To: [email protected]
Subject: [php_mysql] Digest Number 1112
There are 2 messages in this issue.
Topics in this digest:
1. excel & mysql
From: "OwaiZ" <[EMAIL PROTECTED]>
2. Re: excel & mysql
From: Michael Roush <[EMAIL PROTECTED]>
________________________________________________________________________
________________________________________________________________________
Message: 1
Date: Sun, 20 Mar 2005 18:42:56 -0000
From: "OwaiZ" <[EMAIL PROTECTED]>
Subject: excel & mysql
Hello
I would like to know how to import excel worksheet on to mysql
database.
asuming i have same fields in excel and mysql is it possible ?
thanks
________________________________________________________________________
________________________________________________________________________
Message: 2
Date: Mon, 21 Mar 2005 07:03:08 -0800 (PST)
From: Michael Roush <[EMAIL PROTECTED]>
Subject: Re: excel & mysql
--- OwaiZ <[EMAIL PROTECTED]> wrote:
>
> I would like to know how to import excel worksheet on to mysql
> database.
>
> asuming i have same fields in excel and mysql is it possible ?
> thanks
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
_
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/
