Possible bug (or I'm doing something wrong) using "Read binary files", "Read PDF", "Set", "Google Sheets". I'm getting the error "bad XRef entry" in two very weird situations

Hello!

n8n Version: 0.153.0 (Desktop App)

I’ve two similar workflows:

“Start” > “Read binary file” (or “Read binary files”) > “Read PDF” > “Set” > “Google Sheets”.

The intend is to monitor a local folder, read the PDF and append the data to Google Sheets.

I’ve placed 3 PDFs in the same folder, all of them are “Payment Receipt”, two of them have the same structure but with different “data”, like name, date of the payment, price, etc, the other one also has the same structure with different “data”, like name, date of the payment, price, etc, but also with a different keyword (like two of them are Debit Card and the other one is Credit Card).

When I execute the workflow to read the “Credit Card” pdf alone, It goes without any error, after that, If I try to execute the “Read Binary files”, I get no error in the “Credit Card” ones but “bad XRef entry” in the “Debit Card”, If I do the same but in a different order (first read the “Debit Card” alone) and them “Read PDF files”, I get the same error but only on the “Credit Card” ones.

Does anyone have an idea why this is happening?

If I wasn’t clear enough, please, let me know and I’ll try to explain in a better way.

Thank you for your help and attention!

I’m sorry for any inconvenience!

Happy new year! :slight_smile:

Can you please share the workflow? To do so, copy all the nodes and paste them here.

Sorry for the late reply!

Can you please share the workflow? To do so, copy all the nodes and paste them here.

Sure!

A few observations: I kept trying between yesterday and today and I keep getting different results, I was running the workflow in a folder with more than 30 PDFs, I reduced it to a folder with only 3 to make it easier to track the weird behavior.

What I’ve tried and how:

  1. Launch n8n.
  2. Run workflow #1.

Results:

First run: either nothing shows on Google Sheets or only the “Key” value shows up.
Second run: one or two outputs shows up on Google Sheets (with the weird behavior described in the topic).

  1. Launch n8n.
  2. Run workflow #2:

Results:

First run: either nothing shows on Google Sheets or only the “Key” value shows up.
Second run: one or two outputs shows up on Google Sheets (with the weird behavior described in the topic).
Third run: if in the previous run only one value showed up, now, usually another value shows up, but never all of them (all files in the folder).

Also, If possible, please, would you know why sometimes the file path parameter doesn’t accept the path as the way Windows format it?

For example, If I direct copy the file path using shift + left button + copy as path, I get this result: C:\Users\david\Desktop\TestePDF\File 03 - PIX.pdf

When I was setting up the file path for workflow #2, I had to replace the \ with / to make it work.

Workflow #1 (read only one file):

{
  "nodes": [
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "typeVersion": 1,
      "position": [
        240,
        400
      ]
    },
    {
      "parameters": {},
      "name": "Read PDF",
      "type": "n8n-nodes-base.readPDF",
      "position": [
        640,
        400
      ],
      "typeVersion": 1,
      "notesInFlow": false,
      "continueOnFail": true
    },
    {
      "parameters": {
        "authentication": "oAuth2",
        "operation": "append",
        "sheetId": "194kCrt8xecYScV02lAPgyygkHapLS0uOtHzOBPcdK3E",
        "range": "A:X",
        "options": {
          "valueInputMode": "RAW"
        }
      },
      "name": "Google Sheets",
      "type": "n8n-nodes-base.googleSheets",
      "typeVersion": 1,
      "position": [
        1020,
        400
      ],
      "retryOnFail": false,
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "3",
          "name": "Google Sheets account"
        }
      },
      "continueOnFail": true
    },
    {
      "parameters": {
        "fileSelector": "C:/Users/david/Desktop/TestePDF/*.pdf",
        "dataPropertyName": "=data"
      },
      "name": "Read Binary Files",
      "type": "n8n-nodes-base.readBinaryFiles",
      "typeVersion": 1,
      "position": [
        460,
        400
      ]
    },
    {
      "parameters": {
        "keepOnlySet": true,
        "values": {
          "number": [
            {
              "name": "Key"
            }
          ],
          "string": [
            {
              "name": "text",
              "value": "={{$node[\"Read PDF\"].json[\"text\"]}}"
            }
          ]
        },
        "options": {
          "dotNotation": true
        }
      },
      "name": "Set",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        840,
        400
      ],
      "continueOnFail": true
    }
  ],
  "connections": {
    "Start": {
      "main": [
        [
          {
            "node": "Read Binary Files",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Read PDF": {
      "main": [
        [
          {
            "node": "Set",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Read Binary Files": {
      "main": [
        [
          {
            "node": "Read PDF",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set": {
      "main": [
        [
          {
            "node": "Google Sheets",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

Workflow #2 (read all files in the same path):

{
  "nodes": [
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "typeVersion": 1,
      "position": [
        240,
        400
      ]
    },
    {
      "parameters": {},
      "name": "Read PDF",
      "type": "n8n-nodes-base.readPDF",
      "position": [
        640,
        400
      ],
      "typeVersion": 1,
      "notesInFlow": false,
      "continueOnFail": true
    },
    {
      "parameters": {
        "authentication": "oAuth2",
        "operation": "append",
        "sheetId": "194kCrt8xecYScV02lAPgyygkHapLS0uOtHzOBPcdK3E",
        "range": "A:X",
        "options": {
          "valueInputMode": "RAW"
        }
      },
      "name": "Google Sheets",
      "type": "n8n-nodes-base.googleSheets",
      "typeVersion": 1,
      "position": [
        1020,
        400
      ],
      "retryOnFail": false,
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "3",
          "name": "Google Sheets account"
        }
      },
      "continueOnFail": true
    },
    {
      "parameters": {
        "fileSelector": "C:/Users/david/Desktop/TestePDF/*.pdf",
        "dataPropertyName": "=data"
      },
      "name": "Read Binary Files",
      "type": "n8n-nodes-base.readBinaryFiles",
      "typeVersion": 1,
      "position": [
        460,
        400
      ]
    },
    {
      "parameters": {
        "keepOnlySet": true,
        "values": {
          "number": [
            {
              "name": "Key"
            }
          ],
          "string": [
            {
              "name": "text",
              "value": "={{$node[\"Read PDF\"].json[\"text\"]}}"
            }
          ]
        },
        "options": {
          "dotNotation": true
        }
      },
      "name": "Set",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        840,
        400
      ],
      "continueOnFail": true
    }
  ],
  "connections": {
    "Start": {
      "main": [
        [
          {
            "node": "Read Binary Files",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Read PDF": {
      "main": [
        [
          {
            "node": "Set",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Read Binary Files": {
      "main": [
        [
          {
            "node": "Read PDF",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set": {
      "main": [
        [
          {
            "node": "Google Sheets",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

Please let me know If you want me to provide more information.

Thank you for your help and attention!

I’m sorry for any inconvenience!

Hi @dcbn, I had a look at your workflow and paths on Windows can indeed be a pain (see for example Basic starting question: how to download a list of files, starting from URL? - #6 by MutedJam). We’re trying to avoid these of course, but things like this can slip through unfortunately.

That said, reading the files was working fine for me with both your workflow 1 and workflow 2 and a path like C:/Users/tom/Desktop/*.pdf when I was testing this on Windows, regardless of the number or order of PDF files I was reading (these were the test files I have created):

From reading this issue description it sounds the problem might be with your actual files which would explain why I can’t reproduce it.

So as next steps I’d suggest:

  1. Testing your workflow with PDF files from a different source to verify whether this is indeed a problem with your specific files.
  2. Narrow down the problem (e.g. based on your description the problem seems to occur before reaching Google Sheets, so verify this by looking at the data from each step and identifying the problematic part of your workflow)

Once done, it would be great if you could share a simplified version of your workflow only containing the problematic node(s) along with files using which the problem can be reproduced so we can take a closer look at these parts.

(I’m sorry that I deleted my previous reply, I accidentally hit the reply button when I was still organizing it.)

Hello!

Thank you for your reply!

Hi @dcbn, I had a look at your workflow and paths on Windows can indeed be a pain (see for example Basic starting question: how to download a list of files, starting from URL? - #6 by MutedJam ). We’re trying to avoid these of course, but things like this can slip through unfortunately.

I’ve read that topic before but I thought It could have already been fixed, that’s why I ended up commenting, I’m sorry.

I am almost sure that the problem could be the PDFs from my bank, but these are the things that make me feel confused:

  1. If I try to read them separately, they work, even If sometimes on the first (or second) try I get the “bad XRef entry”.
  2. When I execute the workflow to read more than one PDF after executing the workflow to read one PDF, they all show on Sheets except those with slightly different content.
  1. Testing your workflow with PDF files from a different source to verify whether this is indeed a problem with your specific files.

I tried to find any PDF that would be similar to the ones I’m trying to extract the data but found none in my possession, almost 100% of them are proof of payments. :confused:

  1. Narrow down the problem (e.g. based on your description the problem seems to occur before reaching Google Sheets, so verify this by looking at the data from each step and identifying the problematic part of your workflow)

You are right, every time I get an error, It’s from the “Read PDF node”, but even when no error is shown not all of the extracted data goes to Sheets.

Anyway, as I was unable (due to lack of knowledge) to properly format them, as I’m getting only plain text (and don’t know how to properly organize it), I started trying to use HTML extract instead, but, I still couldn’t find a proper software (that can be automated) to convert PDF to HTML with actual usable output, as most of them are either non-possible to automate or their CSS is polluted which leads to bad results.

(I mean, is it feasible to format all the “text” extracted from Read PDF node using a function or something similar?)

Do you think I can share with you or do you think would be better to create another topic showing what I already tried, what I want to achieve, and which problems I’m facing?

All I want to do is to:

  1. Monitor a local folder.
  2. New file added.
  3. Extract data from it.
  4. Upload (already formatted) to Sheets.

Thank you for your help and attention!

I’m sorry for any inconvenience.

Tbh, while I think the documents are the culprit here, a factor contributing to this could well be the specific library used for PDF parsing in n8n. It has not been updated for a few years now (there also is an open issue in our GitHub repo about this).

So what you might want to try out as an alternative is a specialized PDF parsing service like Docparser. They provide a REST API, so can easily be integrated with the HTTP Request node. I played around with it a bit, extracting the checks from https://www.commercebank.com/-/media/cb/pdf/personal/bank/statement_sample1.pdf and this appears to work reasonably well in n8n:

I am happy to share my workflow but I reckon it might not be very useful to you, assuming your bank statements look very different than my example. The kywarucinyst in my workflow refers to the parser ID I have created in Docparser:

Example Workflow
{
  "nodes": [
    {
      "parameters": {
        "authentication": "headerAuth",
        "url": "=https://api.docparser.com/v1/results/kywarucinyst/{{$json[\"data\"][\"id\"]}}",
        "options": {
          "splitIntoItems": true
        }
      },
      "name": "Fetch Data",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 1,
      "position": [
        900,
        300
      ],
      "credentials": {
        "httpHeaderAuth": {
          "id": "39",
          "name": "Docparser"
        }
      }
    },
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "typeVersion": 1,
      "position": [
        240,
        300
      ]
    },
    {
      "parameters": {
        "url": "https://www.commercebank.com/-/media/cb/pdf/personal/bank/statement_sample1.pdf",
        "responseFormat": "file",
        "options": {}
      },
      "name": "Download Statement",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 1,
      "position": [
        460,
        300
      ]
    },
    {
      "parameters": {
        "authentication": "headerAuth",
        "requestMethod": "POST",
        "url": "https://api.docparser.com/v1/document/upload/kywarucinyst",
        "responseFormat": "string",
        "jsonParameters": true,
        "options": {
          "bodyContentType": "multipart-form-data"
        },
        "sendBinaryData": true
      },
      "name": "Send to Docparser",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 1,
      "position": [
        680,
        300
      ],
      "credentials": {
        "httpHeaderAuth": {
          "id": "39",
          "name": "Docparser"
        }
      }
    },
    {
      "parameters": {
        "authentication": "headerAuth",
        "url": "=https://api.docparser.com/v1/results/kywarucinyst/{{$json[\"data\"][\"id\"]}}",
        "options": {
          "splitIntoItems": true
        }
      },
      "name": "Fetch Data",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 1,
      "position": [
        900,
        300
      ],
      "credentials": {
        "httpHeaderAuth": {
          "id": "39",
          "name": "Docparser"
        }
      }
    },
    {
      "parameters": {
        "functionCode": "const rows = items[0].json.field_1.split('\\n');\nlet results = [];\nfor (row of rows) {\n  const fields = row.split(/\\s+/);\n  results.push({\n    json: {\n      date_paid: fields[0],\n      check_number: fields[1],\n      amount: parseFloat(fields[2]),\n      reference_number: fields[3]\n    }\n  });\n}\nreturn results;"
      },
      "name": "Transform Result",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        1120,
        300
      ]
    }
  ],
  "connections": {
    "Fetch Data": {
      "main": [
        [
          {
            "node": "Transform Result",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Start": {
      "main": [
        [
          {
            "node": "Download Statement",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Download Statement": {
      "main": [
        [
          {
            "node": "Send to Docparser",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Send to Docparser": {
      "main": [
        [
          {
            "node": "Fetch Data",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

Hope this nevertheless provides some pointers to start with :slight_smile:

Hello!

I’m sorry for the late reply, really busy days.

Tbh, while I think the documents are the culprit here, a factor contributing to this could well be the specific library used for PDF parsing in n8n. It has not been updated for a few years now (there also is an open issue in our GitHub repo about this).

That’s fine.

So what you might want to try out as an alternative is a specialized PDF parsing service like Docparser. They provide a REST API, so can easily be integrated with the HTTP Request node. I played around with it a bit, extracting the checks from https://www.commercebank.com/-/media/cb/pdf/personal/bank/statement_sample1.pdf and this appears to work reasonably well in n8n:

Thank you for the recommendation, I didn’t know the DocParser, but I’ve already used a similar solution (Nanonets), but at this moment I’m trying as much as possible to not use a paid application (considering I’m in Brazil and the cost in dollars is way expensive) and I’m using this case to learn more about automation and things like that.

I’m still in the process of hoping I can access the HTML or JSON files directly from my bank account, but no response from them so far.

Anyway, I found a workaround to extract and transform the data from the PDF, I’ll be using Excel (with Power Query) then use n8n to upload the files in the cloud and use them.

If anyone is interested in the Excel workaround, It’s well shown in the following link (from a Brazillian programmer/teacher):

It’s in pt-br, but It’s very easy to replicate.

Thank you for all your help and attention, @MutedJam.

I’m sorry for all the inconvenience!