topic Create tables without knowing the content of a csv in Talend Studio

Create tables without knowing the content of a csv

AMarchand1696583542 — Fri, 15 Nov 2024 21:21:18 GMT

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 !

Re: Create tables without knowing the content of a csv

Anonymous — Sun, 08 Oct 2023 07:50:51 GMT

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

Re: Create tables without knowing the content of a csv

AMarchand1696583542 — Mon, 09 Oct 2023 06:57:18 GMT

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

Re: Create tables without knowing the content of a csv

Anonymous — Tue, 10 Oct 2023 03:42:01 GMT

@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

Re: Create tables without knowing the content of a csv

LHall1681108134 — Tue, 17 Oct 2023 07:20:52 GMT

Thank you, I will try it.