Retrieve multiple lines within same cell from CSV file

Question

I am trying to retrieve some data from CSV file. I used the following code to go through CSV:

//create array for csv file
$array_data = array();
$row = 0;
if (($handle = fopen("import/pdf_data.csv", "r")) !== FALSE) {
    // read the CSV from root folder "web/interval/import"
    while (($data = fgetcsv($handle, 0, ";")) !== FALSE) {
        if ($row > 0) {
            $array_data[$row] = $data;
        }
        $row++;
    }
    fclose($handle);
}

//loop through all the feature data array
foreach ($array_data as $entry_value => $array_column) {
    foreach( $array_column as $value )
    {       
       //split data
       list($col1,$col2,$col3) = explode( ',', $value );

       echo "Name: ".$col1.", Surname: ".$col2.", Text: ".$col3."<br/>";

When I print the columns... col1 and col2 are fine as they have only one single value in their cells. col3 may contain multiple lines. Let's say for example (see below one cell of col3):

col3
::::::::::::::
Text-a in line 1
Text-a in line 2
Text-a in line 3

If there are multiple lines within one cell then the CSV output will be like this: "Text-a in line 1Text-a in line 2Text-a in line 3"

and with the code I use above it prints the first line "Text-a in line 1 then in a new entry the second line etc etc.

What I want to achieve is the following format

echo "Name: ".$col1.", Surname: ".$col2.", Text: ".$col3."<br/>";

Which doesn't work with multiple lines as I get this:

Name: Test, Surname: Test2, Text: "Text-a in line 1
Name: , Surname: , Text: Text-a in line 2

Any suggestions would be much appreciated, thank you

You have a lot of undefined variables and code which you don't show us here! — Rizier123, Apr 09 '15 at 20:31
Yes a full line of CSV content please, from memory fgetcsv should deal with newlines as long as they are in a quoted cell — dsas, Apr 09 '15 at 22:21
ok, i just uploaded the content :) check ID: 91 or 58 for example. You can see that the line break moves the information to a new row with null ID. They should be in the same row. I tried to use str_replace and replace the /n or /r with space but still no luck — shieldcy, Apr 09 '15 at 22:28
In excel it is text 1, *then new line* and text2. When I convert it to CSV and open it with notepad it is in quotes... like "text1text2" with no space. You can see on the screenshot I post how it looks in PHP. — shieldcy, Apr 09 '15 at 22:35

astax · Accepted Answer · 2015-04-09T23:15:47.940

1

A CSV file may contain multi-line value in a cell. But it needs to be enclosed in quotes. Excel does this correctly - create a test file and save into CSV. PHP function fgetcsv also can read such files correctly.

Example file:

"col1","col2","col3"
"test1","test2.1
test2.2
test2.3","test3"

The second column in the second row contains multi-line value and this is a perfectly valid CSV file.

Here is the correct code:

$array_data = array();
$row = 0;
if (($handle = fopen("import/pdf_data.csv", "r")) !== FALSE) {
    // read the CSV from root folder "web/interval/import"
    while (($data = fgetcsv($handle, 0, ",")) !== FALSE) {
        if ($row > 0) {
            $array_data[$row] = $data;
        }
        $row++;
    }
    fclose($handle);
}

//loop through all the feature data array
foreach ($array_data as $row) {
   list($col1,$col2,$col3) = $row;

   echo "Name: ".$col1.", Surname: ".$col2.", Text: ".$col3."<br/>";
}

edited Apr 09 '15 at 23:15

answered Apr 09 '15 at 22:57

astax

1,769
1
14
23

yes that is correct. In CSV it looks ok with quotes etc. But when I echo it you can see from the screenshot that it goes into a new line. It prints a quote with the first text, then it goes to second line with second text. Or I tried to insert the data to DB. Again, it goes to a new row instead of one field. – shieldcy Apr 09 '15 at 23:03
This can be related to Excel using weird CSV format (it actually depends on locale). I noticed you specified semi-colon as fields separator and this is a sign that Excel has saved it without quotes. Can you save the file into CSV and then open in notepad. Does it have quotes? Please post it into your question. – astax Apr 09 '15 at 23:08
yes, in CSV it shows it as how it should be. with quotes and a comma as a separator. Now i'm just trying to find a way to remove those break lines and have the column3 with the rest columns in one row – shieldcy Apr 09 '15 at 23:12
I think I figured out what's wrong. Your CSV file actually uses commas, but you specified semi-colon. So fgetcsv just reads one line from the file instead of parsing the CSV format. That's why you had to put explode() afterwards. Change the fgetcsv call to use comma as a delimiter and remove the explode – astax Apr 09 '15 at 23:13
I think you did find the solution. Now it seems to work which is great! It took me so many hours. Thanks for the help – shieldcy Apr 09 '15 at 23:21

score 0 · Answer 2 · edited May 23 '17 at 11:50

0

I could advice you by another simple solution. It is using fgets() and explode()

$handle = fopen("inputfile.txt", "r");
$out = array();
if ($handle) {
    while (($line = fgets($handle)) !== false) {
        $arr = explode(',' $line);
        $out[] = trim($arr[2]); // Supposing you want to get the third cell.
    }

    fclose($handle);
} else {
    // error opening the file.
}
print_r($out);

Reference: How to read a file line by line in php

edited May 23 '17 at 11:50

Community

1
1

answered Apr 10 '15 at 00:02

SaidbakR

13,303
20
101
195

Using `explode()` is exactly what causes the problem, so must be avoided. It doesn't handle quoted and multi-line strings correctly. – astax Apr 10 '15 at 09:23
The only problem that may `explode` cause when the string itself contains the delimiter. i.e `,` – SaidbakR Apr 10 '15 at 09:41

Retrieve multiple lines within same cell from CSV file

2 Answers2