Loading fixed-width, space delimited .txt file into mySQL

user_1522168 0
Viewed 14805

I have a .txt file that has a bunch of formatted data in it that looks like the following:

...
   1     75175.18     95128.46
   1    790890.89    795829.16
   1    875975.98    880914.25
   8   2137704.37   2162195.53
   8   2167267.27   2375275.28
  10   2375408.74   2763997.33
  14   2764264.26   2804437.77
  15   2804504.50   2881981.98
  16   2882048.72   2887921.25
  16   2993093.09   2998031.36
  19   3004104.10   3008041.37
...

I am trying to load each row as an entry into a table in my database, where each column is a different field. I am having trouble getting mySQL to separate all of the data properly. I think the issue is coming from the fact that not all of the numbers are separated with an equidistant white-space amount.

Here are two queries I have tried so far (I have also tried several variations of these queries):

LOAD DATA LOCAL INFILE 
'/some/Path/segmentation.txt' 
INTO TABLE clip (slideNum, startTime, endTime) 
SET presID = 1;


LOAD DATA LOCAL INFILE 
'/some/Path/segmentation.txt' 
INTO TABLE clip 
FIELDS TERMINATED BY ' ' 
LINES TERMINATED BY '\n'
(slideNum, startTime, endTime) 
SET presID = 1;

Any ideas how to get this to work?

local load file-io mysql
3 Answers

  1. If you're on unix/linux then you can put it through sed to strip out spaces. The solution is here

  2. You can programmatically replace spaces with a different delimiter. I decided to use PHP, you can also safely do it in Python

    <?php
$mysqli  =  new mysqli(
"***",
"***",
"***",
"***",
3306
);
mysqli_options($mysqli, MYSQLI_OPT_LOCAL_INFILE, true);

if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

function createTempFileWithDelimiter($filename, $path){
    $content = file_get_contents($filename);
    $replaceContent = preg_replace('/\ +/', ',', $content); // NOT \s+

    $onlyFileName = explode('\\',$filename);

    $newFileName = $path.end($onlyFileName);
    file_put_contents($newFileName, $replaceContent);

    return $newFileName;
}

$pathTemp = 'C:\\TempDir\\';

$pathToFile = 'C:\\some\\Path\\segmentation.txt';

$file = createFileWithDelimiter($pathToFile, $pathTemp);
$file = str_replace(DIRECTORY_SEPARATOR, '/', $file);

$sql = "LOAD DATA LOCAL INFILE '".$file."' INTO TABLE `clip` 
    COLUMNS TERMINATED BY ','
    LINES TERMINATED BY '\n'  // or '\r\n'
    (slideNum, startTime, endTime)
    SET presID = 1;";

if (!($stmt = $mysqli->query($sql))) {
    echo "\nQuery execute failed: ERRNO: (" . $mysqli->errno . ") " . $mysqli->error;
};

unlink($file);
?>

Don't use '/\s+/' in preg_replace because \s matches any whitespace character (equivalent to [\r\n\t\f\v ]) and the formatting will change, columns and line breaks will disappear.

user_130685000
Related