How do I read from one file and write to another ?

[jeddiki]

Hello,

I am not very experienced with file handling, so I am
asking for a little help with this.

I have a file called "geotext_1a.csv"
and it is a csv file looking like this:

"2.6.190.56","2.6.190.63","33996344","33996351","G B","United Kingdom"
"3.0.0.0","4.17.135.31","50331648","68257567","US" ,"United States"
"4.17.135.32","4.17.135.63","68257568","68257599", "CA","Canada"


I want to take some data from it and write a file called geo_01.txt

and will look like this :

33996344,33996351,United Kingdom
50331648,68257567,United States
68257568,68257599,Canada


Now I have written some code but I used
the file function to put the contents into an array.

As the file is a few megabytes big, this has caused memory
failure problems.

This is what I have:

<?php 
$file_array = file('geotext_1a.csv');
$data = array();
$i = 0;
 
foreach ($file_array as $row)
 {
   $row = str_replace('"','',$row); // get rid of double quotes
     $cells = explode(",",$row);
     $data[$i]['trash1'] = $cells[0];
   $data[$i]['trash2'] = $cells[1];
   $data[$i]['ip1'] = $cells[2];
   $data[$i]['ip2'] = $cells[3];
   $data[$i]['trash3'] = $cells[4];
   $data[$i]['country'] = $cells[5];
     $i++;
  }
 
echo '<pre>';
print_r($data);
echo '</pre>'; 
 
?>  

So my question is

Does processing the file as a stream use
less memory ?

i.e. $handle = fopen("$file","r")

Because I am not familiar with using this stuff
I don't know how to do this

Any guidance much appreciated

Many thanks

[Cmaza]

Which stage of the code execution is your memory problem occurring at?

[jeddiki]

Actually, when I run this , its seems to be working until it gets to a
line and this fatal error occurs:

Fatal error: Allowed memory size of 16777216 bytes exhausted (tried to allocate 13 bytes) in /home/ew78gt/public_html/sys/convert.php on line 9

[pezhvak]

Try this:

<?php
function read_csv_file($file)
{
    $fp = fopen($file, "r");
    $stream = fread($fp, filesize($file));
    $stream = str_replace("\"","",$stream);
    $rows = explode("\n", $stream);
    $data = array();
    for($i = 0; $i<=count($rows)-1; $i++)
    {
        $cells = explode(",",$rows[$i]);
        $data[$i] = array("trash1" => $cells[0], "trash2" => $cells[1], "ip1" => $cells[2], "ip2" => $cells[3], "trash3" => $cells[4], "country" => $cells[5]);
    }
    return $data;
}
 
echo '<pre>';
print_r(read_csv_file("test.csv"));
echo '</pre>'; 
?>  

hope this helps you..

[jeddiki]

Thanks for your suggestion.
I ran it on my server and I got this output:


Notice: Undefined offset: 1 in /home/ew78gt/public_html/sys/con_01.php on line 12



Notice: Undefined offset: 2 in /home/ew78gt/public_html/sys/con_01.php on line 12



Notice: Undefined offset: 3 in /home/ew78gt/public_html/sys/con_01.php on line 12



Notice: Undefined offset: 4 in /home/ew78gt/public_html/sys/con_01.php on line 12



Notice: Undefined offset: 5 in /home/ew78gt/public_html/sys/con_01.php on line 12



Fatal error: Allowed memory size of 16777216 bytes exhausted (tried to allocate 15 bytes) in /home/ew78gt/public_html/sys/con_01.php on line 11

[jeddiki]

Instead of putting the whole file into memory,
can I not work on the file just one line at a time ?

ı.e.

1) open the file to read,
2) open file to write,

3) read first row
4) take out double quotes
5) explode row into 6 variables
6) write desired 3 variables to row in new file

7) loop and read next row until end of file.
8) close files


So:

$file1 = "geotext_1a.csv";
$file2 = "geo_new".txt";

$fp1 = fopen($file1, "r");
$fp2 = fopen($file2, "w");

while not-end-of-file1
3) read first row
$row = str_replace('"','',$row);
$cells = explode(",",$row);
write to file2 on first row $cells[2],$cells[3], $cells[5]
}

close files

If someone can help me fill in the bits
that I am missing shouldn't this thenl work ok without
memory problems ?

[pezhvak]

it's possible, just upload the large file (CSV) and give me the link of uploaded file, i will make the code for you

[jeddiki]

The file is here:

