0

Consider the following three files:

1.csv [contains 3 fields: a, b, c] 2.csv [contains 4 fields: d, e, f, g] 3.csv [contains 2 fields: h, i]

My assignment is to load all three files to their respective table output. So

File "*.csv" ->loads-> Table "*_csv"

I know I can process multiple files with the "Get File Names" step but how do I generate a DDL statement that creates the target table for each file? I am looking at the metadata injection step but I am not sure this fits my needs.

Any advice?

Pentaho Data Integration 7.0 Postgres RDS

Alan
  • 51
  • 8

1 Answers1

0

You can below procedure to create the table dynamically.The only prob in this procedure is it is creating table with the same name. play with this code. I created this code in mysql. Prerequisite is you have to pass field names in concatenated form like 'col1,col2,col3'.

delimiter $$

create procedure dynamic_table (col_concat varchar(2000),out query1 varchar(1000))

begin

declare i integer ;

declare v_count int;

declare v_col varchar(100);

set i =1;

select LENGTH(col_concat) - LENGTH(REPLACE(col_concat, ',', ''))+1 into v_count;

set query1 =(select concat('create table table',convert(i,signed),' ( '));

while (i<= v_count)

do

begin

select replace(substring_index(col_concat,',',i),',','_') into v_col;

set query1 = (select concat(query1, v_col,' varchar(1000), '));

set i=i+1;

end;

end while;

select query1;

set query1= replace(query1,substring(query1,length(query1)-1,1),' )');

select query1;

end;$$

sample run : call dynamic_table ('col1,col2,col3',@query1)

karan arora
  • 176
  • 9