Export/Import Data

csv2json-pinnacle.php

<?php
// SimpleExcel source: https://github.com/faisalman/simple-excel-php/tree/master
// Note: Use the older 0.3 branch and not the 0.4 "develop" branch.
require_once('SimpleExcel-0.3.15/SimpleExcel.php');
use SimpleExcel\SimpleExcel;

//~ echo 'foo';

function trim_value(&$value) {
	$value = trim($value);
}

//~ $excel = new SimpleExcel('CSV', array('delimiter' => '~'));
$excel = new SimpleExcel('CSV');
//~ echo 'foo';
$excel->parser->loadFile('pinnacle-test.csv');
//~ echo 'foo';

// Load the spreadsheet as an array.
// The fields for Pinnacle are:
// 1. Stock Code
// 2. Category
// 3. Category 1
// 4. Category 2
// 5. Category 3
// 6. Catogory 4
// 7. Description
// 8. Brand
// 9. Status
// 10. Reseller Price (Excl. VAT)
// 11. Reseller Margin (%)
// 12. Calculated Retail Price (Incl. VAT)
// 13. Warranty
// 14. Product URL
$rows = $excel->parser->getField();
//~ print_r($fields);
//~ echo count($fields);

foreach ($rows as $key => $value) {
	list($col1, $col2, $col3, $col4, $col5, $col6, $col7, $col8, $col9, $col10, $col11, $col12, $col13, $col14) = $value;
	//~ echo "$col1, $col2, $col3, $col4, $col5, $col6, $col7, $col8, $col9, $col10, $col11, $col12, $col13, $col14<br><br>";
	//~ var_dump($value);
	
	// Remove whitespace from every cell.
	array_walk($rows[$key], 'trim_value');
	
	// Field *2. Category* is a " - " separated mixed field of categories.
	// Split field *2. Category* into categories 1, 2, 3 and 4 as needed.
	$categories = explode(" - ", $col2);
	list($col3, $col4, $col5, $col6) = $categories;
	$rows[$key][2] = $col3;
	$rows[$key][3] = $col4;
	$rows[$key][4] = $col5;
	$rows[$key][5] = $col6;
	
	unset($rows[$key][$value]);
}

//~ var_dump($rows);

// TESTING
//~ for ($i = 0; $i < count($rows); $i++) {
	//~ for ($j = 0; $j < count($rows[$i]); $j++) {
		//~ echo $rows[$i][$j]."<br>";
	//~ }
//~ }

$excel->convertTo('JSON');
$excel->writer->setData($rows);
$excel->writer->saveFile('test');

Steps to convert Pinnacle spreadsheet to JSON

1. Export "Main Pricelist" sheet as CSV.
2. Cleanup the CSV file:
  2.1. Run macro to extract URLs from hyperlinks.
  2.2. Cut the extracted URLs and paste into the "Product URLs" column
  2.3. Use Search & Replace regex to convert \n to ' ':
  
  ```
  Find: \n
  Replace: _ // As in ' ' or a single press of the spacebar
  ```
  
3. Save as a regular CSV with ',' as the separator and '"' for text.
4. Copy CSV file to web server.