PHP 电子表格读取 CSV

Question

我正在尝试使用 PHP 电子表格库读取 CSV 文件。

我成功获取了数据，但它把分隔到数组中的逗号打错了。

$reader = new \PhpOffice\PhpSpreadsheet\Reader\Csv();

$spreadsheet = $reader->load('File.csv');
$sheetData   = $spreadsheet->getActiveSheet()->toArray();

echo '<pre>';
print_r($sheetData);

当我看到输出时，它看起来像这样:

Array
(
    [0] => Array
        (
            [0] => Id,Record,Book,Description,Date,Action
            [1] => 
            [2] => 
        )

    [1] => Array
        (
            [0] => 1306,5466,84,Item,04-05-2018 06
            [1] => 26
            [2] => 28,Auto Show
        )
)

代替:

Array
(
    [0] => Array
        (
            [0] => Id
            [1] => Record
            [2] => Book
            [3] => Description
            [4] => Date
            [5] => Action
        )

    [1] => Array
        (
            [0] => 1306
            [1] => 5466
            [2] => 84
            [3] => Item
            [4] => 04-05-2018 06:26:28
            [5] => Auto Show
        )
)

提前谢谢你。

Answer 1

您是否尝试过强制行尾保持一致？我以前遇到过这个，在解析 CSV 之前必须添加一个 PHP ini 设置:

ini_set('auto_detect_line_endings',TRUE)

把它放在 $reader 之前

Edit via request in comments

这是我用于 CSV 的类，假设您至少有 PHP 5.5:

<?php

use Exception;
use InvalidArgumentException;
use SplFileObject;
use NoRewindIterator;

class LargeFile {

const ERROR_UNABLE = 'ERROR: Unable to open file';
const ERROR_TYPE = 'ERROR: Type must be "ByLength", "ByLine", or "Csv"';

protected $file;
protected $allowed_types = [ 'ByLine', 'ByLength', 'Csv' ];

/**
 * LargeFile constructor.
 *
 * @param $filename
 * @param string $mode
 *
 * @throws Exception
 *
 * Populates $file with new SPL File object instance.
 */
public function __construct($filename, $mode = 'r') {
    if (!file_exists($filename)) {
        $message = __METHOD__ . ' : ' . self::ERROR_UNABLE . PHP_EOL;
        $message .= strip_tags($filename) . PHP_EOL;
        throw new Exception($message);
    }
    $this->file = new SplFileObject($filename, $mode);
}

/**
 * @return \Generator|int
 *
 * References SplFileObject method to read the file one line
 * at a time with fgets.
 *
 * Suitable for smaller text files like Csvs and / or
 * include line feeds.
 */
protected function fileIteratorByLine() {
    $count = 0;

    while (!$this->file->eof()) {
        yield $this->file->fgets();
        $count++;
    }
    return $count;
}

/**
 * @param $numBytes
 *
 * @return \Generator|int
 *
 * References SplFileObject method to read the file one line
 * at a time with freads.
 *
 * Suitable for larger binary files.
 */
protected function fileIteratorByLength($numBytes) {
    $count = 0;

    while (!$this->file->eof()) {
        yield $this->file->fread($numBytes);
        $count++;
    }
    return $count;
}

protected function fileIteratorCsv() {
    $count = 0;

    while (!$this->file->eof()) {
        yield $this->file->fgetcsv();
        $count++;
    }
    return $count;
}

/**
 * @param string $type
 * @param null $numBytes
 *
 * @return NoRewindIterator
 */
public function getIterator($type = 'ByLine', $numBytes = null) {
    if (!in_array($type, $this->allowed_types)) {
        $message = __METHOD__ . ' : ' . self::ERROR_TYPE . PHP_EOL;
        throw new InvalidArgumentException($message);
    }
    $iterator = 'fileIterator' . $type;
    return new NoRewindIterator($this->$iterator($numBytes));
}
}

你可以这样使用它:

$largeFile = new LargeFile($file);
// tell it use the CSV iterator
$iterator  = $largeFile->getIterator('Csv');

// start iterator and pull out header row as $rawHeaders
$rawHeaders = $iterator->current();

$headerIterator = new \ArrayIterator;
foreach ($rawHeaders as $header) {
    $headerIterator->append(strtolower("`$header`"));
}

$headers = $headerIterator->getArrayCopy();

// move pointer to the data rows
$iterator->next();

// loop through each row
foreach( $iterator as $row ) {
  // do stuff with each row
  print_r($row);
}