Manipulating excel files and writing text files sequentially

Questions
1

Hi, I discovered n8n this week and I have become a ultra-fan of it.

Couple of questions:

  1. I’ve seen the excel writer seems to ask credentials, so I guess it’s excel cloud. How do I manipulate cells in an Excel file written on disk (ie: without credentials, just tell the file path on the host)?

Examples:

  • Open file and add a row at the end of the written text and set values “abc” in column 1 and “xyz” in column 2. Close the file.
  • Open the file, take the value of cell C4, append the word “hey!” and write the result in cell H8. Close the file.
  1. I’ve seen there are binary file writers. But, how do I append text-lines to a text file?

Examples:

  • Open the file and append “result = success. Done on 2022-09-06 10:43 CEST”. Close the file.
2

Hi @xmontero, welcome to the community!

  1. I’ve seen the excel writer seems to ask credentials, so I guess it’s excel cloud. How do I manipulate cells in an Excel file written on disk (ie: without credentials, just tell the file path on the host)?

To create Excel files rather than Excel online documents you’d probably want to use the Spreadsheet File node. This node expects incoming binary data (a file), which you could read from your local filesystem using the Read Binary File node in a previous step.

This node can read all the data from your sheet and convert it into n8n items which you can then manipulate in n8n as needed. You can then save your data in a file using another Spreadsheet file node and finally write it to your local filesystem using the Write Binary File node (or instead upload it somewhere, send it out via email, etc.).

  1. I’ve seen there are binary file writers. But, how do I append text-lines to a text file?

n8n doesn’t have a concept of plain text. Instead, it works with a specific data structure. This means, you could read your text file into a field of your choice which you can then manipulate. So, to first read a text file, and then append a line to the content, you could do something like this:

This would download a random txt file, convert it into JSON item n8n can work with, append a line and then write the result back into a file:

image
image1320×419 32.1 KB

Hope this helps!

3

Hi @MutedJam (Tom), thanks for your fast reply.

I understand, on both cases you read the whole “binary object” in memory, process, and write.

For the binary excel file, I understand and agree.

For the text file, I understand and will initially use like this. Nevertheless, I think there’s room for improvement:

Imagine a log file 1 GB long. Filesystems (and javascript via the filesystems) give options to “append lines” to files. Noone working purely on a bash script would “open the 1GB file” + “add the line in RAM” + “write the whole file”. Everyone would do a echo ${WHATEVER} >> ${FILE_NAME} and the only data object seen by our script is ${WHATEVER}.

Let’s imagine this:

  • For Google Drive we have a node that is able to do any operation on there. But if there was not this node, playing both with the HTTP webhook node + HTTP request node we can model “anything” that is based on REST.

Could we implement a couple of “filesystem nodes” that act as a “proxy” to the basic i/o operations in the filesystem?

One trigger node (maybe named “Filesystem Trigger”) that simulates any kind of stat() we can do to the underlying filesystem (change permissions, rename, deletion, update of a directory or file, etc.)

The other node would be some kind of a “filesystem proxy” (maybe named “Filesystem”) able to work with entities “Folder”, “File”, “Symlink” and do basic operations like “Create”, “Delete”, “Change owner”, “Change permissions” and for the files “Open for reading binary”, “Open for reading text”, “Open for writing binary”, “Open for writing text”, “Open for appending binary”, “Open for appending text”, “Seek” (to place the cursor somewhere like the C function fseek(), “Read”; “Write”, etc.

I have only had to spend 48h to understand that n8n is “the future”, I’ve started to love it and I think we alltogether should make it the “de facto automation standard” in any company in the world.

I deeply think those 2 nodes would greatly enhance the n8n power and would always be used as a “fallback”, ie: equivalently to the “http webhook” + “http request” => ie: When there’s not a specific node that does what you want.

Does it make any kind of sense?

4

Imagine a log file 1 GB long. Filesystems (and javascript via the filesystems) give options to “append lines” to files. Noone working purely on a bash script would “open the 1GB file” + “add the line in RAM” + “write the whole file”. Everyone would do a echo ${WHATEVER} >> ${FILE_NAME} and the only data object seen by our script is ${WHATEVER}.

I didn’t know this is the kind of file you had in mind, but you have a very good point here. n8n would keep datasets in memory during the workflow execution, so you wouldn’t want to manipulate large log files with it. If you’re working with local files you can consider simply using the Execute Command node instead which would let you do echo foo >> myfile.txt like so:

With other data stores this is obviously less of a problem (thinking of databases for example you wouldn’t need to read all records to simply insert a new one), so no need for workarounds here.

Could we implement a couple of “filesystem nodes” that act as a “proxy” to the basic i/o operations in the filesystem?
One trigger node (maybe named “Filesystem Trigger”) that simulates any kind of stat() we can do to the underlying filesystem (change permissions, rename, deletion, update of a directory or file, etc.)

So n8n does have a Local File Trigger node which might suit your needs.

If you’re missing something your could still implement your own polling logic if needed, by combining an Interval or Cron node with the Execute Command node querying the information you’re looking for (and possibly compare it in a later node with a previously stored value in order to identify changes if needed).

I deeply think those 2 nodes would greatly enhance the n8n power and would always be used as a “fallback”, ie: equivalently to the “http webhook” + “http request” => ie: When there’s not a specific node that does what you want.

I’m with you here - I am using these generic nodes a lot. In addition to the Execute Command node mentioned above (using which you can call any binary on your host) there’s also the Function and Function Item nodes that let your run JS code right inside n8n.

Hope this makes sense and helps! Let me know if you have any questions on the points above.

5

Thanks a lot. Will use the “execute command” with the >> inside.

And if we can contribute making new nodes, or helping in existing ones, I’m open.

I usually help startup companies to pivot fast, and n8n was the piece I was missing. I am expecting to heavily connect n8n to Mautic, Wordpress, Strapi to make “business models” fast and furious.

I want people I help to be able to create an idea and in less than a week having a business online making money. With frontend (Wordpress), data (Strapi), leads and sells (Mautic) and a bit of traffic via AdWords or FBAds it was possible but as soon as leads came in, we all struggled at “processing”.

The missing piece was connecting all together “automatically” without vendor lock-in.

So in short, will try that solution and here I am, from now on part of this community, to help and contribute to make this project make it shine.

1 Like
6

Sweet, I am glad to hear this works for you!

The easiest way to add new nodes btw would be to publish them as a community node. There is in fact a text manipulation node built by @lublak which makes working with plain text a lot easier (though I believe it would also read the full file in memory right now, so might not really help for this exact use case).

7

@MutedJam I’m always open fot a feature request :stuck_out_tongue:
And yes currently I read the complete file.

1 Like