http://www.expert-world.net/sys/geotext_1a.csv

feel free to download it.

[pezhvak]

Ok, i downloaded your file and test it with the previous script that i wrote for you and it worked correctly, it seems that you have something wrong with writing columns in your new file,
i changed the script and added another function that works great with your CSV file (i have already test that)

<?php
function read_csv_file($file, $newfile)
{
    $fp = fopen($file, "r");
    $stream = fread($fp, filesize($file));
    $stream = str_replace("\"","",$stream);
    $rows = explode("\n", $stream);
    $data = array();
    for($i = 0; $i<=count($rows)-1; $i++)
    {
        if(strpos($rows[$i], ","))
        {
            $cells = explode(",",$rows[$i]);
            if(count($cells>=6))
                write_to_file($newfile, $cells[0], $cells[1], $cells[2]); // You can replace your own columns here (To write to the new file)
        }
    }
}
 
function write_to_file($file, $col1, $col2, $col3)
{
    $stream = $col1.",".$col2.",".$col3;
    if(strlen($stream)>5)
    {
        $fileExists = file_exists($file);
        $fp = fopen($file, "a");
        if($fileExists) $stream = "\n" . $stream;
        fwrite($fp, $stream, strlen($stream));
        fclose($fp);
    }
}
 
// export columns from the file
read_csv_file("sourceFile.csv", "newFile.txt");
?> 
 

remember 1 thing:
when i checked your CSV file, there was some empty row, that's why you face to those warnings (Notice: Undefined offset: 1 in /home/ew78gt ..)
you don't need to remove the empty lines, the script will ignore them.

if you had any questions, feel free to ask me ;)

[jeddiki]

Thanks very much,

I really appreciate the time.

While you doing that I have been reading the manual and
I found these two commands:
fgetcsv() and fputcsv()

So I have written this:

But do not know if it will work.
May be it won't ignore those problem lines ?

What do you think ?

<?php
$row = 1;
$handle1 = fopen("geotext_1a.csv", "r");
$handle2 = fopen("geo_sm_1a.csv", "w");
while (($data = fgetcsv($handle1,  ",")) !== FALSE) {
    $row = str_replace('"','',$data );
    $cells = explode(",",$row);
        $new_arr = array('$cells[2]', '$cells[3]', '$cells[5]'); 
 
    fputcsv($handle2,$new_arr);
   }
fclose($handle1);
fclose($handle2);
?>
 

[pezhvak]

alright, i tried to test your script, but it doesn't work for me
if it works for you, i just added few lines to make sure your script will ignore the empty lines:

<?php
$row = 1;
$handle1 = fopen("geotext_1a.csv", "r");
$handle2 = fopen("geo_sm_1a.csv", "w");
while (($data = fgetcsv($handle1,  ",")) !== FALSE)
{
    if(strlen($data)>5)
    {
        $row = str_replace('"','',$data);
        $cells = explode(",",$row);
        if(count($cells) >= 6)
        {
            $new_arr = array($cells[2], $cells[3], $cells[5]);     
            fputcsv($handle2,$new_arr);
        }
    }
}
fclose($handle1);
fclose($handle2);
?> 
 

remember this:
as far as i know you can use variables in double quotations (") like "$str" but about the single quotation ('), it will ignore the variable, it will write exact string like "$str" not the value of that..

so i changed [array('$cells[2]', '$cells[3]', '$cells[5]')] to [array($cells[2], $cells[3], $cells[5])]

fgetcsv() is exactly like fgets(), but it will explode them by given character
i'm still recommend you to use the previous script that i wrote for you, because this script didn't work for me, i dont know about you,

if previous script didn't work for you, tell me to re write the script with this functions ;)

[Markus]

You can read a file, line by line, by using the file() function - see php.net/file

[pezhvak]

@Markus
We know about that, he have problem with writing some columns of the main file in a new file

[jeddiki]

Hi pezhvak,

Thanks again,

I was hoping that you could get the script that I wrote to
work. - You seem to know more about this stuff than me.

The reason I liked my script is because it is less code.

What do you you think I did wrong?

This is my first time trying to change a file like this !!

I will use your script, (thanks)
but I think you how it is... I would just
like to know what is wrong with the scrpt I wrote !!

Thanks for your help.

[pezhvak]

as your request i checked your script again,

Your problem was:

1- in the [while] loop:
all of the conditions will be converted to true or false, so we don't need !==false in our conditions (this will not effect your script, i said that just to know ;) )

2- actually in this part ($data = fgetcsv($handle1, ",")), $data will convert to an array and you cannot replace double quotations like you wrote:

$row = str_replace('"','',$data);  // it's completely wrong

3- as i said, fgetcsv will automatically split your string by given character.
so when you use $data = fgetcsv($handle1, "," ) in the [while] loop, it means that while until EOF (eof in the given file handle)

fgetcsv will read your CSV file line by line and it will split the line by given character (argument 2) and will return an array

so:

$cells = explode(",",$row); // this is completely wrong too!

4- why you wrote this line ($row = 1;)?? ;)


