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
Loading
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment