Automate CSV Imports

There is no office in the world without at least one business process that requires importing text data into the corporate ERP system. It is often quite annoying. Let's see if it can be done quickly and easily.

Data Source - List of Cities

A plain text file with data is assembled in CSV (Comma Separated Values) format. This format is very practical, as a user can read it with Notepad on Windows or many other text editors on other operating systems. We will use a sample CSV file publicly available on the internet. Here is a fragment of the content in the CSV file:

"LatD", "LatM", "LatS",     "NS", "LonD", "LonM", "LonS",  "EW",  "City",           "State"
   41,    5,      59,       "N",     80,   39,       0,     "W",  "Youngstown",       OH
   42,   52,      48,       "N",     97,   23,       23,    "W",  "Yankton",          SD
   46,   35,      59,       "N",    120,   30,       36,    "W",  "Yakima",           WA
   42,   16,      12,       "N",     71,   48,       0,     "W",  "Worcester",        MA
   43,   37,      48,       "N",     89,   46,       11,    "W",  "Wisconsin Dells",  WI

Data Destination - Database Table

The intention of this business goal is to pick only a subset of the data present in the CSV and insert it into a database table. This guide targets city names and corresponding country names. Our business relies on a PostgreSQL DBMS that contains a CITY table. The table is described with this DDL statement:

CREATE TABLE CITY(
    id SERIAL PRIMARY KEY,
    name varchar NOT NULL,
    country varchar NOT NULL
);

Business Requirement

The expectations from my employer are to make sure our data is synchronized and consistent with its source. It is daunting and tedious to put in daily effort to check a particular internet resource for updates. A better approach would be to work with an existing automation solution. Prior to deciding what tool to use, I identified my everyday steps:

• obtain the physical CSV file from the internet
• place the physical CSV file on the local file system
• open the file with a text reader or editor (Word, Notepad, TextEdit, Sublime)
• scan the content
• insert new records into the CITY table
• update existing records if there is any change in the internet source

It is quite easy to manage 20 records of data, but if the file has more than that, expect issues with focus, concentration, and willpower. Please do not forget that a business grows and changes. Soon there might be new requirements, maybe to exclude particular cities or to enrich your ERP with details about customers in those cities.

Automation Plan

A better approach for my daily routine is engaging a machine and suitable software. Here is a step-by-step guide on how to automate work with the ByteChef platform. The user interface allows you to register, create an automation project, and start building an automation workflow. Follow these steps:

• obtain the physical CSV file from the internet
• place the physical CSV file on the local file system
• make sure the absolute path to that file is accessible to the ByteChef platform

ByteChef Self-Hosted Automation Configuration

• Sign in to the platform

• Create a new project with the name My Daily Routines and the description Daunting and scary errands I have to do every day

• Rename the automatically created New Workflow to Import CSV to Database and give it the description Import cities.csv file content into PostgreSQL DBMS

• use the Filesystem component to model the physical file

• use the CSV component to access data as a collection of lines and columns

• use the Loop flow component to iterate over each line

• use the PostgreSQL component to insert data into the database table

• optionally use the Log component to trace the flow

Running Project and Testing Results

• Create a Project Instance
• Pick a project version
• Enable a particular workflow
• Enable the Project Instance
• Run the Project Instance
• Review the project execution history

The history lists the success status for each step performed by the executed workflow. The red background color alerts you about possible issues and errors. If there are no red entries in the list, we can check the data in the database.

Resources Used in This Article

• Download cities.csv
• JSON version of the ByteChef workflow: workflow.json

Conclusion

By following these steps, you can automate your tedious CSV data imports and let ByteChef handle the heavy lifting, saving you valuable time and effort.