Automate Every Day Errands

There is no office in the world without at least one business process that requires importing data present in the text format into the corporate ERP system. Often it is quite annoying. Let’s look can it be done fast and easy.

Data Source - List of Cities

There is plain text file with data assembled in CSV (Character Separated Values) form. Format is very practical as user can read it with Notepad in Windows OS or many other text readers on other operational systems. We will use sample CSV file publicly available at Internet. Here is the content fragment 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

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

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

Business Requirement

Expectations from my employer are to make sure our data is synchronized and consistent with its source. It is daunting and tedious to put daily efforts to check particular Internet resource for its updates. Better approach would be to work with some of existing automation solutions. Prior to decision what tool to use I recognized my everyday steps:

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

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

Automation Plan

Better approach for may daily routine is engaging machine and suitable software. Here is step-by-step guide how to Automate work with ByteChef platform. User interface allows you to register, create automation project and start building automation workflow. Follow this steps:

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

ByteChef Self-Hosted Automation Configuration

• Sign In to the platform
• Create new Project with name My Daily Routines and description Daunting and scarry errands I have to do every day
• Rename automatically created New Workflow workflow to Import CSV to Databaseand give it description Import cities.csv file content into PostgreSQL DBMS
• use Filesystem component to model physical file
• use CSV component to access data as collection of lines and columns
• use LOOP flow component loop over each line
• use PostgreSQL component to insert data to database table
• optionally use Log component to trace flow

Running Project and Testing Results

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

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

Resources Used in This Article

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

Conclusion

By following these tips, you can write an effective blog post that engages your readers and helps you to share your ideas and expertise with the world.