fixed code:

<?php
$handle1 = fopen("geotext_1a.csv", "r");
$handle2 = fopen("geo_sm_1a.csv", "w");
while ($data = fgetcsv($handle1,  ","))
{
    $row = $data[2]."[,]".$data[3]."[,]".trim($data[5]);
    if(strlen($row)>10)
    {
        $row = str_replace('"','',$row);
        $row = explode("[,]", $row);
        fputcsv($handle2, $row);
    }
}
fclose($handle1);
fclose($handle2);
?> 
 

remember:
[fputcsv] will add double quotations automatically for those indexes of given array that contains [space] character, so if you dont want it, you must use another function to write the line to your file

so... it was all about your script,

i agree with you, this script is much simpler that the one i gave to you, it will work great, exactly like the script that i sent before...

UPDATE:
i added few lines to the script, because i saw some of the countries like my country have [,] in their name, script will split the string by [,] and return an array with 4 indexes, and it's wrong

ex: 209868800,209869055,Iran, Islamic Republic of
NOTE: you don't really need to save the name like this!! (ignore the "Islamic Republic of") "Iran" is enough!!:D

alright another change that i made is trim(),
i add this function because i saw some columns (country column) in the final file that contains [space] before their name... so i add trim() function to remove those :)

NOTE: trim() will remove space (space is default character) from begin and end of the string;

[jeddiki]

Thanks VERY much.

I appreciate your explanation,
and using my script as a basis.

A combined effort to end up with a nice short
script :)

I ran it and it created the new file. :) :)

The output does give quite a few notices.

Is this because of those commas in the country description ?
Iran, Congo and Tanzania all have them.

Is there an easy way to truncate those country names at the comma ?

Here are the notices I get:
( these repeat many times)

Notice: Undefined offset: 2 in /home/ew78gt/public_html/sys/con_01.php on line 6

Notice: Undefined offset: 3 in /home/ew78gt/public_html/sys/con_01.php on line 6

Notice: Undefined offset: 5 in /home/ew78gt/public_html/sys/con_01.php on line 6

Notice: Undefined offset: 2 in /home/ew78gt/public_html/sys/con_01.php on line 6

Notice: Undefined offset: 3 in /home/ew78gt/public_html/sys/con_01.php on line 6

Notice: Undefined offset: 5 in /home/ew78gt/public_html/sys/con_01.php on line 6

Can we get rid of these notices as well as the extended country name parts ?

Thanks again.

[pezhvak]

Aha! i think i know what is the problem!!
Do you remember the empty lines in your CSV file?
that's the problem, as i said, fgetcsv will explode the line by ',' character.
in other hand there is no ',' character in the empty lines, so we don't have $data[1-5] indexes, so it will show those notices,
So, i update the script again:

<?php
$handle1 = fopen("geotext_1a.csv", "r");
$handle2 = fopen("geo_sm_1a.csv", "w");
while ($data = fgetcsv($handle1,  ","))
{
    if(count($data) >= 5)
    {
        $row = $data[2]."[,]".$data[3]."[,]".trim($data[5]);
        if(strlen($row)>10)
        {
            $row = str_replace('"','',$row);
            $row = explode("[,]", $row);
            fputcsv($handle2, $row);
        }
    }
}
fclose($handle1);
fclose($handle2);
?> 
 

i add this part to the script:

if(count($data) >= 5) //so, we will check if there are more than 4 indexes, if yes, we will write it to second CSV file
 

hope this resolve your problem ;)

[jeddiki]

You are one smart cookie :)

ran it again and no notices.

so to get trim off these extra bits in the
country name:

would I insert this:

$data[5] =strstr($data[5], ',', true);


so:

