topic Re: create staging table in Talend Studio

create staging table

Anonymous — Sat, 16 Nov 2024 06:18:42 GMT

Hi,

I've got a MS SQL 2012 Server and I'm connection successful with the following Parameters:

DB Version: Open source JTDS

Parameters: domain=yourdomain;useNTLMv2=true

if there is a better option I would prefer to use it

=======

I want to load data from a .csv file into a new created table (staging table), this .csv is changing every time if a new supplier sends it (in it are article data), my first question is - how to load this csv into a new table?

After uploading and loading the new data into the correct tables (I think this will be another post) I would like to delete or truncate the table - how to do this?

=======

Thank you for your help!

apit

Re: create staging table

Anonymous — Mon, 18 Mar 2019 07:24:07 GMT

Hi,

You can read multiple files to DB at same time. You need to use tFileList to read the files.

Then for each file (provided the schema is same), you can connect to target DB with create table option enabled.

For each input file, you can use the same name as of input file (after trimming the file extension) so that new table will be created if its not there.

Please note that I have directly added file name here. But you need to remove the extn using a tjavarow or tjava.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: create staging table

Anonymous — Mon, 18 Mar 2019 07:29:05 GMT

Hi @apit

If you want to delete or truncate, you can use tDBRow component where you can add the delete or truncate command.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: create staging table

Anonymous — Mon, 18 Mar 2019 15:28:04 GMT

@nthampithank you for your help - but for the moment I get a error.

Compile Error on Line 1335, pstmt_tDBOutput_2 cannot be resolved.

Hope you can help

Re: create staging table

Anonymous — Mon, 18 Mar 2019 16:01:03 GMT

Hi,

Could you please show me the content of the error component?

Please also print and verify the output data in tjava row also to make sure that there are no data issues like table name format.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: create staging table

Anonymous — Tue, 19 Mar 2019 06:48:01 GMT

Sure

Can you explain me how to iterate all files? I have no idea how it is working - or something equivalent to read, thank you very much!

How would it be possible to edit code?

Here are the errors:

Re: create staging table

manodwhb — Tue, 19 Mar 2019 07:05:06 GMT

@apit ,I believe,you have not configured properly tDBOutput or you need to install the required jars,since i see the red color sign on the top right corner on tDBoutput

Re: create staging table

Anonymous — Tue, 19 Mar 2019 07:18:40 GMT

@manodwhbI think you are right - but maybe it is because of the name of the table, I'm not sure.

what *.jar files do I need?

Re: create staging table

Anonymous — Tue, 19 Mar 2019 07:40:27 GMT

@apit

Could you please print the current value of the tables to make sure they are following the DB standards?

Please check whether all the jars have been added to the component as it is also an important factor.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: create staging table

Anonymous — Tue, 19 Mar 2019 08:56:13 GMT

@nthampi

here are the 25st lines

