Problem Statement : How to load CSV file data into SQL Server using Talend

Solution :

Loading data from CSV most important task in every ETL tool.

For doing this in Talend you need to use following components :

• tFileInputDelimited

• tDBOutput

• Metadata -> DB Connections (optional)

• Metadata -> File delimited (optional)

It is good practice to create Metadata for database connections and retrive schema so that you can reuse it multiple times.Otherwise it is optional. You can give connection details in the component every time you need to connect to database. 

Load CSV Talend Job

STEPS:

1. Right click Metadata -> File delimited -> New Delimited File. Enter Name, Purpose, Description and click Next.
2. Browse File path. Select correct format. File viewer below will show content of file. click Next.
3. Change Field separator from default Semicolon to Comma. Select appropriate Row separator.
4. Tick 'Set heading row as column names' if you have column names in your CSV file and press Refresh Preview and check the data. Press Next.
5. Check the schema and press Finish.
6. Similarly create new DB connection for SQL Server. Right Click Metadata -> DB Connections -> Create Connection. Enter Name, Purpose, Description and click Next.
7. Choose DB type as Microsoft SQL Server and fill all the connection details. Test connection and Finish.
8. Now you have both Source and Target connections ready. Create a new job and drag the CSV file you just added under Metadata section.
9. Select tFileInputDelimited component double click and cross check all the properties i.e File Path, Schema etc
10. Similarly drag SQL Server connection and select tDBOutput from component list. Check connection details, Schema, mention Table name, select Action on table as per requirement.
11. Right click tFileInputDelimited -> Row -> Main and grag it on tDBOutput component.
12. Run the job.