<?php
$handle1 = fopen("geotext_1a.csv", "r");
$handle2 = fopen("geo_sm_1a.csv", "w");
while ($data = fgetcsv($handle1, ",")) {
if(count($data) >=5) {
$data[5] =strstr($data[5], ',', true);
$row = $data[2]."[,]".$data[3]."[,]".trim($data[5]);
if(strlen($row)>10) {
$row = str_replace('"','',$row);
$row = explode("[,]", $row);
fputcsv($handle2, $row);
}
}
}
fclose($handle1);
fclose($handle2);
?>

Well I tried it and I get :

Warning: Wrong parameter count for strstr() in /home/ew78gt/public_html/sys/con_01.php on line 6

This might be because I have php 5.2 on my server
and not 5.3 as required.

How else can I cut everything after a comma in
$data[5] - be good to get rid of the comma as well :)

[pezhvak]

Alright, here you are:

<?php
$handle1 = fopen("geotext_1a.csv", "r");
$handle2 = fopen("geo_sm_1a.csv", "w");
while ($data = fgetcsv($handle1,  ","))
{
    if(count($data) >= 5)
    {
        $country = strpos($data[5], ",") ? substr($data[5],0,strpos($data[5], ",")) : $data[5];
        $row = $data[2]."[,]".$data[3]."[,]".trim($country);
        if(strlen($row)>10)
        {
            $row = str_replace('"','',$row);
            $row = explode("[,]", $row);
            fputcsv($handle2, $row);
        }
    }
}
fclose($handle1);
fclose($handle2);
?> 
 

this line will remove everything after comma in country column:

$country = strpos($data[5], ",") ? substr($data[5],0,strpos($data[5], ",")) : $data[5];
 

[jeddiki]

I tried what you suggested, but it did not work.

Then, I rewrote it in a style I understand:

if (strpos($data[5], ",")) { 
 $country = substr($data[5],0,strpos($data[5], ","));
 }
else {
  $country = $data[5];
}
$row = $data[2]."[,]".$data[3]."[,]".trim($country);  

I think it is just the long-nad version of what you wrote.

It looks like it should work to me - but it does not.


SORRY

MY Mistake - it works fine now :)

Thanks again

[pezhvak]

Your welcome ;)

if you had any questions, feel free to ask me :)

[Farhat]

Hello,

I have following code to read csv file, do some manipulations(i.e. add new records), and write it to new csv file. When i execute it on small files it works perfectly but when i use it for bigger files like 9MB following error occurs:

Fatal error: Allowed memory size of 104857600 bytes exhausted (tried to allocate 35 bytes) in D:\projects\wamp\www\data_handling\data_handling.p hp on line 16.

please help me out solving thios problem

<?php
set_time_limit(600);
 
if($_FILES)
{
    $filename    =    $_FILES['file']['name'];
 
    move_uploaded_file($_FILES['file']['tmp_name'], $_FILES['file']['name']);
 
    $handle = fopen("$filename", 'r');
    $records    =    array();
    $i = 0;
    $csv_output    =    '';
    while(($data = fgetcsv($handle, 4096, ",")) !== false)
    {
        $records[$i]    =    $data;
        $i++;
    }
 
    fclose($handle);
 
    for($j=0; $j<$i; $j++)
    {
        for($k=0; $k<=26; $k++)
        {
            if($k == 7)
                continue;
            else if($j==0)    
                $csv_output .= $records[$j][$k].",";
 
            if($j>0)
            {
                $start    =     $records[$j][6];
                $end    =    $records[$j][7];
 
                for($l = $start; $l<$end; $l++)
                {
                    for($k=0; $k<=26; $k++)
                    {
                        if($k == 7)
                            continue;
                        if($j>0 && $k == 6)
                        {
                            $csv_output .= $l.",";
                        }
                        else
                        {
                            $csv_output .= $records[$j][$k].",";
                        }
                    }
 
                    $csv_output .= "\n";
                }
            }
        }
        if($j == 0)
        $csv_output .= "\n";
    }
    //print $csv_output;
    $export_file = "output_".$filename;
    $fp = fopen($export_file, "wb");
    if (!is_resource($fp))
    {
        die("Cannot open $export_file");
    }
 
    fwrite($fp, $csv_output);
    fclose($fp);
 
    exit;
}
?>

[pezhvak]

it's all about your PHP.ini file, you have to change your memory_limit & post_max_size and also upload_max_filesize that is required to be changed, in default the value of upload_max_filesize is 5mb, change it to 2000mb or more... so your script will work again

[Farhat]

Thanks a lot. Its done by increasing memory size to 300M.
Thank you so much.