What ⚡

A simple script to resort any CSV file in chronological order.

Why 🤷‍♂️

I usually want to see exported date in the order it was created and many tools, like task tools, will reorder based other criteria.

I’ve created backup-to-markown scripts that work more efficiently if the input CSV is already formatted chronologically so this needn’t be fixed while processing.

How 📋

1. Copy and paste the PHP code below into a text file and rename the extension to PHP like, “Cron.php”
2. Modify the input and output variables at the top to your preference based on location of CSV you are working with
3. Set the column containing the timestamp value
4. Determine how these timestamp values are formatted and keep as “milliseconds” in format variable if this matches your input data or leave empty if in standard seconds.
5. Run script in your terminal app simply by typing php Cron.php if you’re in the same directory as the file or include the full path to the file just by dragging the actual file into your terminal after typing PHP.

$currentDirectory = __DIR__;
// Define input and output file paths
$inputFile = $currentDirectory.'/csvs/clickup2023_10_28.csv';
$outputFile = $currentDirectory.'/csvs/Cron.csv';

$timestamp_column = 4;

$timestamp_format = "milliseconds";

// Read the CSV data into an array
$rows = [];
if (($handle = fopen($inputFile, 'r')) ! false) {
    while (($data = fgetcsv($handle, 1000, ',')) ! false) {
        $rows[] = $data;
    }
    fclose($handle);
}

function sortByTimestamp($a, $b) {

    global $timestamp_column, $timestamp_format;

    if ($timestamp_format  "milliseconds") {
    // Compare timestamps in column 4
    $timestampA = intval($a[$timestamp_column]) / 1000; // Convert milliseconds to seconds
    $timestampB = intval($b[$timestamp_column]) / 1000;
     } else {
        $timestampA = $a[$timestamp_column];
        $timestampB = $b[$timestamp_column];
     } 

    if ($timestampA ! false && $timestampB ! false) {
        return $timestampA - $timestampB;
    }

    // Handle missing or invalid timestamps by moving them to the end
    return 1; // You can adjust this based on your sorting preference
}

// Sort the array by timestamp
usort($rows, 'sortByTimestamp');

// Write the sorted data to a new CSV file
if (($handle = fopen($outputFile, 'w')) ! false) {
    foreach ($rows as $row) {
        fputcsv($handle, $row);
    }
    fclose($handle);
}

echo "CSV file sorted and saved as $outputFile.";