Re: Excel to MySQL

Yet another good way (if you have Windows) is to grab a copy of the MSDE (comes with most distros of Office, on the CD.. you'll hafta search for it), which is essentially a stripped down version of Microsoft SQL Server. Install that, and on your start menu somewhere (it'll say like MSDE or something), there will be the Import and Export Wizard. It supports tonnes of database formats, and allows you to map fields to other fields. Use that in conjunction with a MySQL ODBC driver, and you're cooking with gravy. Mike Gerard Onorato wrote:

Another idea that may work... and I say may because I have never done it myself. Is to use MyODBC drivers and do the import code native to Access. I would be interested to hear if others have done this. Gerard -----Original Message----- From: Jeff Gannaway [mailto:webmaster@cactusgraphics.com] Sent: Saturday, October 27, 2001 10:30 AM To: Daniel Harik; php-general@lists.php.net Subject: Re: [PHP] Excel to MySQL At 04:11 PM 10/27/01 -0800, Daniel Harik wrote:

You see i have huge(for me) 100 000 record access table, i wanted to convert it to mysql, i thought of making php convertor that uses odbc and mysql, but maybe there is faster way?

Daniel, You're making it too hard on yourself. 1. CREATE TABLES in MySQL that match up to your Access tables. 2. In MICROSOFT ACCESS, go to the Export... command (I believe it's under the File menu.) 3. You'll save it as a .txt file with NO TEXT QUALIFIERS, TAB DELIMITED, without having column names on the first row. 4. FTP the .txt file to your server. I always use ASCII mode for it because I remember one time having some problem which I think was due to Binary transfer (I could've been wrong about that, though). 5. Go into MySQL an use the LOAD DATA INFILE command. ie: LOAD DATA INFILE '/home/virtual/yoursite/home/html/filename.txt' REPLACE INTO tablename; The first few times it can be kind of sticky, but after you do it a couple fo times, it will be as easy as breathing. There is 1 compatibility issue that I ran into. If a field in MS ACCESS has data on 2 lines.... For instance... +----+-------------------------------+

| ID | Address                       |

+----+-------------------------------+

| 1  | 123 Evergreen Terrace         | <-- This is GOOD

+----+-------------------------------+

| 2  | 321 Pine Street               | <-- This is BAD
|    | Apartment A                   |

+----+-------------------------------+ You see, when MySQL runs the LOAD DATA, new records are indicated by a NEW LINE. You'll want to check your ACCESS table for new lines before creating the export file. Later, Jeff Gannaway ___________________________________________________________ Save 15% on 2002 Calendars and Holiday Cards! http://www.AvantGifts.com Discount Code: hopper22ct Offer Good Through October 31, 2001 ___________________________________________________________ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: php-general-unsubscribe@lists.php.net For additional commands, e-mail: php-general-help@lists.php.net To contact the list administrators, e-mail: php-list-admin@lists.php.net