将固定宽度，空格分隔的.txt文件加载到mySQL中 [英] Loading fixed-width, space delimited .txt file into mySQL

问题描述

我有一个.txt文件，里面有一堆格式化的数据，如下所示：

  .. 
 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 
 ... 
  

我试图加载每行作为一个表入我的数据库，其中每列是一个不同的领域。我无法让MySQL正确分离所有的数据。我认为这个问题是由于这样一个事实，即并不是所有的数字都用一个等距的空白量来分隔的。

这是我迄今为止尝试过的两个查询我也尝试了这些查询的几个变种）：

$ p $ LOAD DATA LOCAL INFILE $ b $ / some / Path / segmentation .txt'
INTO TABLE剪辑（slideNum，startTime，endTime）
SET presID = 1;


LOAD DATA本地INFILE
'/some/Path/segmentation.txt'
表格剪辑
字段终止符''
'\\\
'
（slideNum，startTime，endTime）
SET presID = 1;


任何想法如何得到这个工作？

解决方案

这些就是我们所说的固定宽度记录，LOAD DATA不能很好地与它们配合。选项：

1. 首先在Excel中清理数据，或者
   
2. 将数据加载到temp只有一列的表，将整个文本行推入该列。然后你可以使用 SUBSTR（）和 TRIM（）来把你需要的列分成最后一个表。 / li>
   
3. 或使用用户变量（@row），您可以在LOAD DATA语句中完成所有操作。

< pre-class =lang-sql prettyprint-override> 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））
;


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?

解决方案

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

这篇关于将固定宽度，空格分隔的.txt文件加载到mySQL中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！