Extract from File with mismatched headers and data

Questions
,
1

Describe the problem/error/question

I have a workflow that is supposed to extract a CSV file from my email every hour (generated by a 3rd party software) and paste it into Google Sheets, but I get an error from the Extract from File node because the CSV has 54 header columns, but 56 columns of data beneath.

This is an error on that 3rd party software’s end, but I don’t think they will fix the missing header in a timely manner. Is there a way to only extract the first 54 columns without getting this error? I simply need to copy and paste this CSV file into a Google Sheet.

What is the error message (if any)?

Problem in node ‘Extract from File‘
Invalid Record Length: columns length is 54, got 56 on line 2

Please share your workflow

{
“nodes”: [
{
“parameters”: {
“pollTimes”: {
“item”: [
{
“hour”: 7
}
]
},
“simple”: false,
“filters”: {
“q”: “subject:Export Order Summary (for Order Management)”,
“sender”: “reports@ordoro.com”
},
“options”: {
“downloadAttachments”: true
}
},
“type”: “n8n-nodes-base.gmailTrigger”,
“typeVersion”: 1.2,
“position”: [
0,
0
],
“id”: “4fd7e7b0-fbea-425f-8f45-b128f9c366f7”,
“name”: “Gmail Trigger”,
“credentials”: {
“gmailOAuth2”: {
“id”: “ASW0YK23GsBmscgU”,
“name”: “Mitch’s Gmail”
}
}
},
{
“parameters”: {
“operation”: “get”,
“messageId”: “={{ $json.id }}”,
“simple”: false,
“options”: {
“downloadAttachments”: true
}
},
“type”: “n8n-nodes-base.gmail”,
“typeVersion”: 2.1,
“position”: [
220,
0
],
“id”: “f037f76d-d827-4bf6-86d2-b210dfa5eb5d”,
“name”: “Get a message”,
“webhookId”: “0aea5cdd-66d5-4350-82db-0c7600f43640”,
“credentials”: {
“gmailOAuth2”: {
“id”: “ASW0YK23GsBmscgU”,
“name”: “Mitch’s Gmail”
}
}
},
{
“parameters”: {
“operation”: “clear”,
“documentId”: {
“__rl”: true,
“value”: “1G0uklrJodbfZ5RsOkDZBjUmOfEMlbTZBy3NVMpgeVkI”,
“mode”: “list”,
“cachedResultName”: “Order Management Dashboard (Live)”,
“cachedResultUrl”: “Order Management Dashboard (Live) - Google Sheets”
},
“sheetName”: {
“__rl”: true,
“value”: 479660170,
“mode”: “list”,
“cachedResultName”: “Ultimate Benchmark INPUT”,
“cachedResultUrl”: “https://docs.google.com/spreadsheets/d/1G0uklrJodbfZ5RsOkDZBjUmOfEMlbTZBy3NVMpgeVkI/edit#gid=479660170”
},
“keepFirstRow”: true
},
“type”: “n8n-nodes-base.googleSheets”,
“typeVersion”: 4.6,
“position”: [
440,
0
],
“id”: “db45eff3-26de-4a19-b46b-8f0bd1a9593b”,
“name”: “Clear sheet”,
“credentials”: {
“googleSheetsOAuth2Api”: {
“id”: “B6ynExSfpGYfnpBt”,
“name”: “Mitch’s Google Sheets Account”
}
}
},
{
“parameters”: {
“amount”: 1
},
“type”: “n8n-nodes-base.wait”,
“typeVersion”: 1.1,
“position”: [
660,
0
],
“id”: “74e48933-75cd-4bd2-804e-e9ffa7772384”,
“name”: “Wait”,
“webhookId”: “ab7386f0-f1e6-48e3-9e15-e9ec5ce6a9ab”
},
{
“parameters”: {
“operation”: “update”,
“documentId”: {
“__rl”: true,
“value”: “1G0uklrJodbfZ5RsOkDZBjUmOfEMlbTZBy3NVMpgeVkI”,
“mode”: “list”,
“cachedResultName”: “Order Management Dashboard (Live)”,
“cachedResultUrl”: “Order Management Dashboard (Live) - Google Sheets”
},
“sheetName”: {
“__rl”: true,
“value”: 479660170,
“mode”: “list”,
“cachedResultName”: “Ultimate Benchmark INPUT”,
“cachedResultUrl”: “https://docs.google.com/spreadsheets/d/1G0uklrJodbfZ5RsOkDZBjUmOfEMlbTZBy3NVMpgeVkI/edit#gid=479660170”
},
“columns”: {
“mappingMode”: “autoMapInputData”,
“value”: {},
“matchingColumns”: [
“Order ID”
],
“schema”: [
{
“id”: “Order ID”,
“displayName”: “Order ID”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “External Order ID”,
“displayName”: “External Order ID”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “Order Date”,
“displayName”: “Order Date”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “Cart Name”,
“displayName”: “Cart Name”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “Order Status”,
“displayName”: “Order Status”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “Number of Lines”,
“displayName”: “Number of Lines”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “Total Price”,
“displayName”: “Total Price”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “Product Subtotal”,
“displayName”: “Product Subtotal”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “Discount”,
“displayName”: “Discount”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “Shipping & Handling”,
“displayName”: “Shipping & Handling”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “Tax”,
“displayName”: “Tax”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “Credit Card Issuer”,
“displayName”: “Credit Card Issuer”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “Bill To (Company Name)”,
“displayName”: “Bill To (Company Name)”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “Bill To (Name)”,
“displayName”: “Bill To (Name)”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “Bill To (Street1)”,
“displayName”: “Bill To (Street1)”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “Bill To (Street2)”,
“displayName”: “Bill To (Street2)”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “Bill To (City)”,
“displayName”: “Bill To (City)”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “Bill To (State)”,
“displayName”: “Bill To (State)”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “Bill To (Code)”,
“displayName”: “Bill To (Code)”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “Bill To (Country)”,
“displayName”: “Bill To (Country)”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “Bill To (Email)”,
“displayName”: “Bill To (Email)”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “Bill To (Phone)”,
“displayName”: “Bill To (Phone)”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “Bill To (Fax)”,
“displayName”: “Bill To (Fax)”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “Bill To (Reference Number)”,
“displayName”: “Bill To (Reference Number)”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “Ship To (Company Name)”,
“displayName”: “Ship To (Company Name)”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “Ship To (Name)”,
“displayName”: “Ship To (Name)”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “Ship To (Street1)”,
“displayName”: “Ship To (Street1)”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “Ship To (Street2)”,
“displayName”: “Ship To (Street2)”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “Ship To (City)”,
“displayName”: “Ship To (City)”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “Ship To (State)”,
“displayName”: “Ship To (State)”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “Ship To (Code)”,
“displayName”: “Ship To (Code)”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “Ship To (Country)”,
“displayName”: “Ship To (Country)”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “Ship To (Email)”,
“displayName”: “Ship To (Email)”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “Ship To (Phone)”,
“displayName”: “Ship To (Phone)”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “Ship To (Fax)”,
“displayName”: “Ship To (Fax)”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “Ship To (Reference Number)”,
“displayName”: “Ship To (Reference Number)”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “Requested Shipping Method”,
“displayName”: “Requested Shipping Method”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “Customer Instructions”,
“displayName”: “Customer Instructions”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “Internal Notes”,
“displayName”: “Internal Notes”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “Tags”,
“displayName”: “Tags”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “Supplier”,
“displayName”: “Supplier”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “Length”,
“displayName”: “Length”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “Width”,
“displayName”: “Width”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “Height”,
“displayName”: “Height”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “Weight”,
“displayName”: “Weight”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “Vendor”,
“displayName”: “Vendor”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “Package Type”,
“displayName”: “Package Type”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “Shipment Date”,
“displayName”: “Shipment Date”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “Shipping Method”,
“displayName”: “Shipping Method”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “Tracking Number”,
“displayName”: “Tracking Number”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “Tracking Status”,
“displayName”: “Tracking Status”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “Shipping Cost”,
“displayName”: “Shipping Cost”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “Allocation Status”,
“displayName”: “Allocation Status”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “Shipper”,
“displayName”: “Shipper”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“removed”: false
},
{
“id”: “row_number”,
“displayName”: “row_number”,
“required”: false,
“defaultMatch”: false,
“display”: true,
“type”: “string”,
“canBeUsedToMatch”: true,
“readOnly”: true,
“removed”: false
}
],
“attemptToConvertTypes”: false,
“convertFieldsToString”: false
},
“options”: {}
},
“type”: “n8n-nodes-base.googleSheets”,
“typeVersion”: 4.6,
“position”: [
1060,
0
],
“id”: “d7b5fbc7-2764-4bd2-8935-ca190de1b03c”,
“name”: “Update row in sheet”,
“credentials”: {
“googleSheetsOAuth2Api”: {
“id”: “B6ynExSfpGYfnpBt”,
“name”: “Mitch’s Google Sheets Account”
}
}
},
{
“parameters”: {
“binaryPropertyName”: “attachment_0”,
“options”: {}
},
“type”: “n8n-nodes-base.extractFromFile”,
“typeVersion”: 1,
“position”: [
860,
0
],
“id”: “44c722a9-f69e-4a9e-b2a2-16e72a1541f7”,
“name”: “Extract from File”
}
],
“connections”: {
“Gmail Trigger”: {
“main”: [
[
{
“node”: “Get a message”,
“type”: “main”,
“index”: 0
}
]
]
},
“Get a message”: {
“main”: [
[
{
“node”: “Clear sheet”,
“type”: “main”,
“index”: 0
}
]
]
},
“Clear sheet”: {
“main”: [
[
{
“node”: “Wait”,
“type”: “main”,
“index”: 0
}
]
]
},
“Wait”: {
“main”: [
[
{
“node”: “Extract from File”,
“type”: “main”,
“index”: 0
}
]
]
},
“Extract from File”: {
“main”: [
[
{
“node”: “Update row in sheet”,
“type”: “main”,
“index”: 0
}
]
]
}
},
“pinData”: {},
“meta”: {
“templateCredsSetupCompleted”: true,
“instanceId”: “b0ba8ccf046ad7208c4659df2dcd76923e0f3b85e04052c7dc4999aa7361c745”
}
}

