Creating CSV file and emailing

I currently create a PDF report and email it. Customer would like a CSV/Excel file instead. Best way of accomplishing this is ? Any examples available?

1 Like

Hi @fred-michael

The JourneyApps runtime has a built-in CSV library which makes creating and reading CSV files in the app really simple. Please have a look here for reference documentation. You can create the CSV file in the app, save it as an Attachment to the DB and send the customer a link to the attachment using the Attachment URL.

However, since you mentioned email, let’s assume you don’t want to save the CSV file in the DB but rather create it in memory in CloudCode and then send it as an attachment to an email. In that case, you can use the below example


const sgMail = require("@sendgrid/mail");
const { convertArrayToCSV } = require('convert-array-to-csv');

// To send email, a SendGrid API key is required
// A "sendgrid_api_key" environment variable must be set for the deployment
sgMail.setApiKey(process.env.sendgrid_api_key);

export async function run() {
    // OPTION 1: WORKING WITH an ARRAY of ARRAYS
    var header = ['number', 'first', 'last', 'handle'];
    var dataArrays = [
        [1, 'Mark', 'Otto', '@mdo'],
        [2, 'Jacob', 'Thornton', '@fat'],
        [3, 'Tielman', 'the Bird', '@twitter']
    ];

    var csv = convertArrayToCSV(dataArrays, {
        header,
        separator: ','
    });

    // OPTION 2: WORKING WITH an ARRAY of OBJECTS
    const dataObjects = [
        {
            number: 1,
            first: 'Mark',
            last: 'Otto',
            handle: '@mdo',
        },
        {
            number: 2,
            first: 'Jacob',
            last: 'Thornton',
            handle: '@fat',
        },
        {
            number: 3,
            first: 'Larry',
            last: 'the Bird',
            handle: '@twitter',
        },
    ];

    var csv = convertArrayToCSV(dataObjects);

    // SEND EMAIL
    const email = {
        from: "tielman@journeyapps.com",
        to: "tielman@journeyapps.com",
        subject: "CSV report sample email",
        text: "Plain email",
        attachments: [
            {
                content: Buffer.from(csv).toString('base64'),
                filename: 'test.csv',
                type: 'text/csv',
                disposition: 'attachment',
                content_id: 'csv'
            }
        ]
    };
    try {
        await sgMail.send(email);
    } catch (error) {
        // This helps for debugging
        if (error.response) {
            console.error(error.response.body);
        }
        throw error;
    }
    
    // OPTIONALLY SAVE CSV TO DB
    var csvAttachment = await Attachment.create({filename: 'test.csv', text: csv})
    let obj = DB.some_object.create();
    obj.attachment_field = csvAttachment;
    await obj.save();
}

PS. There are lots of very powerful CSV libraries available, but most of them work with streams and assume direct access to the file system, which we don’t really have in CC - also I hate streams - which is why I kinda like this lightweight one.

I hope this helps