Export device state to CSV > FTP

Help
,
1

I have a requirement to query device state data and export a CSV file to an external FTP server on a periodic basis. It must be fully automated and setup to operate on a schedule.

Can anyone think of different approaches?

2

Hi Paul,

Currently, there is not a direct way to do this with the FTP server from a workflow. Our next release will include a CSV node, so you will be able to use a timer to trigger export a CSV of state data. I would recommend using the HTTP node to send the CSV to a “middleman” that can communicate with the FTP server, since we do not have a way to do so directly.

Hopefully this helps!
Julia

3

Thanks. Do you now when the next release is scheduled?

4

Do you think an email attachment could be used as a middleman? There are online services available to automatically upload an email attachment to and FTP server. https://www.integromat.com/en/integration/329-automatically-upload-email-attachments-to-your-ftp-server

5

Hi Paul!

An email attachment would be a great solution. Our built-in email node uses SendGrid under the hood, and with an account you have a lot of control on how emails are sent from the workflow.

This forum post has more information: Feature request - email attachments?

Hopefully this helps,
Julia

6

@JuliaKempf would it be possible to generate the content of a Sendgrid attachment from within a workflow?

7

Hi @paul_wareham,

It will be very easy to generate the SendGrid attachment content with the CSV node. The content will be stored on the payload, and you will then be able to include it as an attachment to SendGrid in an HTTP Node.

I built an example, but used a Virtual Button with a CSV formatted string.

45%20PM
Screen Shot 2019-02-27 at 1.09.45 PM.png1202Ă—1138 114 KB

Then I make it Base64 friendly:

57%20PM
Screen Shot 2019-02-27 at 1.09.57 PM.png1234Ă—1120 87.7 KB

Then I use an HTTP Post to SendGrid:

14%20PM
Screen Shot 2019-02-27 at 1.12.14 PM.png1334Ă—1420 108 KB

35%20PM
Screen Shot 2019-02-27 at 1.11.35 PM.png1412Ă—1196 69.7 KB

Then my payload will reflect this request, and an email comes through with my CSV attached.

56%20PM
Screen Shot 2019-02-27 at 1.12.56 PM.png490Ă—594 19.7 KB

Hopefully this helps!
Julia

1 Like
8

@JuliaKempf thanks!

Any idea the timeline if the csv node?

9

Hi Paul,

That node is now released!

Thanks,
Julia

10

that’s pretty wicked! Can you rework your above example based on the csv node?

11

Hi Paul!

Below is the reworked workflow with the CSV node. The only other change (aside from the CSV node) is the addition of the Losant API node to get my device state data.

44%20AM
Screen Shot 2019-02-28 at 10.49.44 AM.png1212Ă—1454 120 KB

55%20AM
Screen Shot 2019-02-28 at 10.49.55 AM.png1212Ă—1518 133 KB

And I still receive my CSV attachment!
02%20AM

Thanks,
Julia

1 Like
12

That works!

Is there an easy way to get the actual device state data from the API node into an array format so the CSV node can operate on the individual attributes instead of the top level API result array?

13

Hi Paul,

I wrote a loop in a Function node to get to the attributes, but there are also other nodes that could have been used. Here is my Function node code:
56%20PM

And the CSV attachment that came to my inbox:

31%20PM
Screen Shot 2019-02-28 at 3.34.31 PM.png502Ă—982 59.9 KB

Hopefully this helps,
Julia

14

hi @JuliaKempf

I was thinking about this issue a bit more. I don’t like the idea of using a third party middleman to convert an email attachment to FTP. Too many steps to go wrong :wink:

Can you think of any other method, other than email attachments, to get csv data to an FTP server from a workflow?

Thanks

Paul

15

Hi @paul_wareham,

I think the best solution would be to leverage a third-party to perform the conversion. Here are some options I can think of:

  1. Cloud Functions: All of the cloud vendors (Google Cloud, AWS, Azure) support the ability to write serverless functions. This would allow you to create a small function that’s triggered by the workflow engine to communicate with FTP.

  2. Roll your own API: Because you can trigger any arbitrary HTTP endpoint in the Workflow Engine, you can create your own microservice that takes CSV data as input and communicates to your FTP Server.

I’m happy to provide more thoughts here if needed!

16

Those are good options.

If you could choose a direction, which path would you take? I’m leaning toward the cloud functions.

17

Same here. It removes the headache of you having to worry about where to host a new service. Best of all, we have an Azure Function Node and a AWS Lambda Node.

Both platforms support Node.js functions. There are many many npm modules you could choose from, but here is one that looks good:

I’d still recommend searching around, npm has a lot to offer.

18

Thanks @anaptfox that’s what I was thinking

Do Lamda functions have access to any NPM package;l or would I need to install it somewhere myself?

19

I’m not 100% sure, but it looks like this might help:

https://docs.aws.amazon.com/lambda/latest/dg/nodejs-create-deployment-pkg.html#nodejs-package-dependencies

20

@paul_wareham this thread is quite old but FYI …

We just released the FTP: Get and FTP: Put Nodes, which support establishing a connection through SFTP, FTPS, or FTP.

The nodes are available both in our cloud platform and in Edge Workflows.