如何将以空格分隔的文本文件导入MySQL？

I need to import largish (24MB) text files into a MySQL table. Each line looks like this:

我需要将大型(24MB)文本文件导入MySQL表。每行看起来像这样:

1 1 0.008  0  0  0  0  0

There are one or more spaces after each field, and the last field is tailed by about 36 spaces before the newline.

每个字段后面有一个或多个空格,最后一个字段在换行符之前大约有36个空格。

How do I import such a file into MySQL? From the documentation it seems that LOAD DATA expects all fields to be terminated by exactly the same string. I have tried

如何将这样的文件导入MySQL?从文档中可以看出,LOAD DATA期望所有字段都以完全相同的字符串终止。我试过了

LOAD DATA INFILE 'filename' INTO TABLE mytable FIELDS TERMINATED BY ' ';

but MySQL will interpret a sequence of more than one space as delimiting an empty field.

但MySQL将解释一个多个空格的序列作为分隔空字段。

Any ideas?

4 个解决方案

#1

If you're on unix/linux then you can put it through sed.

如果你在unix / linux上,那么你可以通过sed。

open a terminal and type:

打开终端并键入:

sed 's/ \+/ /g' thefile > thefile.new

this replaces all sequences of multiple spaces with one space.

这将用一个空格替换多个空格的所有序列。

#2

If you're on Windows, just use Excel.

如果您使用的是Windows,请使用Excel。

Excel will import a whitespace-delimited file (check the 'treat subsequent delimiters as one' box from the import menu).

Excel将导入以空格分隔的文件(请从导入菜单中选中“将后续分隔符视为一个”框)。

Then you can simply save the file as a CSV from Excel and import into MySQL using:

然后,您只需将文件从Excel保存为CSV,然后使用以下命令导入MySQL:

LOAD DATA INFILE 'filename' INTO TABLE mytable FIELDS TERMINATED BY ',';

#3

You can also use the same command posted by Jauco to change the delimiter to ';' or \n. That would also help.

您还可以使用Jauco发布的相同命令将分隔符更改为“;”或\ n。这也有帮助。

#4

Is there no way you can do this pragmatically? A simple PHP script would be able to load the file in, split by spaces, and do an insert in no time at all:

有没有办法可以做到这一点实用?一个简单的PHP脚本可以加载文件,按空格分割,并立即插入:

<?php

$db = mysql_connect('host', 'user', 'password')
or die('Failed to connect');
mysql_select_db('database', $db);

$fileHandle= @fopen("import.file", "r");
if ($fileHandle) {
    while (!feof($fileHandle)) {
        $rawLine = fgets($fileHandle, 4096);

        $columns = preg_split("/\s+/", $rawLine);

        //Construct and run an INSERT statement here ...   

    }
    fclose($fileHandle);
}

?>

Edit That being said, Jakal's suggestion is far neater ;)

编辑那就是说,雅加尔的建议更加整洁;)

#1