8

A csv file contains a '\' character in one of its record for which i am not able to get the exact number of key value pair in the array.

I am using PHP fgetcsv function as follows-

 while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
   ....
 }

I have also tried escaping the characters as follows.

 while (($data = fgetcsv($handle, 1000, ",",'"',"\\")) !== FALSE) {
    ....
 }

But it did not work.What might be the possible solution?

Sitansu
  • 861
  • 2
  • 14
  • 24
  • Have you tried escaping the '\' in the CSV file itself. I always make sure that anything I save to a flatfile in CSV format is run through `addslashes()` to avoid this problem – David Barker Nov 16 '11 at 11:04
  • I just ran a few tests locally. `fgetcsv()` will automatically escape characters when it reads each line without you setting the escape string as default is backslash. I don't think that is your problem. Are you using 1000 as an arbitrary length in your script? – David Barker Nov 16 '11 at 12:12
  • 2
    escape do not work when the backslash is at the end of a cell in .csv file. – Sitansu Dec 06 '11 at 04:09
  • This question neither provides sample data, nor explains what problems are encountered with the code given. – miken32 Dec 07 '22 at 18:27

7 Answers7

13

I had the same problem, and solve it too easy. If your string enclosure charater is '"' ( double quotes ), and you just don't want to consider the backslash as a escape character, just inform the double quote twice, as the string enclusure and as a escape character.

    $data = fgetcsv($handle, 8192, ';' , '"' , '"'  );
siga0984
  • 233
  • 2
  • 6
5
$handle = @fopen($filename, "r");
if ($handle) {
    while (($buffer = fgets($handle, 4096)) !== false) {
        $buffer = preg_replace('/(\\\",)/','\\ ",',$buffer);
        $buffer = preg_replace('/(\\\"("?),)/',' ',$buffer);
        $data[] = str_getcsv($buffer);
    }
    if (!feof($handle)) {
        echo "Error: unexpected fgets() fail\n";
    }
    fclose($handle);
}
Sitansu
  • 861
  • 2
  • 14
  • 24
3

You may need to fix the file manually. If the file only has one slash, that's the quickest thing.

Many programs do not export to csv format properly.

If this is something that is going to occur a lot, you may want to fix the source or correct the information as you are importing it.

evan
  • 12,307
  • 7
  • 37
  • 51
  • As the csv file is created in some remote server and i am importing using ftp and its a cron job also it contains a huge number of records for which i cannot edit it manually.is there any other possible solution? – Sitansu Nov 16 '11 at 11:11
  • 1
    Fix the problem programmatically. Use `fgets()`, fix the broken things, then run the fixed string through `str_getcsv()` http://www.php.net/manual/en/function.str-getcsv.php – evan Nov 16 '11 at 16:30
  • Thanks Evan.This works.I have putted this in my answer section. – Sitansu Dec 06 '11 at 06:51
2

I also got the same problem, I have solved it in this way.

 while (($data = fgetcsv($file, 0, "\t", '"', "\\")) !== FALSE) {
    $val_arr = explode(",",$data[0]);   
    //here, you can get each column values from $val_arr    
   ....
 }
user2905792
  • 121
  • 1
  • 1
  • 7
1

I don't mean to be necro on this one and I don't have enough clout to reply to your answer, but this line

$data[] = str_getcsv($buffer);

should read

$data = str_getcsv($buffer);

This worked for me.

Moebius
  • 700
  • 7
  • 25
0

Hello I had a similar pb when having \"" in my .csv it would strip some \" so based on what Sitansu said above I did:

if (($handle = fopen($input_csv, "r")) !== FALSE)
{
    while (($buffer = fgets($handle)) !== false)
    {
        $buffer = str_replace('\""', '\\"', $buffer);
        $row = str_getcsv($buffer);

        // Do stuff on cells.
    }

    ...
}
antoni
  • 5,001
  • 1
  • 35
  • 44
0

None of the other answers worked for me. Using fgets() function already resulted trimmed data in the buffer variable and made pointless to call any replace.

I have found the working solution here: https://stackoverflow.com/a/46342634/5356216

Code:

$handle = fopen($filepath, "r");
if ($handle === false) {
    //throw new someException('fopen error');
}

$rowNumber = 1;
while (($data = fgetcsv($handle, 0, ',', '"', "\0")) !== false) {
    yield $rowNumber => $data;
    $rowNumber++;
}
fclose($handle);

Data:

