Split CSV file with unix timestamps into 7 different csv files

Question

I have a large 2 column csv file ("data.csv") with a weeks worth of data. Column one has unix time stamps Column two has strings:

1329548400,cats
1329548400,dogs
1329550200,fish
1329550200,cats
1329552000,dogs
1329552000,fish
1329584400,cats
1329584400,dogs
1329550200,cats

Via php, I need to convert data.csv into 7 files, Sunday.csv, Monday.csv...Saturday.csv

If I could grab each row, I know I can do something like

$temp = "data.csv";
$fileName = array_unique($temp);
foreach ($fileName as $row) {
    $theDay = date("Y", $row[firstcolumn]);
    if ($theDay == "Sunday") {
        $fileToWriteTo = "Sunday.csv";
        $f = fopen($fileToWriteTo, "a");
        fwrite($fileToWriteTo, $row . "\n");
        fclose($fileToWriteTo);
        }
    if ($theDay == "Monday") {
        $fileToWriteTo = "Monday.csv";
        $f = fopen($fileToWriteTo, "a");
        fwrite($fileToWriteTo, $row . "\n");
        fclose($fileToWriteTo);
        }
    }

Problem is

1) I don't know how to read each row of data.csv, assuming the syntax above is wrong 2) I admit Im a bit of a php laymen :)

...how far off am I? Am I even in the ballpark?

possible duplicate of [how to extract data from csv file in php](http://stackoverflow.com/questions/2805427/how-to-extract-data-from-csv-file-in-php) — Gordon, Feb 16 '12 at 14:54
There is a special function [fgetcsv](http://php.net/manual/en/function.fgetcsv.php) which might come in handy for you ʘ͜ʘ — yunzen, Feb 16 '12 at 14:58

score 0 · Answer 1 · answered Feb 16 '12 at 14:45

0

If you are reading from a file you need to create a handle to read that file and then get each line and then parse.

$handle = fopen('data.csv','r');
while($line = fgets($handle)){
   //Do any parsing here.
   $array = explode(',',$line);
   $timestamp = $array[0];
   $string = $array[1];
   //Do whatever else you need to do with it.
}

Try reading this if you want to create more files from one file.

http://php.net/manual/en/function.fopen.php

answered Feb 16 '12 at 14:45

jjs9534

475
2
7

Waaaah! Every time somebody reads and explodes a CSV file instead of using fgetCSV() an elephpant dies – Mark Baker Feb 16 '12 at 15:14

core1024 · Answer 2 · 2012-02-16T15:24:08.233

0

Maybe this would do the job. I haven't tested it.

$temp = "data.csv";
$fileData = file($temp);
$errors = 0;
$days = Array(fopen('Sunday.csv','a+'),
              fopen('Monday.csv','a+'),
              fopen('Tuesday.csv','a+'),
              fopen('Wednesday.csv','a+'),
              fopen('Thursday.csv','a+'),
              fopen('Friday.csv','a+'),
              fopen('Saturday.csv','a+'));
foreach($days as $fd) $errors += ($fd === FALSE);
if( ! $errors)
{
    foreach ($fileData as $row) {
        $columns=explode(',',$row);
        $theDay = date("w", $columns[0]);
        if(empty($days[$theDay])) continue; // maybe not valid date
        fwrite($days[$theDay], $row."\n");
    }
}
else
{
    echo "I couldn't open $errors of the files\n";
}
foreach($days as $fd) if($fd !== FALSE) fclose($fd);

edited Feb 16 '12 at 15:24

answered Feb 16 '12 at 14:56

core1024

1,882
15
22

Yeah open and close file in each iteration, way to go! ** :) – Vyktor Feb 16 '12 at 15:01
You're right. Maybe I had to put the file descriptor in the $days array and close them at the end. – core1024 Feb 16 '12 at 15:02
Not checking whether file was opened successfully .)) (use @Vyktor so I'll get notice about the comment) – Vyktor Feb 16 '12 at 15:09
THIS...is what I needed: $columns=explode(',',$row); theDay = date("w", $columns[0]); Should work fine...thanks all...! – Erik Malson Feb 16 '12 at 16:06

Vyktor · Accepted Answer · 2012-02-16T15:06:23.650

At first, don't open file at each iteration do it before loop.

$fps = array(); // fps = File Pointers
$days = array( 'Sunday', 'Monday', 'Tuesday', ...);
foreach( $days as $key => $name){
    $fps[ $name] = fopen( 'a', $name . '.csv');
    if( !$fps[ $name]){
        die("Cannot open $name.csv");
    }

    // Although I wouldn't rely on date's `l` format, because
    // it can be localized and script may just stop working
    // and rather use:
    $fps[ $key] = fopen( 'a', $name . '.csv');
    if( $fps[ $key])...
    // Sunday = 7 = 0, Monday = 1...
}

Now, you'll address fields via date( 'w'), which will give numbers 0 - Sunday, 6 - Saturday:

$day = date( 'w', $timestamp);

About making unique results... You'll probably need to use function like in_array() and store all processed rows in one array.

About reading and writing CSV files, there are 2 functions: fgetcsv() resp. fputcsv(). So entire loop:

$temp = "data.csv";
$rows = array(); // List of unique itemps
$fp = fopen( $temp, 'r') or die( "Cannot open `$temp` for reading");
// Put here loop which will open all 7 files as mentioned above
// Optionally skip first row (it may contain header)
fgets( $fp);

// Read each row
while( ($array = fgetcsv( $fp, 10000, ',', '"') !== false){
    // Check unique row
    $row = implode( ',', $array);
    if( in_array( $row, $rows)){
         continue; // Skip this iteration
    }
    $rows[] = $row;

    $day = date( 'w', $array[0]);
    fputcsv( $fps[$day], $array);
}

And don't forget to close all files:

fclose( $fp);
foreach( $fps as $fp){
    fclose( $fp);
}

Split CSV file with unix timestamps into 7 different csv files

3 Answers3