From a csv/excel file with unknown number of Columns to a database
Hi,
I have been looking for an answer for the past few days. I thought the "dynamic" type of column could help for this but it doesn't. So, I have a csv/excel file in entry that I know nothing about except that the first row contains the columns names (but I am unaware of their number, their types or whatever). I must then insert those into a table (which has to be created with the correct number of columns and the correct names) from a MS SQL server database.
I could developp an entire java program that would do that successfully but my team would prefer it to be done using talend.
read first line, generate create table or alter table to adapt db table (use sys table to get current table name and table fields) then use dynamics format to load data
I'm sorry, I didn't understand much in what you were just saying, I'm very new to Talend. What do you mean by "read first line"? Using a TFileInputDelimited connected to a tCreateTable? What is sys table?
the first line of your file contain columns name.
all db have système table. they are here to get informations about tables fields triggers ……
so first create or update the db tables and columns.
and then use dynamics to upload data.
Creating the table is part of the job. Meaning that (if the table doesn't already exist), the table has to be created automatically at the start of the job with the adequate columns. How do I make talend read the first line and create a table accordingly?
All I'm thinking about right now is :
tFileInputDelimited -------> tJavaRow
With the tFileInputDelimited limited to the first row and using jdbc to create a connexion to the database and creating a table.
And even this doesn't seem to work since I think I have to prepare the schema in the tFileInputDelimited which is impossible since the schema changes with each input file.