(Select the nodes on your canvas and use the keyboard shortcuts CMD+C/CTRL+C and CMD+V/CTRL+V to copy and paste the workflow.)

Information on your n8n setup

  • 1.100.1
  • Database (default: SQLite):
  • n8n EXECUTIONS_PROCESS setting (default: own, main):
  • Running n8n via Google Chrome
  • Windows 11
2

Hey @Mitchell_Munro,

Can you embed your workflow using the Preformatted text by clicking on

image
image876×228 37.5 KB

Mitchell_Munro 3
4

Would this options help:

image
image722×1448 144 KB

5

The problem comes from extraction of the CSV itself, not the transfer to Google Sheet.

6

I see. Could you please provide an example of the file and an example of the sheet, I could try to take a look.

7

Here’s an example of the CSV File. Note that the rows extend into the thousands, I just shortened it to 5 rows of data.

I highlighted the problematic columns (last two, BC and BD). Since they have no headers, the Extract from File node doesn’t work correctly. Is there a way to delete these columns before extracting from CSV?

CSV File Example

8

Thanks for supplying the example table.
So the table has X header column values and each row has X+2 column values.

You get a CSV in the email with the same X headers and X+2 column values n each row and you would like to get them onto the sheet (update action based on OrderID). Right?

9

What I did:

  • I downloaded the sheet as CSV
  • I sent to myself as an attachment and
  • I imported it back (appended) to the same sheet.

When I tried to extract the data as CSV it didn’t work for me, what did is extract data as XLS or XLSX. The only issue is that it didn’t handle the dates properly. Could you see if this approach generally works for you and if it does, we can try to figure out the dates as well.

10

Extracting as XLSX worked! I had to mess with some settings since it autofilled the empty headers, but after some fiddling I think it works consistently now. Thank you!