Using fgetcsv to replace commans within numbers

Question

I have the following CSV data:

"Symbol","Name","Trade Volume","Trade Value","UIN Settlement Volume ","UIN Settlement Value ","UIN Percentage Volume","UIN Percentage Value "
"786","786 INVESTMENT LIMITED","500.00"," 2,325.00 ","500.00"," 2,325.00 ","100.00 ","100.00 "
"ABL","ALLIED BANK LIMITED","15,501.00"," 981,819.00 ","10,001.00"," 629,379.00 ","64.52 ","64.10 "
"ABOT","ABBOTT LABORATORIES (PAKISTAN) LIMITED","4,043.00"," 1,730,197.34 ","3,031.00"," 1,299,214.65 ","74.97 ","75.09 "
"ACPL","ATTOCK CEMENT PAKISTAN LIMITED","40,231.00"," 2,345,598.00 ","36,131.00"," 2,107,058.00 ","89.81 ","89.83 "
"ADAMS","ADAM SUGAR MILLS LIMITED","4,091.00"," 107,544.61 ","3,091.00"," 80,669.61 ","75.56 ","75.01 "

Before splitting the w.r.t comma by using fgetcsv, I want to remove command from numbers. How can I do it?

My current code looks like the below:

while (($row = fgetcsv($file, 1000, ",")) !== FALSE) {
            print("<br><b>Before</b><br>");
            print_r($row);
            $row = preg_replace('/([0-9]+),([0-9]+)/', '$1$2', $row);
            // $row = preg_replace('/"([0-9]+),([0-9]+)(.*?)"/', '"$1$2$3"', $row);
            print("<br><b>After</b><br>");
            print_r($row);
            $Symbol = $row[0];

Why would you do this before fgetcsv()? fgetcsv() will parse the values correctly since they're quoted, and stripping the the commas after is much easier (just str_replace()) and less likely to break. — Alex Howansky, Feb 13 '23 at 16:58
@AlexHowansky The value `2,325.00` is being stored as `2` only. — Volatil3, Feb 13 '23 at 17:02
@AlexHowansky I mean without using preg_replace, the above result comes, if I use preg_replace only first value is valid else not — Volatil3, Feb 13 '23 at 17:03
You don't need to strip these commas, as fgetcsv() will not break on commas inside quoted strings. Post the output of `print_r($row);` before your preg_replace() call and you'll see `2,325.00` inside the value there. — Alex Howansky, Feb 13 '23 at 17:05
@AlexHowansky I got the issue, it is mysql `double` datatype that is truncating it — Volatil3, Feb 13 '23 at 17:10

score 0 · Answer 1 · answered Feb 13 '23 at 17:46

Do you want to remove extra commas and spaces from numbers in all cells?

The result of fgetcsv is an array, not a string. You need to process each item

while (($row = fgetcsv($file, 1000, ",", "\"")) !== FALSE) {
    echo "<br><b>Before</b><br>" . PHP_EOL;
    print_r($row);
    $row = array_map(function($el) {
            if (preg_match("/^[. ,0-9]+$/", $el)) {
                $el = preg_replace("/,/", "", trim($el));
           }
           return $el;
        },
        $row);
    echo "<br><b>After</b><br>" . PHP_EOL;
    print_r($row);
    $Symbol = $row[0];
}

Using fgetcsv to replace commans within numbers

1 Answers1