Geek asks and answers

Loading a fixed-width file limited by a .txt space in mySQL

I have a .txt file with a bunch of formatted data that looks like this:

...
   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 in a table in my database, where each column is a different field. I am having trouble getting mySQL to properly split all the data. I think the problem is due to the fact that not all numbers are separated by an equidistant sum of white space.

Here are two queries that 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 make this work?

+5
source share
2 answers

, , LOAD DATA . :

  • Excel
  • temp , . SUBSTR() TRIM(), .
  • (@row) LOAD DATA.
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))
;
+10
    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))
)
0

More articles:


All Articles