Problem with .csv files in PHPExcel

Question

I have a problem with PHPExcel while reading .csv files.

I wanted to get values ​​from a 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 works with .xls and .xlsx files, and I get the desired result that I wanted.

Answer 1

This works great:

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

However, if you are not 100% sure if its tab or comma does not exist, there may be no way to add BOTH, for example. $objReader->setDelimiter("\t",); as required. When you open Excel and go to Import CSV, the actual steps on the screen allow you to specify several delimiters, which would be cool.

Is this what you are working on in PHP Office?

In a separate note, there are two links that will help you use PHP to find out if a file is a comma, tab, or pipe separated - a quiet smart solution:

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

How to determine which delimiter is used in a text file?

Answer 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); 

Additional information https://docs.typo3.org/typo3cms/extensions/phpexcel_library/1.7.4/manual.html#_Toc237519888

Answer 3

So what is a delimiter in your file? Is it a comma, half-colon, tab, something else?

PHPExcel does not yet have auto-detection mode, so if you don’t specify which separators and shells to use, the default will be a comma separator and a double-quoted shell ("). If your file uses tabs or half-columns or some other character in as a separator, then you need to manually tell the CSV reader which character to use, otherwise it will treat the string as a separate cell.

It contains the entire section of the user documentation for readers devoted to the explanation of these parameters for CSV files (section 4.6).

Please note that I am aiming the logic at the “best guess” of the separator and shell values ​​from the file itself in hackathon # phpnw13, but before that you need to specify manually if this is not the default