Worksheet to Table Script -Might find useful

Topics: Developer Forum, User Forum
Aug 11, 2009 at 5:11 AM
Edited Aug 11, 2009 at 5:15 AM

I've managed to get my hack of the phppgadmin dataimport script to work

 

This will insert your records into the proper columns regardless of column order

Some of the loops could probably be tightened up, but it works.

I don't use nulls in my table at all so any empty cell is replaced with an empty string.. easy enough to change if you want to use nulls I guess

 

Any feedback is welcome

You need to edit the database information, the table to insert into, and the file to open.

 

<?php 
//This is essentially the dataimport script from phppgadmin reworked to use an excel sheet rather than a csv or xml file.
error_reporting(E_ALL);

/** PHPExcel_IOFactory */
require_once './Classes/PHPExcel/IOFactory.php';


function getdb() {
    $db = pg_connect("YOUR DB INFO") or die('connection failed');
    return $db;
}
$conn = getdb();
	
$table= 'SET TABLE TO INSERT INTO';

//Load XLS file and 
$objPHPExcel = PHPExcel_IOFactory::load("XLS FILE");

$objPHPExcel->setActiveSheetIndex(0);
$objWorksheet = $objPHPExcel->getActiveSheet();

$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);

//These functions are from phppgadmin 	
function fieldClean(&$str) {
		if ($str === null) return null;
		$str = str_replace('"', '""', $str);
		return $str;
	}
	function schema() {
		return '';
	}
	
	//I'm not using this function properly-- not sure what $allowednulls should be
$allowednulls = '\\'; 
	function loadNULLArray() {
		$array = array();
		if (isset($allowednulls)) {
			foreach ($allowednulls as $null_char)
				$array[] = $null_char;
		}
		return $array;
	}
		function determineNull($field, $null_array) {
		return in_array($field, $null_array);
	}
function clean(&$str) {
		if ($str === null) return null;
		$str = str_replace("\r\n","\n",$str);
		if (function_exists('pg_escape_string'))
			$str = pg_escape_string($str);
		else
			$str = addslashes($str);
		return $str;
	}
$null_array= loadnullarray();

	function formatValue($type, $format, $value) {
		switch ($type) {
			case 'bool':
			case 'boolean':
				if ($value == 't')
					return 'TRUE';
				elseif ($value == 'f')
					return 'FALSE';
				elseif ($value == '')
					return 'NULL';
				else
					return $value;
				break;
			default:
				// Checking variable fields is difficult as there might be a size
				// attribute...
				if (strpos($type, 'time') === 0) {
					// Assume it's one of the time types...
					if ($value == '') return "''";
					elseif (strcasecmp($value, 'CURRENT_TIMESTAMP') == 0
							|| strcasecmp($value, 'CURRENT_TIME') == 0
							|| strcasecmp($value, 'CURRENT_DATE') == 0
							|| strcasecmp($value, 'LOCALTIME') == 0
							|| strcasecmp($value, 'LOCALTIMESTAMP') == 0) {
						return $value;
					}
					elseif ($format == 'EXPRESSION')
						return $value;
					else {
						clean($value);
						return "'{$value}'";
					}
				}
				else {
					if ($format == 'VALUE') {
						clean($value);
						return "'{$value}'";
					}
					return $value;
				}
		}
	}
	
function insertRow($table, $vars, $nulls, $format, $types) {

		if (!is_array($vars) || !is_array($nulls) || !is_array($format)
			|| !is_array($types)) return -1;
		else {
			fieldClean($table);

			$schema = schema();
		
			// Build clause
			if (sizeof($vars) > 0) {
				$fields = '';
				$values = '';
				foreach($vars as $key => $value) {
					fieldClean($key);

					// Handle NULL values
					if (isset($nulls[$key])) $tmp = 'NULL';
					else $tmp = formatValue($types[$key], $format[$key], $value);

					if ($fields) $fields .= ", \"{$key}\"";
					else $fields = "INSERT INTO {$schema}\"{$table}\" (\"{$key}\"";

					if ($values) $values .= ", {$tmp}";
					else $values = ") VALUES ({$tmp}";
				}
				$sql = $fields . $values . ')';
			}
			return $sql;
		}
	}
	//End phppgfunctions

//Get column names from file and place them in an array
$fields = array();
for ($row = 1; $row < 2; ++$row) {
	for ($col = 0; $col < $highestColumnIndex; ++$col) {
		   $fields[$col]=  strtolower($tmpfields[$col] = $values[$row][$col] = $objWorksheet->getCellByColumnAndRow($col, $row)->getValue());
			
	}
}

$line = array();
//We start on the line AFTER the field names
					//grab a line from the file into array $line
					for ($row = 2; $row <= $highestRow; ++$row) {
	for ($col = 0; $col < $highestColumnIndex; ++$col) {
		   $line[$col]=  $tmpfields[$col] = $values[$row][$col] = $objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
		     
			 //This part needs some work-- handling null cells works in conjunction with allowed_nulls above
			  if (!isset($line[$col])) {
							$line[$col] ='';
							}
						}
						// Build value map
						$vars = array();
						$nulls = array();
						$format = array();
						$types = array();
						$i = 0;
						
						
						foreach ($fields as $f) {
								
											
							// Check that there is a column
							if (!isset($line[$i])) {
								echo 'bad column';
								exit;
							}
							// Check for nulls
							if (determineNull($line[$i], $null_array)) {
								$nulls[$f] = 'on';
							}
							// Add to value array
							$vars[$f] = $line[$i];
								
					
							// Format is always VALUE
							$format[$f] = 'VALUE';
							// Type is always text
							$types[$f] = 'text';
							$i++;
													
	}
						
								$status = insertRow($table, $vars, $nulls, $format, $types);
								
								$result = pg_query($conn, $status);
								}
?>