Kategorie;Produktgruppe;Warengruppe;Artikelnummer;EAN;Hersteller-Artikelnummer;Beschreibung;Nettogewicht;Länge mm;Breite mm;Höhe mm;Durchmesser mm;Spannung;Kapazität;System;Herstellercode;Variante;VK-Preis;Einheit;Menge VE;Empf. EVP;Beschreibung 2;Beschreibung 3;Zollpos.;Ursprungsland
Akkus;Backup - Akkus;Backup Zellen;101183;4038338011180;101183;3/X150H mit Stecker;24;0;0;0;0;3.6;150;Ni-MH;CONNECT;;6.68;ST;1;0;CMOS Batterie;3er Reihe aus X150H;85075000;CHI
Akkus;Backup - Akkus;Backup Zellen;124540;4038338028584;124540;6/V40H mit Stecker;20;0;0;0;0;7.2;40;Ni-MH;CONNECT;;22.76;ST;1;0;CMOS Batterie;6er Raute aus V40H;85075000;CHI
Akkus;Backup - Akkus;Backup Zellen;125756;4038338028829;125756;CR2016 mit Stecker;2;0;0;0;0;3;75;Li-Mn;CONNECT;;2.02;ST;1;0;CMOS Batterie;CR2016;85065030;CHI
Akkus;Backup - Akkus;Backup Zellen;125759;4038338021431;125759;CR2025 mit Stecker;5;0;0;2.5;20;3;165;Li-Mn;CONNECT;;2.04;ST;1;0;CMOS Batterie;CR2025;85065030;J
Akkus;Backup - Akkus;Backup Zellen;125760;4038338015645;125760;CR2032 mit Stecker;3;0;0;0;0;3;220;Li-Mn;CONNECT;;1.98;ST;1;0;CMOS Batterie;CR2032;85065030;CHI
Akkus;Backup - Akkus;Backup Zellen;301291;;301291;CR2032 mit Stecker;3;0;0;0;0;3;220;;CONNECT;;1.73;ST;1;0;CMOS Batterie;CR2032;85065030;CHI
Akkus;Backup - Akkus;Backup Zellen;301293;;301293;2/V15H mit Stecker;GO;0;0;0;0;2.4;15;Ni-MH;CONNECT;;7.22;ST;1;0;CMOS Batterie;2er Reihe aus Varta V15H;85075000;CHI
Akkus;Backup - Akkus;Backup Zellen;301294;4038338017793;301294;3/V15H mit Stecker;30;0;0;0;0;3.6;15;Ni-MH;CONNECT;;8.18;ST;1;0;CMOS Batterie;3er Reihe aus V15H;85075000;D
Akkus;Backup - Akkus;Backup Zellen;301300;4038338011210;301300;6/V15H mit Stecker;12;0;0;0;0;7.2;15;Ni-MH;DIVERSE;;10.72;ST;1;0;CMOS Batterie;6er Doppelreihe aus V15H;85075000;CHI
Akkus;Backup - Akkus;Backup Zellen;301302;;301302;6/X150H mit Stecker;15;0;0;0;0;7.2;150;Ni-MH;CONNECT;;9.99;ST;1;0;CMOS Batterie;6er Doppelreihe aus X150H;85075000;D
Akkus;Backup - Akkus;Backup Zellen;301305;4038338027860;301305;6/V15H mit Stecker;12;0;0;0;0;7.2;15;Ni-MH;BLACKFOX;BLISTER;16.02;ST;1;0;CMOS Batterie;6er Reihe aus V15H;85075000;CHI
Akkus;Backup - Akkus;Backup Zellen;301306;4038338028973;301306;6/V40H mit Stecker;20;0;0;0;0;7.2;40;Ni-MH;CONNECT;;17.28;ST;1;0;CMOS Batterie;6er Reihe aus V40H;85075000;CHI
Akkus;Bleiakkus - AGM;CTM CT Standard;303272;;CT2.6-12;CT2.6-12 für Waeco PowerVac;823;70;48;100;0;12;2600;Pb;CTM;;12.58;ST;1;0;CTM CT2.6-12 Ersatzakku für Waeco PowerVac;Handstaubsauger;85072020;CHI
Akkus;Bleiakkus - AGM;EXIDE Marathon L;301007;;8544122055;Marathon L2V220;16.000;209;136;283;0;2;220000;Pb;EXIDE;;759.99;ST;1;0;Exide Marathon L2V220;Anschluss M-M8;85072020;PT
Akkus;Bleiakkus - AGM;EXIDE Marathon L;123209;;8544311155;Marathon L6V110;23.000;272;166;190;0;6;112000;Pb;EXIDE;;108.7;ST;1;0;Exide Marathon L6V110;Anschluss M-M8;85072020;PT
Akkus;Bleiakkus - AGM;EXIDE Marathon L;300399;;8544601555;Marathon L12V15;6.500;181;76;167;167;12;14000;Pb;EXIDE;;52.82;ST;1;0;Exide Marathon L12V15;Anschluss M-M6;85072020;PT
Akkus;Bleiakkus - AGM;EXIDE Marathon L;124906;;8544602455;Marathon L12V24;9.500;168;127;174;0;12;23500;Pb;EXIDE;;57.25;ST;1;0;Exide Marathon L12V24;Anschluss M-M6;85072020;PT
Akkus;Bleiakkus - AGM;EXIDE Marathon L;118107;;8544603355;Marathon L12V32;13.500;198;168;175;0;12;31500;Pb;EXIDE;;74.12;ST;1;0;Exide Marathon L12V32;Anschluss M-M6;85072020;PT
Akkus;Bleiakkus - AGM;EXIDE Marathon L;305138;;NALL020270HM0FA;Marathon L2V270;16.000;209;136;283;0;2;270000;Pb;EXIDE;;759.99;ST;1;0;Exide Marathon L2V270;Anschluss M-M8;85072020;PT
Akkus;Bleiakkus - AGM;EXIDE Marathon L;305139;;NALL020320HM0FA;Marathon L2V320;16.000;209;202;283;0;2;320000;Pb;EXIDE;;759.99;ST;1;0;Exide Marathon L2V320;Anschluss M-M8;85072020;PT
Akkus;Bleiakkus - AGM;EXIDE Marathon L;304293;;NALL020375HM0FA;Marathon L2V375;16.000;209;202;283;0;2;375000;Pb;EXIDE;;759.99;ST;1;0;Exide Marathon L2V375;Anschluss M-M8;85072020;PT
Akkus;Bleiakkus - AGM;EXIDE Marathon L;304371;;NALL020425HM0FA;Marathon L2V425;16.000;209;202;283;0;2;425000;Pb;EXIDE;;759.99;ST;1;0;Exide Marathon L2V425;Anschluss M-M8;85072020;PT
Akkus;Bleiakkus - AGM;EXIDE Marathon L;304401;;NALL020470HM0FA;Marathon L2V470;16.000;209;202;283;0;2;470000;Pb;EXIDE;;759.99;ST;1;0;Exide Marathon L2V470;Anschluss M-M8;85072020;PT
Akkus;Bleiakkus - AGM;EXIDE Marathon L;304402;;NALL020520HM0FA;Marathon L2V520;16.000;209;202;283;0;2;575000;Pb;EXIDE;;759.99;ST;1;0;Exide Marathon L2V575;Anschluss M-M8;85072020;PT
Akkus;Bleiakkus - AGM;EXIDE Marathon L;304403;;NALL020575HM0FA;Marathon L2V575;16.000;209;202;283;0;2;520000;Pb;EXIDE;;759.99;ST;1;0;Exide Marathon L2V520;Anschluss M-M8;85072020;PT

