Toolbox CSV Parser

Introduction to Toolbox CSV Parser

The functionality of the CSV Parser is very simple, but it can be handy when you need to process CSV data from different sources. It elevates all the dirty work of parsing CSV-formatted text, which makes your life easier and scripts cleaner.

Features

  • Parse CSV text into an array of nested arrays of strings. Each nested array corresponding to a row in the CSV file.
  • Parse CSV text with existing headers into an array of JS objects.
  • Parse CSV text with custom headers into an array of JS objects.
  • Override existing headers with custom ones, and limit result fields if needed.

Usage

Parse CSV-formatted text into an array of nested arrays of strings

In this example, we have simple CSV-formatted text. We can use the parse method of the csv object from the toolbox to parse it.

    const csvText = `
        id,name,age
        1,John,25
        2,Jane,30`;

    const parsed = ctx.toolbox.csv.parse({ data: csvText });

    console.log("Parsed Text: " + parsed);

In the output, we get an array described earlier.

    Parsed Text: [['id', 'name', 'age'], ['1', 'John', '25'], ['2', 'Jane', '30']]

And now we can process it as we want.

Parse CSV-formatted text into an array of JS objects, using existing headers

If we want to parse CSV formatted text to objects, we have several cases:

  • CSV has own headers
  • CSV has no headers
  • CSV has headers, but we do not need all of them in the result objects

In this case, we can parse CSV into an array of JS objects using the parseToObj method. It accepts the Dictionary as a parameter. Dictionary can have a few properties:

property type description example
data string CSV text '1,John,Doe'
headers array of strings the custom headers of the CSV file ['id', 'first name', 'last name']
overrideHeaders boolean is the first row needs to be overridden ether true or false

CSV has own headers

Example:

    const csvText = `
    id,name,age,createdAt
    1,John,25,2020-01-01T00:00:00.000Z
    2,Jane,30,2020-01-01T00:00:00.000Z`;

    const parsed = ctx.toolbox.csv.parseToObj({ data: csvText });

    console.log("First parsed Object: " + parsed[0]);   // { id: '1', name: 'John', age: '25', createdAt: '2020-01-01T00:00:00.000Z' }
    console.log("Second parsed Object: " + parsed[1]);  // { id: '2', name: 'Jane', age: '30', createdAt: '2020-01-01T00:00:00.000Z' }

CSV text has no headers

But if we do not have headers in CSV text, we can use the parseToObj, and provide a custom headers.

Example:

    const csvText = `
    1,John,25,2020-01-01T00:00:00.000Z
    2,Jane,30,2020-01-01T00:00:00.000Z`;

    const parsed = ctx.toolbox.csv.parseToObj({ data: csvText, headers: ['id', 'name', 'age', 'createdAt'] });

    console.log("First parsed Object: " + parsed[0]);   // { id: '1', name: 'John', age: '25', createdAt: '2020-01-01T00:00:00.000Z' }
    console.log("Second parsed Object: " + parsed[1]);  // { id: '2', name: 'Jane', age: '30', createdAt: '2020-01-01T00:00:00.000Z' }

Also, you can skip columns if they are in the middle of the sequence, by providing empty string or null as header name. It can be useful if you don`t want to include some columns from the CSV file into your objects, for whatever reason.

    const csvText = `
    1,John,25,2020-01-01T00:00:00.000Z
    2,Jane,30,2020-01-01T00:00:00.000Z`;

    const parsed = ctx.toolbox.csv.parseToObj({ data: csvText, headers: ['id', 'name', '', 'createdAt'] });

    console.log("First parsed Object: " + parsed[0]); // { id: '1', name: 'John', createdAt: '2020-01-01T00:00:00.000Z' }

CSV has headers, but we do not need all of them in the result objects

In some cases we do not need all fields from CSV file, so we can override headers and limit result fields.

    const csvText = `
    id,name,age,createdAt
    1,John,25,2020-01-01T00:00:00.000Z
    2,Jane,30,2020-01-01T00:00:00.000Z`;

    // All headers after `name` will be skipped.
    const parsed = ctx.toolbox.csv.parseToObj({ data: csvText, headers: ['id', 'name'], overrideHeaders: true });

    console.log("First parsed Object: " + parsed[0]);   // { id: '1', name: 'John' }
    console.log("Second parsed Object: " + parsed[1]);  // { id: '2', name: 'Jane' }

Using those techniques, you can construct objects with a different structure from the same CSV file.

    const csvText = `
    id,name,age,country,city,street,createdAt
    1,John,25,USA,New York,123 Main St,2020-01-01T00:00:00.000Z
    2,Jane,30,USA,Washington,456 Main St,2020-01-01T00:00:00.000Z`;

    const userHeaders = ['id', 'name', 'age', '', '', '', 'createdAt'];
    const users = ctx.toolbox.csv.parseToObj({ data: csvText, headers: userHeaders, overrideHeaders: true });

    const addressHeaders = ['userId', null, null, 'country', 'city', 'street'];
    const addresses = ctx.toolbox.csv.parseToObj({ data: csvText, headers: addressHeaders, overrideHeaders: true }); 

    console.log("Users: " + users);
    // Users: 
    //      [ { id: '1', name: 'John', age: '25', createdAt: '2020-01-01T00:00:00.000Z' },
    //      { id: '2', name: 'Jane', age: '30', createdAt: '2020-01-01T00:00:00.000Z' } ]
    
    console.log("Addresses: " + addresses);
    // Addresses: 
    //      [ { userId: '1', country: 'USA', city: 'New York', street: '123 Main St' },
    //      { userId: '2', country: 'USA', city: 'Washington', street: '456 Main St' } ]

Conclusion

In this article, we have seen how to use the CSV Parser in Rule Engine. Feel free to explore the toolbox and find more useful tools for your automation needs.