Create tables without knowing the content of a csv

[AMarchand1696583542]

Hello, I am new to Talend, and I need to create tables in a database from different CSV files following this model: Table name = File name, Column name = 1st line of my csv , data = the other lines of my CSV. I don't know in advance the number of columns or rows but I need to automate all of this without using dynamic mode. Anyone have an idea how to do this? Thanks in advance !

[Anonymous]

Hi

The 'Dynamic schema' functionality allows you to create a schema without knowing the number of columns, you can find more details about Dynamic schema in this page.

https://help.talend.com/r/en-US/8.0/studio-user-guide/dynamic-schema

Dynamic schema scenarios.

https://help.talend.com/r/en-US/8.0/dynamic-schema/tsetdynamicschema-tfileinputpositional-tfileoutputpositional-tfixedflowinput-handling-positional-file-based-on-dynamic-schema-standard-component-enterprise-this

Note: the 'Dynamic schema' feature is only available in Talend Enterprise subscription products.

A simple Job design looks like:

tFileList--iterate--tFileInputDelimited--main-->tMysqlOutput

Regards

Shong

[AMarchand1696583542]

Hello shong thanks for your anwser, unfortunatly we don't have enterprise licence ... So i try to find another way to do it

[Anonymous]

@Adrien Marchand​ , without dynamic schema, it would be difficult to achieve such needs. You can't use tDBOutput to insert data into Database without knowing the column names.

My thought is (I haven't try it):

1- Read the first line of CSV file as one line using tFileInputRaw component, write Java code to extract the column names on a tJavaRow and build a create table query based on the column names, and then, execute the create table query using tDBRow component.

tFileInputRaw--main--tJavaRow--oncomponentok--tDBRow.

2- Read the file content line by line (skip the first line), iterate each line, write Java code to extract the column values on a tJavaRow and build a insert ... query, execute the insert query using tDBRow component.

tFileInputRaw--main--tFlowToIterate--iterate--tFixedFlowInput--main--tJavaRow--oncomponentok--tDBRow.

Regards

Shong

[LHall1681108134]

Thank you, I will try it.