Skip to content

Instantly share code, notes, and snippets.

@ptaferner
Forked from robflaherty/csv-to-json.php
Last active August 29, 2015 14:27
Show Gist options
  • Save ptaferner/6e8ed83e8fdf87b48586 to your computer and use it in GitHub Desktop.
Save ptaferner/6e8ed83e8fdf87b48586 to your computer and use it in GitHub Desktop.
Convert CSV to JSON
*.csv
*.json
* Column names renamed
* Quotation marks removed
* Leading zeros removed for LineIndex
#!/bin/bash
MAINDIR=$HOME/haas
PHP_SCRIPT=$HOME/haas/csv-to-json.php
CSV_FILES=("Pages.csv" "LineInformation.csv" "DataValueLines.csv" "ActionListVertical.csv")
DATE=`date +%Y%m%d`
DATEM=`date +%Y_%m_%d_%H_%M_%S`
echo 'Do not forget to set the paths!'
cd $MAINDIR
for file in ${CSV_FILES[@]}; do
if [ ! -f $file ]; then
echo "No new files."
exit 1
fi
done
if [ ! -d $DATE ]; then
mkdir $DATE
fi
for file in ${CSV_FILES[@]}; do
mv -n $file $DATE/${DATEM}_$file
done
php $PHP_SCRIPT $MAINDIR/$DATE/$DATEM
<?php
/*
* Converts CSV to JSON for Spidernet project.
* Reformats CSV data according to specification.
*/
// Function to check column names against specs.
function checkHeaders($name, $headers)
{
$validHeaders = array(
'Actions' => array('ActionIndex', 'LineIndex', 'DeviceIndex', 'Type', 'Parameter'),
'Values' => array('DataIndex', 'Name', 'Type', 'Unit'),
'Lines' => array('LineIndex', 'LineName', '1_Picture', '1_Name'),
'Pages' => array('DefinitionIndex', 'LineIndex', 'DeviceIndex', 'Type', 'Size', 'DataIndex1', 'DataIndex2', 'DataIndex3'),
);
$altDelimiters = array('tab' => "\t",
'colon' => ":",
'semicolon' => ";"
);
// It's not necessary to check all values for Values and Lines.
switch ($name) {
case 'Values'; case 'Lines';
$headers = array_slice($headers, 0, 4);
}
if ($headers !== $validHeaders[$name]) {
// Check for a wrong delimiter.
foreach ($altDelimiters as $dkey => $dvalue ){
if (count($validHeaders[$name]) == count(explode($dvalue, $headers[0]))) {
trigger_error("Wrong delimiter in CSV file of $name: $dkey.");
}
}
trigger_error("CSV headers in $name not compliant with specs.");
var_dump($headers);
}
}
// Function to convert CSV into associative array
function csvToArray($file, $delimiter)
{
$handle = fopen($file, 'r') or die('Cannot open file: '.$file);
$i = 0;
while (($lineArray = fgetcsv($handle, 20000, $delimiter)) !== false) {
for ($j = 0; $j < count($lineArray); ++$j) {
// Cast numeric strings (for indices)
$val = $lineArray[$j];
/* if (is_float($val)) $arr[$i][$j] = floatval($val); */
/* else if (is_numeric($val)) $arr[$i][$j] = intval($val); */
/* else $arr[$i][$j] = $val; */
$arr[$i][$j] = (is_numeric($val)) ? floatval($val) : $val;
}
++$i;
}
fclose($handle);
return $arr;
}
$INPUT_FILES = array(
'Actions' => $argv[1].'_ActionListVertical.csv',
'Values' => $argv[1].'_DataValueLines.csv',
'Lines' => $argv[1].'_LineInformation.csv',
'Pages' => $argv[1].'_Pages.csv',
);
foreach ($INPUT_FILES as $inputName => $inputFile) {
// Make array from CSV
$data = csvToArray($inputFile, ',');
// Use first row for names
$keys = array_shift($data);
checkHeaders($inputName, $keys);
$combinedArray = array();
// Skip dates in Values if still included
if ($inputName == 'Values') {
while (True) {
if (!array_key_exists(4, $data[0]) || !is_numeric($data[0][4])) array_shift($data);
else break;
}
}
// Set number of elements
$rowCount = count($data);
// Combine labels and data arrays to new array
for ($j = 0; $j < $rowCount; ++$j) {
$row = $data[$j];
$rowArray = array_combine($keys, $row);
// Make array structure a bit cleaner
switch ($inputName) {
case 'Pages':
// Collect all values from DataIndex in an array
$idxKeys = array_slice($keys, -3);
for ($k = 0; $k < 3; ++$k) {
$el = array_pop($row);
unset($rowArray[array_pop($idxKeys)]);
if (!empty($el)) $rowArray['data'][] = $el;
}
break;
case 'Values':
// Collect all reads in an array
$rowArray = array_slice($rowArray, 0, 4);
$rowArray['data'] = array_slice($row, 4);
break;
case 'Lines':
// Rename LineName
$lineNames = array('Flat wafer', 'Crackers', 'Batter', 'Cones');
foreach ($lineNames as $lname) {
if (stripos($rowArray['LineName'], $lname) !== False) $rowArray['LineName'] = $lname;
}
// Remove the devices info; we'll reconstruct it now
$rowArray = array_slice($rowArray, 0, 2);
$row = array_slice($row, 2);
$k = 0;
// Collect all devices for a line in an array
while (count($row) > 1) {
$k++;
$name = array_shift($row);
$picture = array_shift($row);
// Check for non-empty and a '.' indicating a filename
if (!empty($name) && strpos($name, '.') !== FALSE && !empty($picture)) {
$rowArray['devices'][] = array(
'index' => $k, // corresponds to the DeviceIndex
'name' => $name,
'picture' => $picture,
);
}
}
break;
}
$combinedArray[$j] = $rowArray;
}
$outArray[$inputName] = $combinedArray;
}
// Write out to JSON file
$outputFile = "$argv[1]_data.json";
$handle = fopen($outputFile, 'w') or die('Cannot open file: '.$outputFile);
fwrite($handle, json_encode($outArray));
fclose($handle);
Display the source blob
Display the rendered blob
Raw
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment