PHP and Excel

Posted on 2006-04-20
Last Modified: 2008-03-04
hello everyone,

I am doing my first attempt at using PHP with excel.  I need to import all the values from my excel worksheet into MySQL.  Does anyone have any clue how to do so?  I have checked alot of sites from my searches on Google, but i cant figure it out.  

Any info you could give me would be greatly appreciated.

- Dan
Question by:flow79
    LVL 15

    Expert Comment

    The only way PHP can read Excel files is if it's running on Windows and through ODBC. On Linux you need to save your Excel worksheets as CSV/tab delimited.

    If you only need to do it once, you might want to try out this product:
    LVL 40

    Expert Comment

    I use the very good Zakkis Excel reader class.

    There is a good example with the class showing you just how to do what you want.

    LVL 40

    Accepted Solution

    LVL 11

    Expert Comment

    I would recommend saving Excel in a CSV format.

    PHPMYADMIN will allow direct imports from this!
    LVL 40

    Expert Comment

    I would STRONGLY recommend NOT using CSV files.

    They are VERY easy to break.

    Think of this.

    Your columns are separated by a comma.
    Your text columns are surrounded by single or double quotes (pick 1).
    Your records are terminated by a CR/LF (one or both, depending upon OS).

    Now say you have a description of ...

    Black, nylon rope 12'6" long.


    The , will be ok as it will be within single or double quotes.

    Try double

    "Black, nylon rope 12'6" long."


    "Black, nylon rope 12'6"

    the [space]long. is lost.

    Single quotes.

    'Black, nylon rope 12'6" long.'

    Now we add 6" to the lost part.

    CSV is unstructed data.

    If you intend to use a plain text file, you need to do 2 thing.

    Use a column separator that cannot appear as part of the data.
    Use a row separator that cannot appear as part of the data.

    In most cases, CSV files have no support for multiple lines of text, when the text contains the same row terminator. On windows, text lines are terminated with CR/LF. If I have multiple lines of text in a cell, then CR/LF will be on each line.

    You may not think any of this applies to you. But are you importing people's surnames. I work with a family of O'Connells and we make products 385/80R22.5".

    Using a CSV file would fail for us.

    In my nearly 20 years of software development, I would say the CSV file has caused more problems with customers data than any virus/worm/hack! At least you know you have to restore all the data after a virus! But when the CSV importer (and there are a LOT of these) says "Hey! Thanks for the data! Yummy!" and it all gets screwed up and you don't know until weeks/months later ... then you REALLY know the feeling of panic/horror!

    So. Please do NOT use a CSV file. For your own personal well-being.


    Please, please, please!


    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    This is a general how to create your own custom plugin system for your PHP application that you designed (or wish to extend a third party program to have plugin functionality that doesn't have it yet).  This is not how to make plugins for existing s…
    Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
    The viewer will learn how to dynamically set the form action using jQuery.
    The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now