导入一系列包含一个字段的.csv文件,同时在其他字段中添加其他“已知”数据

o2gm4chl  于 2021-06-23  发布在  Mysql
关注(0)|答案(1)|浏览(288)

我有一个进程可以创建一个csv文件,其中包含一组需要导入mysql数据库表中的字段的值。此过程将创建一个特定的文件名,用于标识该表中其他字段的值。例如,文件名t001u020c075.csv将分解如下:

T001 = Test 001
U020 = User 020
C075 = Channel 075

该文件包含一行数据,用逗号分隔该用户在特定通道上的所有测试结果,它可能看起来像:12.555、15.275、18.333、25.000(每个用户、每个频道有数百个,甚至数千个结果)。
我想做的是直接从csv文件导入,从文件名添加字段信息,这样看起来像:

insert into results (test_no, user_id, channel_id, result) values (1, 20, 75, 12.555)

我尝试过使用“批量插入”,但似乎希望导入每行都是记录的所有字段。当然,我可以进入每个文件,将行转换为列,并将文件名中的数据添加到结果前面的列中,但这将是一项非常耗时的任务,因为已经创建了数百个文件,需要导入。
我发现了几种“导入csv”解决方案,但它们都假设所有数据都在文件中。显然,这不是。。。
生成这些文件的进程无法修改(是的,我问过)。即使可以修改,也只能提供正确的格式,需要的是对历史数据的分析。而且,新的格式将占用更多的空间。
我仅限于使用matlab或mysql工作台来导入数据。
感谢您的帮助。鲍勃

4ioopgfo

4ioopgfo1#

将数据加载到表中的一种可能的sql方法是运行如下语句:

LOAD DATA LOCAL INFILE '/dir/T001U020C075.csv'
INTO TABLE results 
FIELDS TERMINATED BY '|'
LINES TERMINATED BY ','
( result )
SET test_no    = '001'
  , user_id    = '020'
  , channel_id = '075'

;
我们需要逗号作为行分隔符。我们可以指定一个字符,保证不作为字段分隔符。所以我们得到 LOAD DATA 要在每个“行”上查看单个“字段”。
(如果文件末尾没有逗号,在最后一个值之后,我们需要进行测试以确保得到最后一个值(我们告诉load data查看文件时的最后一行)
我们可以使用用户定义的变量来代替文本,但这就剩下了解析文件名的部分。这在sql中真的很难看,但是可以做到,假设文件名格式一致。。。

-- parse filename components into user-defined variables
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(f.n,'T',-1),'U',1)   AS t
     , SUBSTRING_INDEX(SUBSTRING_INDEX(f.n,'U',-1),'C',1)   AS u
     , SUBSTRING_INDEX(f.n,'C',-1)                          AS c
     , f.n                                                  AS n
  FROM ( SELECT SUBSTRING_INDEX(SUBSTRING_INDEX( i.filename ,'/',-1),'.csv',1) AS n 
           FROM ( SELECT '/tmp/T001U020C075.csv' AS filename ) i 
       ) f
  INTO @ls_u
     , @ls_t
     , @ls_c
     , @ls_n
;

在进行测试时,我们可能希望看到解析的结果。

-- for debugging/testing
SELECT @ls_t
     , @ls_u
     , @ls_c 
     , @ls_n
;

然后是实际运行部分 LOAD DATA 声明。我们必须再次指定文件名。我们需要确保使用相同的文件名。。。

LOAD DATA LOCAL INFILE '/tmp/T001U020C075.csv'
INTO TABLE results
FIELDS TERMINATED BY '|'
LINES TERMINATED BY ','
( result )
SET test_no    = @ls_t
  , user_id    = @ls_u
  , channel_id = @ls_c

;
(客户端需要对.csv文件的读取权限)
不幸的是,我们无法将其 Package 到过程中,因为正在运行 LOAD DATA 存储程序中不允许使用语句。
有人会正确地指出,作为一种变通方法,我们可以编译/构建一个用户定义函数(udf)来执行一个外部程序,并且一个过程可以调用它。就我个人而言,我不会这么做。但考虑到种种限制,这是我们应该提及的另一种选择。

相关问题