topic Re: CSV to SQlite db in Talend Studio

CSV to SQlite db

Anonymous — Sat, 16 Nov 2024 06:25:24 GMT

I want to insert data from CSV file to Sqlite table with different table name on the basis of value that I get from CSV file. My file has 4 columns I want to use 2 of them to populate database.

1. MD5(32 byte) --> I want to split this into two and create tableName = first 3 byte and hash column remaining 29 byte

2. name --> store this as it is from csv.

Please help how can achieve this functionality, which is how to get value from csv attribute and use it as table name and remaining string to be passed as column value.

Re: CSV to SQlite db

akumar2301 — Tue, 05 Mar 2019 21:05:23 GMT

Input —>

tjavarow ( split md5 column in two - tablename and hashbyte) —>

tflowtoiterate( each column value becomes part of globalMap) —>

titeratetoflow ( map globalMap variable created before with columns to be mapped in table - hashbyte,name) --> tdboutput ( for table name ,use globalMap variable created by tflowtoiterate)

Re: CSV to SQlite db

Anonymous — Wed, 06 Mar 2019 11:47:15 GMT

Thank you Abhishek for the quick response. I was able to implement the solution as suggested by you. I dynamically got the tablename from global variable by using tflowtoiterate & titeratetoflow. However using this I am able to insert data in only 1 table. I want to insert data in multiple tables.

I will again explain my requirement.

I have 1 csv file example having 3 columns

File - Employee

Dept, EmpName, Sal

Finance, ABC, 4000

Admin, PQR, 3000

Tech, STU, 5000

Finance, KLJ, 3500

Tech, RTR, 6000

Finance, HJH, 4000

Admin, NBM, 8000

I want to populate data of these CSV in 1 SQlite database and multiple tables as per the department. From the above csv file I have to dynamically create 3 tables (Finance, Admin and Tech) and insert row in each of the tables dynamically as per the employee department

Output required

Table1 - Fin

Finance, ABC, 4000

Finance, KLJ, 3500

Finance, HJH, 4000

Table 2 - Adm

Admin, PQR, 3000

Admin, NBM, 8000

Table 3 - Tec

Tech, STU, 5000

Tech, RTR, 6000

Re: CSV to SQlite db

akumar2301 — Wed, 06 Mar 2019 12:07:58 GMT

instead of tIteratetoflow , can you try tFixedFlowInput and Assign GlobalMap Var similarly as you did for tIteratetoflow.

Re: CSV to SQlite db

Anonymous — Thu, 07 Mar 2019 11:51:04 GMT

Thanks again for responding quickly. I really appreciate your efforts.

The solution you suggested worked for me but now the performance is low, it is updating 4 rows/sec. Is there is a way to increase that ?

Re: CSV to SQlite db

akumar2301 — Thu, 07 Mar 2019 12:19:52 GMT

Instead of directly inserting in Databse , I would suggest to create separate temprory files( FileName as 3 char MD5 code).

then use tFileList , Load each file in tSqlLiteOutput (FileName w/o extn as output table name )