"id";"lastadded";"lastupdated";"aaa01";"aaa02";"aaa03";"status";"type";"aaa04";"aaa05";"aaa06";"aaa07";"aaa08";"aaa09";"aaa10";"printingallowed"
092916474;"2015-09-15 12:04:39.777";"2015-09-15 12:04:39.777";"";"";"Aaa vom 15.09.2015 11:43:57: Fusce non lectus sed sem varius sodales.";"";"Aaa_link";750265913;;"";"";"2015-09-15 12:04:39.777";;"Aaa vom 15.09.2015: Ut a urna eget mi maximus fringilla.";t
923789309;"2020-06-15 16:23:11.529";"2020-06-15 16:23:11.529";"";"";"Sehr geehrte xxxx yyyy,
In posuere elit eu orci malesuada fringilla. Vestibulum a massa ac libero vehicula rutrum. Donec tincidunt felis libero, non maximus libero laoreet ut?
Vivamus sed sem eu lacus consequat facilisis. Aenean nulla arcu, consectetur ut fringilla et, pharetra in purus.
Nam felis ante, \""win-win-Situation\"" volutpat non est at, tincidunt rhoncus libero.
Morbi dapibus erat rhoncus, fermentum metus id, mattis elit.
Etiam accumsan placerat arcu, et venenatis risus dignissim quis.

mit freundlichen Grüßen
Wxxxxx Wyyyyy
Zzzzzzzzzzzz
";"";"WwwAaa";750265913;;"";"";"2020-06-15 16:23:11.53";;"";t

With the "\0" the code results 2 records as it is expected.

Array
(
    [0] => 092916474
    [1] => 2015-09-15 12:04:39.777
    [2] => 2015-09-15 12:04:39.777
    [3] => 
    [4] => 
    [5] => Aaa vom 15.09.2015 11:43:57: Fusce non lectus sed sem varius sodales.
    [6] => 
    [7] => Aaa_link
    [8] => 750265913
    [9] => 
    [10] => 
    [11] => 
    [12] => 2015-09-15 12:04:39.777
    [13] => 
    [14] => Aaa vom 15.09.2015: Ut a urna eget mi maximus fringilla.
    [15] => t
)

Array
(
    [0] => 923789309
    [1] => 2020-06-15 16:23:11.529
    [2] => 2020-06-15 16:23:11.529
    [3] => 
    [4] => 
    [5] => Sehr geehrte xxxx yyyy,
In posuere elit eu orci malesuada fringilla. Vestibulum a massa ac libero vehicula rutrum. Donec tincidunt felis libero, non maximus libero laoreet ut?
Vivamus sed sem eu lacus consequat facilisis. Aenean nulla arcu, consectetur ut fringilla et, pharetra in purus.
Nam felis ante, \"win-win-Situation\" volutpat non est at, tincidunt rhoncus libero.
Morbi dapibus erat rhoncus, fermentum metus id, mattis elit.
Etiam accumsan placerat arcu, et venenatis risus dignissim quis.

mit freundlichen Grüßen
Wxxxxx Wyyyyy
Zzzzzzzzzzzz

    [6] => 
    [7] => WwwAaa
    [8] => 750265913
    [9] => 
    [10] => 
    [11] => 
    [12] => 2020-06-15 16:23:11.53
    [13] => 
    [14] => 
    [15] => t
)

Without it it results 9 records:

Array
(
    [0] => 092916474
    [1] => 2015-09-15 12:04:39.777
    [2] => 2015-09-15 12:04:39.777
    [3] => 
    [4] => 
    [5] => Aaa vom 15.09.2015 11:43:57: Fusce non lectus sed sem varius sodales.
    [6] => 
    [7] => Aaa_link
    [8] => 750265913
    [9] => 
    [10] => 
    [11] => 
    [12] => 2015-09-15 12:04:39.777
    [13] => 
    [14] => Aaa vom 15.09.2015: Ut a urna eget mi maximus fringilla.
    [15] => t
)

Array
(
    [0] => 923789309
    [1] => 2020-06-15 16:23:11.529
    [2] => 2020-06-15 16:23:11.529
    [3] => 
    [4] => 
    [5] => Sehr geehrte xxxx yyyy,
In posuere elit eu orci malesuada fringilla. Vestibulum a massa ac libero vehicula rutrum. Donec tincidunt felis libero, non maximus libero laoreet ut?
Vivamus sed sem eu lacus consequat facilisis. Aenean nulla arcu, consectetur ut fringilla et, pharetra in purus.
Nam felis ante, \"win-win-Situation\"" volutpat non est at, tincidunt rhoncus libero.
)


Array
(
    [0] => Morbi dapibus erat rhoncus, fermentum metus id, mattis elit.
)


Array
(
    [0] => Etiam accumsan placerat arcu, et venenatis risus dignissim quis.
)


Array
(
    [0] => 
)

Array
(
    [0] => mit freundlichen Grüßen
)

Array
(
    [0] => Wxxxxx Wyyyyy
)

Array
(
    [0] => Zzzzzzzzzzzz
)

Array
(
    [0] => ;";WwwAaa"
    [1] => 750265913
    [2] => 
    [3] => 
    [4] => 
    [5] => 2020-06-15 16:23:11.53
    [6] => 
    [7] => 
    [8] => t
)
Eric Aya
  • 69,473
  • 35
  • 181
  • 253
Zoltán Süle
  • 1,482
  • 19
  • 26