How do I import a whitespace-delimited text file into MySQL?

Question

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 ``` There are one or more spaces after each field, and the last field is tailed by about 36 spaces before the newline. 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 ' '; ``` but MySQL will interpret a sequence of more than one space as delimiting an empty field. Any ideas?

Accepted Answer

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

open a terminal and type:

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

this replaces all sequences of multiple spaces with one space.

How do I import a whitespace-delimited text file into MySQL?

Solution

Problem