We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now


Excel saved as XML import via PHP into mySQL

nonesuch asked
Medium Priority
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">http://www.mycompany.com</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

Watch Question

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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts


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.  


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.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.