Extracting spreadsheet from blob column in MySQL database

Questions
1

Hi,

I have a MySQL table that contains spreadsheet files that are stored in a blob column - I’m struggling to see how I can extract this spreadsheet file from the relevant column and write it to a file.

Is this feasible?

Thanks
Scott

2

Hi @scottjscott,

In theory is it not just a select from the database then using the spreadsheet file node to write the data out maybe with a little function in the middle to do any changes.

3

Hey @scottjscott,

Welcome to the community :sparkling_heart:

It is possible, but you would have to make the blob readable by n8n. As @Jon mentioned, you can use a Function node in your workflow to do the manipulation.

4

Thanks both for the advice so far. It’s the function to do the manipulation that is eluding me.

You can see the nodes in my workflow here which all run, but they produce a file that excel won’t open, clearly because I need to use a function to convert the data coming from the database blob into something that n8n can read.

image
image2108×548 54.7 KB

I suspect my Set node needs to be replaced with a function, but I lack the javascript knowledge to write such a function.

Here is my workflow - I’m sure I’m making a bunch of noob mistakes!

5

Hey @scottjscott,

Unfortunately I don’t have a lot of experience with blobs and I don’t have dummy data to find out solutions. However, doing some quick search on the internet I found this: Blob.text() - Web APIs | MDN

Can you try this method?

6

When you query the blob field you have to use the TO_BASE64 function. Then, using the binary data base64 encoded you can create the binary structure in n8n using a function node.

SELECT `id`,`name`, TO_BASE64(content) FROM `db`.`upload`

The function node should be some similar to:

const results = []

for (const item of items) {
//Here you use the name of blob field.
//I used 'binaryfield' as an example.
  binaryData = items.json.binaryfield
  
  delete items.json.binaryfield
  
  results.push({
    json: {
      ...item.json,
    },
    binary: {
      data: {
        data: binaryData,
        mimeType: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' ,
        fileExtension: 'xlsx',
      }
    }
  })
}

return results;
3 Likes
7

Thanks for the pointers @RicardoE105 - I will do a bit of RTFM on the javascript function :sweat_smile:

8

Quick update, I’ve managed to get things working without a function with the following nodes:

image
image2108×548 58.6 KB

  1. MySQL node:
    The key guidance here was using the TO_BASE64(content) in the SQL statement, but also by adding TO_BASE64(content) as binaryfield as follows so that the variable is available to other nodes:
SELECT TO_BASE64(content) as binaryfield FROM TABLE
  1. Move Binary Data node
    This node moves the data from JSON to Binary, but only works if I enable the Data is Base64 from the options as circled below:
    image
    image1920×890 48.7 KB

Then it was just a simple case of writing binary file with the final node.

The final workflow is below - I’ll have to fumble my way through a javascript function at some point in the future! Thanks to everybody for their help.

2 Likes
9

Ahh, good to know. Actually, I learn that you could do this 1 hour ago from @jan. I had no clue about that. This is one of the many things I like about n8n. I have been working with it for over a year and keep discovering new things.

@scottjscott have fun, and thanks for sharing the solution with the community.

1 Like