excell-->php-->mySQL

[Billy T]

Hey all

I have an excel database that I want to load into php, break up the entries and insert the data into a mySQL database

what is best format to export the data from excel in before I import it into php?

tab delimited?
comma delimited?

Thanks

[freddycodes]

Billy, you have several options, you can export tham as csv files from excel, and use fgetcsv() in PHP. This pulls a line from the csv file and splits it up for you into an array.

You can convert the excel spreadsheet to access, use myODBC to connect to the remote mysql database from access and create the tables from the existing ones.

You can export to any delimited text file and then do the conversion using PHP with unlimited methods.

Let me ask you this though, are the tables created already on the remote server?

If so, you can look at load data infile a function of mysql to import data directly from delimited files without PHP at all.

[Billy T]

hey freddy

yep the db table is already created. The excel database will have some but not all of the columns from mysql database (can easily add them if it makes life easier though)

is myODBC like phpMyAdmin?

fgetcsv() sounds interesting!

Thanks again ;)

[freddycodes]

myODBC is an odbc driver for windows that allows you to make ODBC connections to a remote MySQL database. So you can use Access to make an odbc connection to MySQL and view the tables in Access.

Regardless this may be a lengthy process for someone who has not done it before. I would first look at the csv option with
http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#LOAD_DATA

To load the data right into MySQL from the mysql console.

[Billy T]

cool I'll check it out

Thanks freddy!

[Billy T]

couldnt get anywhere with LOADDATA - my sql knowledge is even worse than my php

fgetcsv is working beautifully though

Thanks freddy!

[freddycodes]

Good Deal.