mysql - PHP LOAD DATA INFILE 仅插入某些文件的第一行

Question

我有一个 .dat 文件，该文件是从另一个应用程序中提取的，该文件包含许多行逗号分隔数据。我使用 Excel 打开该文件，并将其另存为 .csv。我可以使用LOAD DATA INFILE将该文件毫无问题地上传到我的数据库。

但这就是事情变得奇怪的地方。如果我获取原始文件并尝试复制(例如)20 行并创建一个新的 .csv 文件，当我将其上传到相同的数据库、相同的表架构时，它只会加载第一行。当我另存为新的 .csv 时，Excel 是否对我的格式做了一些处理？对我来说看起来是一样的...用其他文本编辑器打开时我看不出有什么区别。

这是我为要导入的数据创建新表的 SQL(我首先检查该表是否已存在):

// Create table - Table name matches name of file without extension
$sql = "CREATE TABLE " . $file_name . " (
    history_id INT,
    stream_id INT,
    record_time DATETIME,
    ALEQ FLOAT,
    APEAK FLOAT,
    AFASTSPL FLOAT,
    dose FLOAT,
    color INT
)";

这是我用于加载 .csv 的 SQL:

// Insert data from uploaded .csv file into table
$sql = "LOAD DATA LOCAL INFILE '" . $target_file . "' 
        INTO TABLE " . $file_name . "
        FIELDS OPTIONALLY ENCLOSED BY '''' TERMINATED BY ','
        LINES TERMINATED BY '\n'";

以下是有效文件的 .csv 格式:

1,1,'2014-08-28 07:00:31',35.2,56.0,35.1,0.0000000,0
2,1,'2014-08-28 07:00:31',35.1,56.0,35.4,0.0000000,0
3,1,'2014-08-28 07:00:31',35.1,56.0,35.2,0.0000000,0
4,1,'2014-08-28 07:00:31',35.3,56.0,35.0,0.0000000,0
5,1,'2014-08-28 07:00:31',35.2,56.0,35.3,0.0000000,0
6,1,'2014-08-28 07:00:31',35.2,56.0,35.2,0.0000000,0

这是无效文件的 .csv:

60135,1,'2014-08-28 15:19:19',52,68.3,52.6,0.43186,0
60136,1,'2014-08-28 15:19:20',56.9,71.6,51.3,0.43186,0
60137,1,'2014-08-28 15:19:20',56.5,68.8,58.3,0.43186,0
60138,1,'2014-08-28 15:19:21',57,70.1,56.4,0.43186,0
60139,1,'2014-08-28 15:19:21',56.5,69.2,56.2,0.43186,0
60140,1,'2014-08-28 15:19:22',58.7,71.4,56.7,0.43186,0

Answer 1

如果它只加载第一行，那么它就无法正确检测行结尾。我建议使用十六进制编辑器来检查源文件中的行结尾。

也许您保存为“CSV (Macintosh)”，它使用\r 作为行结尾。如果是这种情况，您应该能够使用

LINES TERMINATED BY '\r'";

如果您不知道行结尾，则更通用的解决方案...

MySQL 没有自动行结束检测，这就是为什么你必须告诉它如何解析数据。您可以使用这个简短的函数进行一些基本的频率分析。

function detectLineEndings($file)
{
    $data = file_get_contents($file);
    $le_linux = substr_count($data, "\n");
    $le_windows = substr_count($data, "\r\n");
    $le_mac = substr_count($data, "\r");

    if ($le_linux > $le_windows && $le_linux > $le_mac) {
        return '\n';
    }
    if ($le_mac > $le_linux && $le_mac > $le_windows) {
        return '\r';
    }
    return '\r\n';
}

$endings = detectLineEndings($target_file);

$sql = "LOAD DATA LOCAL INFILE '" . $target_file . "' 
        INTO TABLE " . $file_name . "
        FIELDS OPTIONALLY ENCLOSED BY '''' TERMINATED BY ','
        LINES TERMINATED BY '" . $endings ."'";