topic Re: Identify new columns in excel file & load into redshift db in Talend Studio

Identify new columns in excel file & load into redshift db

Tina3 — Sat, 16 Nov 2024 00:44:31 GMT

Hi all,

i have a scenario where I am trying to load all the columns from excel to redshift & delete the excel at the end of job

excel file gets generated everyday & sometimes new columns gets added in b/w the excel file.

For instance : excel has three columns and fourth column gets added in b/w Id & address

name Id address

moderna 6 UK

how do you I identify these new columns & either load all the columns including the new columns into redshift or Notify in an email

my job flow - tfileexcelinput -> tmap -> tdboutput

Re: Identify new columns in excel file & load into redshift db

Prakhar1 — Thu, 28 Jan 2021 08:43:30 GMT

you can use "Dynamic" Schema if you are using licensed version of Talend.

Re: Identify new columns in excel file & load into redshift db

Tina3 — Fri, 29 Jan 2021 03:36:13 GMT

Thank you prakhar.

We use a licensed version.can you guide me through the job flow or any documentation.

Re: Identify new columns in excel file & load into redshift db

Prakhar1 — Fri, 29 Jan 2021 04:45:27 GMT

https://community.talend.com/s/article/Which-components-provide-the-Dynamic-Schema-feature-TBe8P#:~:text=Dynamic%20Schema%20is%20an%20advanced,the%20data%20to%20be%20handled

You can refer above link.

Let me know for further support

Re: Identify new columns in excel file & load into redshift db

Tina3 — Fri, 29 Jan 2021 04:52:30 GMT

Is tfileexcelinput is supported by dynamic schema?.I don’t see that in the list

Re: Identify new columns in excel file & load into redshift db

JohnRMK — Sun, 31 Jan 2021 20:39:20 GMT

Hello,

The dynamic schema is a very powerful feature which allows you to have the name of the columns on the fly without prior definition.

But, if I understood your use case correctly, it cannot be used directly because the data is inserted into an existing table so it will have to be created at each execution and you will lose the already existing data. if not then you stopped reading

if you already have a table in your database and you insert a new file with new columns, then here is an approach:

- Retrieve the columns of your target table (information_schema or a similar schema). Store this in memory ==> tHashOutput

- Retrieve the columns of your Excel or CSV file. either from external metadata or by reading for example the file with a limit 1 and header 0. So, you will have a line with the name of the columns.

you can use a tExtractdynamicFields component with java code to extract column names too.

Once you have both, capitalize everything, tMap two tHashInput and you get the join reject. ==> Extra columns in your file. you recep these columns, you concatenate them with strings like "ALTER TABLE + var_global_table_name + ADD COLUMN" columnName ". then you execute that in a tDBRow and after you can get the data and insert it

Re: Identify new columns in excel file & load into redshift db

Prakhar1 — Mon, 01 Feb 2021 05:28:27 GMT

Sure it does, I use it in my jobs.

Re: Identify new columns in excel file & load into redshift db

Tina3 — Mon, 01 Feb 2021 10:55:47 GMT

Thank you John for the response.

i was able to successfully retrieve the dynamic schema from excel using tfileexcelinput & load into tfileoutputdelimited file.

i have to load these dynamic columns to redshift & I don’t have to store previous data or table schema of redshift.i can drop and recreate the table if exists

how do I map these dynamically generated tfileoutputdelimited file to tmap and tdboutput to retrieve the latest metdata.below is my workflow

tfileexcelinput (load only dynamic columns from a excel sheet) ——> tfileoutputdelimited —> on component ok —> tfileinputdelimited —> tmap ——> tdboutput

Re: Identify new columns in excel file & load into redshift db

Tina3 — Wed, 03 Feb 2021 17:28:02 GMT

Thank you prakhar.

You are right.Currently i am reading all the current columns as a single dynamic column and loading it as CSV output

How do feed this newly changed schema to tmap and load into redshift table ?

I can always drop and recreate the table on redshift

Re: Identify new columns in excel file & load into redshift db

Prakhar1 — Thu, 04 Feb 2021 05:50:03 GMT

@Not defined Not defined

If you are not doing any transformation then don't use tMap.

Directly insert it to the Redshift Output and in that output choose the "Action on table" to "Drop and Create Table".

It will drop the old table and insert the new values and with dynamic schema also.