Download an email attachment and add CSV contents to mySQL?

Describe the issue/error/question

Daily we receive an email from a supplier with a stock level price list. I’d like to automatically take the contents of the attached CSV file and add it all to a MySQL database. The attachment size is ~2Mb and contains almost 12,000 lines.

Please share the workflow

No workflow yet, not sure how to start.

Information on your n8n setup

  • n8n version: 0.174.0
  • Database you’re using (default: SQLite): SQLite
  • Running n8n with the execution process [own(default), main]: none
  • Running n8n via [Docker, npm, n8n.cloud, desktop app]: running in Unraid Docker

Yes, this is doable. I’m not great at the MySQL node, but the email and attached CSV shouldn’t be difficult to do. My recommendation is to let your email client filter the email from the provider into a folder, and then use the folder to call on the emails. You can use the archive feature to archive the email after n8n does the processing in your email client getting it ready for the next email from the supplier.

Thanks for the reply.

Sadly my first issue is signing into my email account. Its failed.

Yes, my credentials and server address are correct. Yes, I can log in to the web portal - correct credentials.

The mail server is Microsoft Office 365. Host address and port are also correct.

Hi there, have you tried the Microsoft Outlook node instead? I’ve had trouble with the Imap node as well but had success using the Gmail node instead.

I wasn’t aware of the Microsoft Outlook node. That worked, I was able to connect to my email account and Get All Messages.

And that leads to my next issue. How do I specify a folder location to get messages and how do I filter it for messages that match my criteria?

The documentation, in my personal opinion, is sparse and brief. Given my immediate roadblocks and resistance in making progress, I’m second-guessing my decision to use N8n.

I was able to complete the task in under 25 minutes using Integromat/Make. Though, I won’t be using Integromat/Make because it would consume 11,000 operations each time it imports the results from the daily spreadsheet.

I really need to do able to do this with n8n.

Do you think import function on SQLite would work in your case?

Thanks for your reply Semighoti.

I don’t see how SQLite will progress my issue.

Currently, I’m stuck unable to retrieve the email from a folder (IMAP).
My next step would be to download the attached CSV
Then iterate the records (11,000 of them) and insert into mySQL.

The MySQL function is something I’m familiar with, so I don’t see that as a problem.

I don’t have a solution for your issue, sorry. I wonder if you need getting the work done, importing the CSV by yourself without any automation could be the best solution for your problem.

I can import the price list manually, as I’ve been doing for weeks now. But I’m tired of doing this. n8n is automation software that is designed for this very use case.

So far I am thoroughly unimpressed with n8n! As I mentioned in my previous post, I was able to create this automation in under 25 minutes using Integromat. Doing the same thing with n8n shouldn’t be difficult.

Yet, so far I can’t even get past the very first step which is finding/downloading the email in an IMAP folder.

Hey @Michael_Pierce,

I am not in front of my instance at the moment but doesn’t the outlook node have a Folder Messages option? That should get all the messages from a folder, from there you could filter the messages once you have them with an If node.

I am not sure why the imap node was returning a login failure message a bit more digging would be needed.

1 Like

Hi Michael,

Sorry to hear you’re having issues. I don’t use Outlook, but I am able to do a lot using n8n and the gmail node to pull attachments and process them just like you’ve mentioned. On gmail the folders are called Labels. After looking at outlook I’m guessing it’s called categories. See attached image:


Once you have authenticated, do you have the Categories option? Once you select it, do you get a drop-down with all the folders on your instance?

Loaded up my instance on mobile, this is what I was referring to.

1 Like

Ah, I had the wrong resource :man_facepalming: Good catch

I thoroughly appreciate the assistance. I’ve made progress to the point I have the attachment.

Now, the next step is to somehow iterate all 11,000+ rows and add them to a MySQL table. … sooo… how do I do that? :slight_smile:

Logically, I would assume writing the file to disk is the next step.

In the Download Attachment node, I’ve called the Binary Property AttachmentData

In the node Write Binary File, I assume the Property Name must contain the binary data from AttachmentData
image

I’m not sure what to pass into the Property Name. Do I pass Binary or AttachmentData or something else? So far I get an error no matter what I chose.

Hey @Michael_Pierce,

For the property name you can just type in the binary property as it is so it would be AttachmentData but as you would need to read it again anyway unless you want a local copy or you are running a command line tool to import the data into MySQL you can just use the spreadsheet node and put in something like

image

From there as n8n automatically loops all the inputs (for most nodes) you can add your MySQL node and in theory all will be good.

1 Like

BRILLIANT!!!

image

1 Like

I’m 99% of the way there.

Part Number and Product Name fields are blank in my table. I know it has to do with the space in the name. With MySQL when working with a table name or column name with a space you enclose the value with an (apostrophe). emmm… it won’t show the raw apostrophe character here :slight_smile:

This is my SET

This is my MySQL. If I omit the apostrophe here, then the MySQL node throws an error.

Likewise, if I omit the apostrophe here, I also get an error.
image

100% working

I also had to add the apostrophe in the SET.

Final outcome is exactly what I need!

I thoroughly appreciate all the support and your patience while I worked through this.

2 Likes