4

Is it possible to remove all blank ROWS from a CSV file?

I'm trying to count all rows from a CSV file but would like to exclude lines that doesn't contain a value on a specific column or the whole row.

This is what I'm using to count the rows as of the moment.

$import = file($target_path, FILE_SKIP_EMPTY_LINES);
$num_rows = count($import);
echo $num_rows;

sample:

Jun,Bronse,137 Raven,Princeton,TX,75407,2147088671,Nell@Gmail.Com,1990,CHEVROLET,K1500,,
,,,,,,,,,,,,
,,,,,,,,,,,,
,,,,,,,,,,,,
Nella,Brown,111 Venna St,Princeton,TX,75407,2147177671,lakb@Gmail.Com,1993,CHEVROLET,K1500,,
Jun,Bronse,137 Raven,Princeton,TX,75407,2147088671,Nell@Gmail.Com,1990,CHEVROLET,K1500,,
,,,,,,,,,,,,
Jun,Bronse,137 Raven,Princeton,TX,75407,2147088671,Nell@Gmail.Com,1990,CHEVROLET,K1500,,
telexper
  • 2,381
  • 8
  • 37
  • 66

5 Answers5

12
$lines = file("test.csv", FILE_SKIP_EMPTY_LINES | FILE_IGNORE_NEW_LINES);
$num_rows = count($lines);
foreach ($lines as $line) {
    $csv = str_getcsv($line);
    if (empty($csv[SPECIFIC_COLUMN])) {
        $num_rows--;
    }
}

If you don't want to check a specific column, but just filter out rows where all columns are empty, change it to:

    if (count(array_filter($csv)) == 0) {
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • +1 for `str_getcsv()`, never even heard of it before. I'm assuming the advantage of that function compared to `explode()` is that it would handle strings with commas in them correctly? (e.g. `1,2,"Here's a string, check it out",3,4`) – sjagr Jan 31 '14 at 20:24
  • how about just getting a blank row not a specific column? – telexper Jan 31 '14 at 20:25
  • @telexper `!array_filter($csv)` would return true if all values of the array are empty, but could become time consuming (better to just use a simple `foreach`)... That part is easy – sjagr Jan 31 '14 at 20:27
  • 1
    If all the columns are blank, the specific column will be blank, so that check will do it. – Barmar Jan 31 '14 at 20:32
  • @Barmar but not exclusively for an entirely blank row – sjagr Jan 31 '14 at 20:34
  • Can't use function return value in write context in /.../... on line "if (empty(array_filter($csv)))" – telexper Jan 31 '14 at 20:43
  • @ Barmar I have a similiar question: [Link](http://stackoverflow.com/questions/24703373/) – Grischa Jul 12 '14 at 11:08
3

For empty rows:

$lines = file("test.csv", FILE_SKIP_EMPTY_LINES | FILE_IGNORE_NEW_LINES);
$num_rows = count($lines);
foreach ($lines as $line) {
    $csv = str_getcsv($line);
    if (!array_filter($csv)) {
        $num_rows--;
    }
}
sjagr
  • 15,983
  • 5
  • 40
  • 67
0
$filename = "test.csv";

if (($fp = fopen($filename, "r")) !== FALSE) { 
    $rows = explode("\n", $fp);
    $csv = "";

    foreach($rows as $r) {
        if(str_replace(array(" ", ","), "", $r) != "")
            $csv .= $r."\n";
    }

    file_put_contents($filename, $csv);;
}
  • Empty row could be stored in the CSV as `,,,,,,,,` (for example.) Your code would produce inaccurate results. Not to mention @telexper has already used `FILE_SKIP_EMPTY_LINES` – sjagr Jan 31 '14 at 20:18
0

Basically what Barmar already posted, but if you need to process (many) large* files, I'd recommend to give rtrim (or ltrim) a try, since usually those are a bit faster for a task like yours:

$lines = file("test.csv", FILE_SKIP_EMPTY_LINES | FILE_IGNORE_NEW_LINES);
$num_rows = count($lines);
foreach ($lines as $line) {
    if (!rtrim($line, ',')) {
        $num_rows--;
    }
}

*By large I mean that large that it's really worth it. Don't waste your time doing micro-optimizations.

Jürgen Thelen
  • 12,745
  • 7
  • 52
  • 71
-5

use notepad++ to open .csv file. Then search for ^\*s goto find and replace and click replace all button. This should solve your problem.

painteR
  • 1