PHP, process and split CSV-file

Question

A S0-logger comes with a csv file on a monthly base. The file is updated every 5 minutes and can be retrieved any moment. At the end of the month the file counts over 8500 rows. When a new month starts a new file is made.

Fileformat is like this:

Datum / Uhrzeit (UTC);Main meter - Sales office (kWh);Meter 9;Temperature - Server room;Supply conductor air conditioner

01.06.12 00:00:00;438.220;0.001;274;155
01.06.12 00:05:00;438.240;0.001;274;203
01.06.12 00:10:00;438.259;0.001;275;134
01.06.12 00:15:00;438.283;0.001;274;176
01.06.12 00:20:00;438.303;0.001;274;206

dd.mm.yy (This is european dateformat)

I want to split the monthly file into a daily file with filename yymmdd.csv and store these files for further use and processing. There is no use for the column names.

During the day, its data is updated every five minutes, but after a day is finished there is no need to reprocess this data, because nothing changes. I found out fgetcsv is the most appropriate method. But how to prevent the reprocessing of the data which is rather time consuming and unnecessary?

Ja͢ck · Accepted Answer · 2012-06-12 14:03:56Z

1

Assuming the monthly file is always appended to.

You could keep a small file named e.g. 2012-january.csv.ptr. This file keeps the last position in the file; if it's non-existent you start at the beginning.

At every successful read, you determine the file pointer using ftell(). When you reached the end, you write the last position inside the .ptr file.

When the .ptr file exists you seek back into the file using fseek() and then start processing as per normal.

answered Jun 12, 2012 at 14:03

Ja͢ck

174k39 gold badges269 silver badges316 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1451304 Over a year ago

Jack, Your idea looks nice, how should I implement it in the code @ioseb gave yesterday? Monthly filename is like this 2012_06.csv

Augustas · Accepted Answer · 2012-06-12 13:55:04Z

0

Can you use any database? Simple table would do for data storage and later procesing.

answered Jun 12, 2012 at 13:55

Augustas

1,22724 silver badges32 bronze badges

1 Comment

user1451304 Over a year ago

I want to split the file, before adding it to a database to keep it simple.

ioseb · Accepted Answer · 2012-06-12 14:05:27Z

0

Here is the code:

if ($fp = fopen('log.csv', 'r')) {
  $line_number = 0;

  while ($line = fgetcsv($fp, 0, ';')) {
    if ($line_number++ == 0) {
      continue;
    }

    $date = explode(' ', $line[0]);
    $file = $date[0] .'.log';

    file_put_contents(
      'monthly/'. $file, 
      implode(';', $line) ."\n",
      FILE_APPEND
    );
  }

  fclose($fp);
}

It reads CSV file line by line, extracts date part from the first column and creates new file and appends data to it.

P.S. folder "monthly" must be writable

answered Jun 12, 2012 at 14:05

ioseb

17k3 gold badges37 silver badges31 bronze badges

3 Comments

user1451304 Over a year ago

I used your code and it works like a charm. Only thing I noticed was a lag on my (external hosted) webserver which confused me a bit (no error and no output). I had to wait.

ioseb Over a year ago

lag depends on initial input file and how many rows PHP needs to process. I do not know how large your input file is, though if there are several thousands of rows it can take some time to process. I/O is expensive.

user1451304 Over a year ago

Oops, script runs on a 5 minute base and adds the data from the days before for the second and the third and the fourth etc. time to the same file. On the day itself it's OK.

user1451304 · Accepted Answer · 2012-06-17 07:03:51Z

Combining the answers I figured this out: It works, but sometimes when running the script again I miss one line in the split files and I can't figure out why.

<?php  
$fh = fopen('2012_06.csv.ptr', 'r');  
$pos1 = fread( $fh, 8192 );  
//echo $pos1 , 'a',"<BR>";
fclose ($fh);
if ($fp = fopen('2012_06.csv', 'r'))
fseek ($fp , $pos1 );
{
$line_number = 0;
while ($line = fgetcsv($fp, 0, ';')) {
if ($line_number++ == 0) {
  continue;
}
$date = explode(' ', $line[0]);
$file = $date[0] .'.log';

file_put_contents(
  'Monthly/'. $file, 
  implode(';', $line) ."\n",
  FILE_APPEND
);
}
$pos2 = ftell ($fp);
//echo $pos2;
fclose($fp);
}
$fh = fopen('2012_06.csv.ptr', 'w');
fwrite ($fh, $pos2);
fclose ($fh);
?>

Collectives™ on Stack Overflow

PHP, process and split CSV-file

4 Answers 4

1 Comment

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related