Geek Answers Handbook

How to import a space delimited text file in MySQL?

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

1 1 0.008  0  0  0  0  0

After each field, there is one or more spaces, and the last field has a length of about 36 spaces before the new line.

How to import such a file into MySQL? From the documentation, it seems that LOAD DATA expects all fields to be completed with exactly the same line. I tried

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

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

Any ideas?

+5
source share
4 answers

If you are using unix / linux, you can pass it through sed.

:

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

.

+9

Windows, Excel.

Excel ( " " ).

CSV Excel MySQL, :

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

, Jauco, ';' \n. .

0

? PHP script , :

<?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 ;)

0

More articles:


All Articles