[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 560
  • Last Modified:

Excel saved as XML import via PHP into mySQL

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

  • 2
1 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

nonesuchAuthor Commented:
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.  
nonesuchAuthor Commented:
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.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now