Reputation: 35250
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?
Upvotes: 5
Views: 14640
Reputation: 29
If you're on Windows, just use Excel.
Excel will import a whitespace-delimited file (check the 'treat subsequent delimiters as one' box from the import menu).
Then you can simply save the file as a CSV from Excel and import into MySQL using:
LOAD DATA INFILE 'filename' INTO TABLE mytable FIELDS TERMINATED BY ',';
Upvotes: 2
Reputation: 4258
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
$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 ;)
Upvotes: 0
Reputation: 12426
You can also use the same command posted by Jauco to change the delimiter to ';' or \n. That would also help.
Upvotes: 0
Reputation: 1319
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.
Upvotes: 10