Talend Connect
Virtual Summit
JOIN US!
And visit the Customer
& Community Lounge.
May 27-28, wherever you are.

CSV to SQlite db

Highlighted
Five Stars

CSV to SQlite db

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.

Highlighted
Eleven Stars

Re: CSV to SQlite db

 

 

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)

 

Talend.JPG

Regards
Abhishek KUMAR
Highlighted
Five Stars

Re: CSV to SQlite db

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

Highlighted
Eleven Stars

Re: CSV to SQlite db

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

Regards
Abhishek KUMAR
Highlighted
Five Stars

Re: CSV to SQlite db

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 ?

Highlighted
Eleven Stars

Re: CSV to SQlite db

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 )

Regards
Abhishek KUMAR

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog