create staging table

[Anonymous]

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

[Anonymous]

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 🙂

[Anonymous]

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 🙂

[Anonymous]

@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

[Anonymous]

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 🙂

[Anonymous]

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:

[manodwhb]

@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

[Anonymous]

@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?

[Anonymous]

@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 🙂

[Anonymous]

@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