From a csv/excel file with unknown number of Columns to a database

Five Stars

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.

Is it possible?

Thank you all for your answers!

Eleven Stars

Re: From a csv/excel file with unknown number of Columns to a database

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

 

if You have a lot of data you can use bulkload

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Five Stars

Re: From a csv/excel file with unknown number of Columns to a database

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?

 

Thank you for your answer?

Eleven Stars

Re: From a csv/excel file with unknown number of Columns to a database

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.

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Five Stars

Re: From a csv/excel file with unknown number of Columns to a database

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.

Five Stars

Re: From a csv/excel file with unknown number of Columns to a database

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.