Issue on .csv files on PHPExcel

Question

I have a problem regarding PHPExcel when reading .csv files.

I wanted to get the values from the .csv file, but the problem is the data from a specific row considered as a single cell.

heres my code:

        include 'Classes/PHPExcel/IOFactory.php';
        $inputFileType = 'CSV';
        $inputFileName = $_FILES['file']['tmp_name'];
        $objReader = PHPExcel_IOFactory::createReader($inputFileType);
        $objPHPExcel = $objReader->load($inputFileName);

        $sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);

        $table = "<table border=1><tr><td>first</td><td>middle</td><td>last</td><td>email</td>";

        for ($x = 2; $x <= count($sheetData); $x++){
            foreach ($sheetData[$x] as $data){
                $first = $sheetData[$x]['A'];
                $middle = $sheetData[$x]['B'];
                $last = $sheetData[$x]['C'];
                $email = $sheetData[$x]['D'];   
            }

            $table .= "<tr><td>" . $first ."</td><td>" . $middle . "</td><td>" . $last . "</td><td>" . $email . "</td></tr>";

        }

        $table .= "</table>";

        echo $table;

It is working on .xls and .xlsx files and I get the desired output that I wanted.

Solution 1

This works fine:

$objReader->setDelimiter("\t"); 

However, when you are not 100% sure if its tab or comma separated there seems to be no way to add BOTH e.g. $objReader->setDelimiter("\t",); which is something that would be required. When you open Excel and go to Import CSV the actual on screen steps allow you to specify multiple delimiters which is something that would be cool.

Is this something you are working on with PHP Office?

On a separate note here are two links that help you using PHP to find out if the file is comma, tab, or pipe separated - quiet a clever solution:

how to find out if csv file fields are tab delimited or comma delimited

How should I detect which delimiter is used in a text file?

Solution 2

This worked for me:

        $objReader = new PHPExcel_Reader_CSV();
        $objReader->setDelimiter(';');
        $objReader->setEnclosure('');
        $objReader->setLineEnding("\r\n");
        $objReader->setSheetIndex(0);
        $objPHPExcel = $objReader->load($myFile);

More info https://docs.typo3.org/typo3cms/extensions/phpexcel_library/1.7.4/manual.html#_Toc237519888

Solution 3

So what is the separator in your file? Is it a comma, a semi-colon, a tab, something else?

PHPExcel doesn't yet have an automagic detect mode, so unless you specify what separators and enclosures to use, it will default to a comma separator, and a double quote (") enclosure. If your file is using tabs, or semi colons, or some other character as a separator instead, then you need to manually tell the CSV reader what character to use, otherwise it will treat the row as a single cell.

There's a whole section of the User documentation for the Readers devoted to explaining these options for CSV files (section 4.6).

Note that I'm targeting logic to "best guess" separator and enclosure values from the file itself at the #phpnw13 hackathon, but until then you need to specify manually if it isn't the defaults

Comments

[rhy]

im using comma as a separator on my csv file. maybe i'll try what you have said. i'll manually set what character to use when the reader retrieves data from the csv. thanks

[cgaldiolo]

After few years, the section in the User Documentation is now section 5.6.

[Jam]

It's perfect¡ With delimiter, I get one line per element.

[ruhalde]

helped me as well, old CSV file without quotes