Excel saved as XML import via PHP into mySQL

Posted on 2009-02-13
Last Modified: 2013-12-12
I want to import a rather simple excel spreadsheet into mySQL through PHP -- I have a PHP form that allows the user to upload a filename into my program which will then do various processing and insert certain rows into mySQL.  I am very close to having it done but I've reached some obstacles.

My current attempt is to have the users save the spreadsheet as XML format and uploading that. What's going on is that when you do the microsoft excel save as XML process and you have some columns without any data, it doesn't export the empty <CELL></CELL>, rather it skips the empties and just references an index number for the next cell to "catch it up".  My XML parsing is expecting the cells to be sequential. Actually, I put code in there to read the index attribute but it apparently never finds it and so it defaults to handling sequentially.

I will post my code that i have so far. What I want in terms of a solution (and I'll be happy to award points) is either (1) show me how to fix my current code with the XML import or (2) show me how to change it to instead import from XLS file, if that will make things easier.

There's also an extra problem (but I'm not that stressed about it at this point) -- numeric data is assumed to have precision, so zip code is coming across as 77777.0, for example, instead of 77777.  

Thanks so much.
My spreadsheet is very simple, it's an addressbook... so company name, address1, address2, city, state, zip, phone, url.  Let's say for example that an entry has address1 but not address2, this is what comes across in the generated XML file:


    <Cell><Data ss:Type="String">My Company Name</Data></Cell>

    <Cell><Data ss:Type="String">3000 Nowhere Drive</Data></Cell>

    <Cell ss:Index="4"><Data ss:Type="String">CityName</Data></Cell>

    <Cell><Data ss:Type="String">ST</Data></Cell>

    <Cell><Data ss:Type="Number">32805.0</Data></Cell>

    <Cell ss:Index="8"><Data ss:Type="String"></Data></Cell>


Here's my code:



	if ( $_FILES['importfile']['tmp_name'] )


		$doc = new DOMDocument();

		$doc->load($_FILES['importfile']['tmp_name']) ;


		$rows = $doc->getElementsByTagName( 'Row' );


		$first_row = true;

		foreach ($rows as $row)


			if ( !$first_row )


				$cname = "";

				$add1 = "";

				$add2 = "";

				$city = "";

				$st = "";

				$zip = "";

				$phone = "";

				$url = "";


				$index = 1;

				$cells = $row->getElementsByTagName( 'Cell' );


				foreach( $cells as $cell )


					$ind = $cell->getAttribute( 'Index' );

					// this apparently never works -- it doesn't find an index value


					if ( $ind != null ) $index = $ind;

					// so it's always just using the incremental. If you can tell me what

					// I've done wrong where the Index attribute doesn't work, that will help

					if ( $index == 1 ) $cname = addslashes($cell->nodeValue);

					if ( $index == 2 ) $add1 = addslashes($cell->nodeValue);

					if ( $index == 3 ) $add2 = addslashes($cell->nodeValue);

					if ( $index == 4 ) $city = addslashes($cell->nodeValue);

					if ( $index == 5 ) $st = $cell->nodeValue;

					if ( $index == 6 ) $zip = $cell->nodeValue;

					if ( $index == 7 ) $phone = $cell->nodeValue;

					if ( $index == 8 ) $url = $cell->nodeValue;


					$index += 1;



				// validate required data -- rmoved for brevity

				// now do the insert...


				} // end of first row

			$first_row = false;

			} // end of foreach

		} // end of if found file

	else {

		echo "else did not find file to open<br>" ;





Open in new window

Question by:nonesuch
    LVL 6

    Accepted Solution

    What I would do is to save the file as csv. If there is no value in the cell, it will show as empty. In my practice it is the easiest way. You don't need any extra parser to read the data. You select the delimiter, let's say a pipe and a line of your file will look e.g.

    Naples|Florida|USA|34109| ...

    then just use:

    $fp = fopen('abc.csv'') or die;
    while(! feof($fp)) {
      $line = fgets($fp);
      $ex = explode("|", $line);
      // and you have all values in $ex[0], $e[1], etc ready to filter and enter in the database.
    If the database is small and does not change, I would even keep the data in the text file a retrieve them for the web as above.
    Hope this will help you.

    Open in new window

    LVL 3

    Author Comment

    It's an address book spreadsheet, so there are going to be commas in the data. I really don't want to ask some non-technical users to export the file and select a different delimiter like a pipe, I think that would be pushing their expertise a bit.

    I'm looking for a solution that makes it easy on the front-end users so I can import the file. Either figuring out a way to read the index value in the XML data as I have it, or reading straight from XLS.  
    LVL 3

    Author Closing Comment

    I'm going to go ahead and reward the points -- it didn't really address what I wanted to do but it's definitely an alternative. I just figured out my own problem, I should do        $ind = $cell->getAttribute( 'ss:Index' ); and the indexing works fine.  Thanks for your assistance.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Suggested Solutions

    Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit ( and similar technologies have enjoyed wide adoption, making it possib…
    Part of the Global Positioning System A geocode ( is the major subset of a GPS coordinate (, the other parts being the altitude and t…
    This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
    The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

    737 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

    22 Experts available now in Live!

    Get 1:1 Help Now