Remove line(s) from a CSV file and generate a new file automatically

Question

I am trying to manipulate an existing .csv file with PHP! I looked through most of the StackOverflow questions and I found the answer from user "Myke Black" which fits best for what I need.

I set $id=2, therefore I expect, that the code affects row 2 from my .csv file and a new generated .csv file without row 2 will be generated.

Here is my code:

$id=2;
$fptemp = fopen('files/FL_insurance_small-temp.csv', "a+");
if (($handle = fopen('files/FL_insurance_small.csv', "r")) !== FALSE) {
    while (($id= fgetcsv($handle)) !== FALSE) {
        if ($id != $data[0]) {
            $list = array($data);
            fputcsv($fptemp, $list);
        }
    }
    fclose($handle);
    fclose($fptemp);

    rename('files/FL_insurance_small-temp.csv','files/output/FL_insurance_small_new.csv');
    echo "Row with ID: ".$id." successfully deleted!";
} else {
    print "There seems to be a problem.";
}

Everything seems to work fine cause a new file inside the output folder is generated. My problem is, that the new generated file is empty. Does anyone have an idea why? What am I missing?

Example data (.csv):

policyID,statecode,county,eq_site_limit,hu_site_limit,fl_site_limit,fr_site_limit,tiv_2011,tiv_2012,eq_site_deductible,hu_site_deductible,fl_site_deductible,fr_site_deductible,point_latitude,point_longitude,line,construction,point_granularity
119736,FL,"CLAY COUNTY",498960,498960,498960,498960,498960,792148.9,0,9979.2,0,0,30.102261,-81.711777,Residential,Masonry,1
448094,FL,"CLAY COUNTY",1322376.3,1322376.3,1322376.3,1322376.3,1322376.3,1438163.57,0,0,0,0,30.063936,-81.707664,Residential,Masonry,3
206893,FL,"CLAY COUNTY",190724.4,190724.4,190724.4,190724.4,190724.4,192476.78,0,0,0,0,30.089579,-81.700455,Residential,Wood,1
333743,FL,"CLAY COUNTY",0,79520.76,0,0,79520.76,86854.48,0,0,0,0,30.063236,-81.707703,Residential,Wood,3
172534,FL,"CLAY COUNTY",0,254281.5,0,254281.5,254281.5,246144.49,0,0,0,0,30.060614,-81.702675,Residential,Wood,1
785275,FL,"CLAY COUNTY",0,515035.62,0,0,515035.62,884419.17,0,0,0,0,30.063236,-81.707703,Residential,Masonry,3
995932,FL,"CLAY COUNTY",0,19260000,0,0,19260000,20610000,0,0,0,0,30.102226,-81.713882,Commercial,"Reinforced Concrete",1
223488,FL,"CLAY COUNTY",328500,328500,328500,328500,328500,348374.25,0,16425,0,0,30.102217,-81.707146,Residential,Wood,1
433512,FL,"CLAY COUNTY",315000,315000,315000,315000,315000,265821.57,0,15750,0,0,30.118774,-81.704613,Residential,Wood,1
142071,FL,"CLAY COUNTY",705600,705600,705600,705600,705600,1010842.56,14112,35280,0,0,30.100628,-81.703751,Residential,Masonry,1
253816,FL,"CLAY COUNTY",831498.3,831498.3,831498.3,831498.3,831498.3,1117791.48,0,0,0,0,30.10216,-81.719444,Residential,Masonry,1
894922,FL,"CLAY COUNTY",0,24059.09,0,0,24059.09,33952.19,0,0,0,0,30.095957,-81.695099,Residential,Wood,1
422834,FL,"CLAY COUNTY",0,48115.94,0,0,48115.94,66755.39,0,0,0,0,30.100073,-81.739822,Residential,Wood,1
582721,FL,"CLAY COUNTY",0,28869.12,0,0,28869.12,42826.99,0,0,0,0,30.09248,-81.725167,Residential,Wood,1

Thank you Patrick. That seems to be the problem, you are right. Can you tell me how I need to change the code so $data is defined? I am stucked on the code I posted. — Christoph C.
– Christoph C., Commented Oct 12, 2018 at 15:19
I can't tell you because I'm not sure what you're intending $data to be. Maybe you mean $data = fgetcsv($handle)? — Patrick Q
– Patrick Q, Commented Oct 12, 2018 at 15:21
Why develop an application in PHP to do a task like this? You really don't want to use flat files as a database. And if its a one-off, a text editor or a one-liner shell script would have been much faster and easier to debug. — symcbean
– symcbean, Commented Oct 12, 2018 at 15:39
@symcbean I have 500+ .csv files where the lines 3-8 should be removed. It is the same for every file. Therefore a simple php script should be the fastest way in my opinion. There may be better ways but PHP is actually the programming language I know. :) — Christoph C.
– Christoph C., Commented Oct 12, 2018 at 16:04

Nigel Ren · Accepted Answer · 2018-10-12 16:01:04Z

2

When reading the file - your overwriting $id which is the row your looking for. Instead your loop should assign the value to $data instead...

$rowcount = 1;
while ($data= fgetcsv($handle)) {
    if ($id != $rowcount++) {
        fputcsv($fptemp, $data);
    }
}

$rowCount is the row that is currently being read and so this is compared against each row as it reads the new line in.

This also removes the redundant assignment to $list.

To 'delete'(or ommit) a list of records...

$id= [2,5,7];
$rowcount = 1;
while ($data= fgetcsv($handle)) {
    if (!in_array($rowcount++, $id)) {   // Only write row if not in the list
        fputcsv($fptemp, $data);
    }
}

edited Oct 12, 2018 at 16:01

answered Oct 12, 2018 at 15:23

Nigel Ren

57.3k11 gold badges49 silver badges59 bronze badges

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

11 Comments

Christoph C. Over a year ago

thank you for your answer. Your updated code seems to work cause within the new created file, the data from the edited .csv file are inserted. Problem now is that the code makes no effect. I get the message "Row with ID: 2 successfully deleted!" but inside the new file, row 2 is still there. Any ideay why?

Patrick Q Over a year ago

@ChristophC. Please put the sample data directly into your question (not a link).

Christoph C. Over a year ago

oh okay. I do not want to remove the row with the specific ID. I want to remove the second row from the file. So $id should not be the ID, instead it should be the row from the file. If I enter $id=234 than the script should remove the line 234 from the .csv file. This is what I am looking for to do.

Christoph C. Over a year ago

amazing. Exactly what I was looking for. Thank you so much Nigel. I will mark your answer as the correct answer. Just one last question: Is it possible to delete more than one row at once? If so, can you tell me what my code should look like. For example: I want to delete line 3,4,5,6,7,8,9 from the .csv file. Do you have an idea?

Patrick Q Over a year ago

@ChristophC. Just a small point, you're not actually deleting those lines. You are omitting them from the content written to the new file.

|

Collectives™ on Stack Overflow

Remove line(s) from a CSV file and generate a new file automatically

1 Answer 1

11 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related