Unstructred Text Data into Structured using Bold ETL

In this article, we have imported the tables from the PostgreSQL server, converted unstructured text data to structured data using Transformation, and moved data into the destination DB using Bold ETL. Follow the step-by-step process below.

Consider data in the PostgreSQL table as follows:

Using Bold ETL, convert this data as follows:

Step 1: Click on the Bold ETL icon to open the Bold ETL site in a new tab.

Step 2: Click Add Project in the left-side panel. Enter the project name and click the tick icon.

Step 3: Click on Project name. It will open yaml editor to configure the source and destination connector configuration.

Step 4: Click PostgreSQL on the left-side panel and add the template in the right-side panel to add the sample configuration in the YAML editor. Replace your DB credentials and tables as shown below:

Step 5: Click Save and choose the destination. Then, click the Yes button.

Step 6: Go to Schedules and select Run Now option in the context menu of the data source grid.

Step 7: Logs will be available in the Logs tab. Click the project name in the left side panel and switch to the Logs tab.

Step 8: Go to the Transformation tab and click Add Table. Enter the table name to be moved into the destination database.

Step 9: Tables will be moved into the destination schema named as Project Name. For example, if the project name was postgresdata, it will be moved to the postgresdata schema.

Step10: Enter the SQL scripts to apply transformations on existing tables and create the SQL scripts into a new table in the destination database.

This SQL query (DuckDB) will be converted into a structured format as follows. Click Preview to preview the random 10 records and Save.

Step 11: Repeat Step 4 for on-demand refresh by selecting Run Now, or go to the Schedules page and select the Schedule option in the context menu of the data source grid. Configure the schedule and click Run.

Step 12: As per the schedule, transform tables and source tables are moved into the destination database, and a data source is created with a random table in Bold Reports.

Step13: Go to the Dataset page and create a dataset with the existing data source created in the previous step.

Refer: Working with ETL