How to import text data in mysql with spaces?

Lets say you have to import a large text file to mysql and fields are totally irregular but only separated by spaces. For example. Since it is only text file and highly irregular data you have to use other methods to import data to database.

17876           BILL            hc-device          4.76                FC900888        0.88776           USD

There are 3 ways to import text data into mysql

1. Use a fixed width data import method

Try to analyse and measure the data in text file and you will have a brief idea on length of fields.

LOAD DATA LOCAL
INFILE '<file name>' INTO TABLE <table>
(@var1)
SET
 `name`=SUBSTR(@var1,1,25),
 `address`=SUBSTR(@var1,26,25),
 `zip`=SUBSTR(@var1,51,10),
 `phone`=SUBSTR(@var1,61,10)
IGNORE 35 LINES

2. MySQL Load Data INFILE method

Usually mysql does not interpret multiple spaces as one, you have to group all spaces first using sed command line, then you can import using load data infile.

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

The above command groups all spaces into one and (the regular expression does it) and the resulting data is output written into new file. Once done we can feed  the new file to load data infile.

LOAD DATA INFILE '<filename>' INTO TABLE <table> FIELDS TERMINATED BY ' ' LINES TERMINATED BY '\n';

3. Parsing a text file using PHP

You can parse the text file using preg_split and matching regular expressions using php. The code below will separate columns separated by spaces.

<?php

$fileHandle= @fopen("data.txt", "r");

if ($fileHandle) {

while (!feof($fileHandle)) {

$i++;

$lines = fgets($fileHandle, 4096);

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

if(preg_match('/[0-9]+/', $columns[1])) {

echo $columns[0] . '---> '. $columns[1] . ' = '. $columns[2] . $columns[3] . $columns[4] .'<br>';

// Once columns isolated, you can insert into mysql

mysql_query("INSERT into <table> (col1,col2,col3) VALUES (columns[0], columns[1]. columns[2])") or die(mysql_error());

}

}

fclose($fileHandle);

}

?>