Loading fixed-width, space delimited .txt file int

问题:

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?

回答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))
;

回答2:

    LOAD DATA
CHARACTERSET AL32UTF8
INFILE 'DCF Master 14APR2013 VSPCFM_reduced size.txt'
INTO TABLE EMPLOYEE3
(
a = TRIM(SUBSTR(@row,1,11)),
b = TRIM(SUBSTR(@row,33,38)),
c = TRIM(SUBSTR(@row,70,86))
)