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

Question

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?

Answer 1

These are what we call "fixed-width" records and LOAD DATA doesn't play well with them. Options:

1. Clean up data in Excel first, or
2. Load up the data to a temp table with only 1 column, shoving an entire text row into that column. Then you can use SUBSTR() and TRIM() to slice out the columns you need into the final table.
3. Or with user variables (@row) you can do it all within the LOAD DATA statement.

LOAD DATA LOCAL INFILE 
'/some/Path/segmentation.txt' 
INTO TABLE clip
(@row)
SET slideNum = TRIM(SUBSTR(@row,1,4)),
    startTime = TRIM(SUBSTR(@row,5,13)),
    endTime = TRIM(SUBSTR(@row,18,13))
;