Re: create staging table

Anonymous — Tue, 19 Mar 2019 09:00:46 GMT

Hi,

This looks fine. But in previous screen shot for tDBOutput, I could see that Table name is empty. Could you please add table name and show us the results? Also please share a latest screen shot of this component.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: create staging table

Anonymous — Tue, 19 Mar 2019 09:11:42 GMT

Re: create staging table

manodwhb — Tue, 19 Mar 2019 09:15:41 GMT

@apit ,you should specify the schema name also. why schema is empty?

Re: create staging table

Anonymous — Tue, 19 Mar 2019 09:34:55 GMT

Good catch @manodwhb !

@apit , Could you please create a metadata connection for your target table? You can then drag and drop the metadata and select tDBOutput. In this way you will make sure that the DB connection is correct.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: create staging table

Anonymous — Tue, 19 Mar 2019 10:06:45 GMT

@manodwhbbecause I have no idea for the moment what would be the right schem.

@nthampithis is what I did.

Re: create staging table

manodwhb — Tue, 19 Mar 2019 10:10:04 GMT

@apit ,since you need to connect MSSQL sever from outside and verify the schema.check below links.

SELECT * FROM sys.schemas

https://stackoverflow.com/questions/226042/how-do-i-query-if-a-database-schema-exists

http://www.informit.com/articles/article.aspx?p=1216889&seqNum=2

Re: create staging table

Anonymous — Tue, 19 Mar 2019 10:14:27 GMT

@manodwhbthank you!

this is what I get - can you help me?

Re: create staging table

manodwhb — Tue, 19 Mar 2019 10:42:12 GMT

@apit ,which schema you want to use to load that table or are you going to create a new table?

Re: create staging table

Anonymous — Tue, 19 Mar 2019 10:46:51 GMT

@manodwhbwill be a new one

Re: create staging table

Anonymous — Tue, 19 Mar 2019 10:47:16 GMT

@apit

Schema name, Table name and data structure (input data schema) are some of the mandatory information while creating a table. So if any of this information is missing or not correct, you will not be able to load data to an existing table or create any new table. If you are creating any new table, make sure that length values should be populated for all String components.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