如何将空格分隔的文本文件导入 MySQL? [英] How do I import a whitespace-delimited text file into MySQL?

问题描述

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

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

1 1 0.008  0  0  0  0  0                                    

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

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

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

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

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

但 MySQL 会将包含多个空格的序列解释为分隔空字段.

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

有什么想法吗?

推荐答案

如果你使用的是 unix/linux，那么你可以通过 sed 来解决.

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

打开终端并输入:

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

这会将多个空格的所有序列替换为一个空格.

this replaces all sequences of multiple spaces with one space.

这篇关于如何将空格分隔的文本文件导入 MySQL?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！