How to Build a Delimited File Parser with Nested Records

Integrator.io
,
1

If you’re working with delimited files where each "parent" row is followed by one or more related "child" rows (like orders with line items), you can use integrator.io’s file definitions and parser rules to turn them into structured JSON.

This is useful for EDI-style data, flat file imports, or legacy exports that follow a repeating pattern.

Here’s a real example I built and tested.

Sample input file

"KOP";"ORD1001";"";"C12345";"jane.doe@example.com";"";"Ms.";"Jane";"Doe";"Doe Inc.";"";"123 Elm St";"Suite 500";"94105";"";"4155551234";"San Francisco";"US";"Ms.";"Jane";"Doe";"Doe Inc.";"";"4155551234";"123 Elm St";"Suite 500";"";"San Francisco";"US";"UPS";"UPS Ground";"";"";"USD";"NET30";"1000";"1500";"";"";"web";"ONLINE-2024-0001";"";"credit_card";"txn_00123456789";"";"";"2025-04-01 10:30:00";"CA";"CA";"en_us";"104";"650";"000";"385";"000";"000"
"POS";"1";"SKU12345";"2";"500";"";"0";"1000";"80";"TX1";"000";"000";"000";"000"
"POS";"2";"SKU67890";"1";"500";"";"0";"500";"40";"TX1";"000";"000";"000";"000"
"KOP";"ORD1002";"";"C67890";"john.smith@example.com";"";"Mr.";"John";"Smith";"Smith Co.";"";"456 Oak Ave";"Floor 2";"10001";"";"2125557890";"New York";"US";"Mr.";"John";"Smith";"Smith Co.";"";"2125557890";"456 Oak Ave";"Floor 2";"";"New York";"US";"FedEx";"FedEx Overnight";"";"";"USD";"PREPAID";"300";"300";"";"";"mobile";"ONLINE-2024-0002";"";"paypal";"txn_00987654321";"";"";"2025-04-02 09:15:00";"NY";"NY";"en_us";"204";"750";"000";"285";"000";"000"
"POS";"1";"SKU54321";"3";"100";"";"0";"300";"24";"TX2";"000";"000";"000";"000"

What this does

  • Each "KOP" line is an order header
  • Each "POS" line is a line item
  • The output groups POS rows under their respective KOP order in a line_items array

How to build the parser

  1. Create a file definition with format: "delimited" and colDelimiter: ";", rowDelimiter: "\n"
  2. Add a orders rule as a container with all the header fields
  3. Under each KOP rule, nest a line_items container for the POS lines
  4. Use "value": "\"KOP\"" and "value": "\"POS\"" to match row types exactly

Example output

[
  {
    "order_number": "\"ORD1001\"",
    "unused_1": "\"\"",
    "customer_number": "\"C12345\"",
    "email": "\"jane.doe@example.com\"",
    "unused_2": "\"\"",
    "prefix": "\"Ms.\"",
    "first_name": "\"Jane\"",
    "last_name": "\"Doe\"",
    "company": "\"Doe Inc.\"",
    "unused_3": "\"\"",
    "address_1": "\"123 Elm St\"",
    "address_2": "\"Suite 500\"",
    "postal_code": "\"94105\"",
    "unused_4": "\"\"",
    "phone": "\"4155551234\"",
    "city": "\"San Francisco\"",
    "country": "\"US\"",
    "billing_prefix": "\"Ms.\"",
    "billing_first_name": "\"Jane\"",
    "billing_last_name": "\"Doe\"",
    "billing_company": "\"Doe Inc.\"",
    "unused_5": "\"\"",
    "billing_phone": "\"4155551234\"",
    "billing_address": "\"123 Elm St\"",
    "billing_address_2": "\"Suite 500\"",
    "unused_6": "\"\"",
    "billing_city": "\"San Francisco\"",
    "billing_city_extra": "\"US\"",
    "billing_country": "\"UPS\"",
    "shipping_method_code": "\"UPS Ground\"",
    "shipping_method": "\"\"",
    "unused_7": "\"\"",
    "unused_8": "\"USD\"",
    "currency": "\"NET30\"",
    "payment_terms": "\"1000\"",
    "subtotal": "\"1500\"",
    "grand_total": "\"\"",
    "unused_9": "\"\"",
    "unused_10": "\"web\"",
    "unknown_p": "\"ONLINE-2024-0001\"",
    "order_source": "\"\"",
    "unused_11": "\"credit_card\"",
    "payment_method": "\"txn_00123456789\"",
    "payment_id": "\"\"",
    "unused_12": "\"\"",
    "unused_13": "\"2025-04-01 10:30:00\"",
    "order_date": "\"CA\"",
    "state": "\"CA\"",
    "billing_state": "\"en_us\"",
    "locale": "\"104\"",
    "code_1": "\"650\"",
    "code_2": "\"000\"",
    "code_3": "\"385\"",
    "code_4": "\"000\"",
    "code_5": "\"000\"",
    "line_items": [
      {
        "line_number": "\"1\"",
        "sku": "\"SKU12345\"",
        "quantity": "\"2\"",
        "unit_price": "\"500\"",
        "unused_1": "\"\"",
        "discount": "\"0\"",
        "net_amount": "\"1000\"",
        "tax": "\"80\"",
        "code_1": "\"TX1\"",
        "code_2": "\"000\"",
        "code_3": "\"000\"",
        "code_4": "\"000\"",
        "code_5": "\"000\""
      },
      {
        "line_number": "\"2\"",
        "sku": "\"SKU67890\"",
        "quantity": "\"1\"",
        "unit_price": "\"500\"",
        "unused_1": "\"\"",
        "discount": "\"0\"",
        "net_amount": "\"500\"",
        "tax": "\"40\"",
        "code_1": "\"TX1\"",
        "code_2": "\"000\"",
        "code_3": "\"000\"",
        "code_4": "\"000\"",
        "code_5": "\"000\""
      }
    ]
  },
  {
    "order_number": "\"ORD1002\"",
    "unused_1": "\"\"",
    "customer_number": "\"C67890\"",
    "email": "\"john.smith@example.com\"",
    "unused_2": "\"\"",
    "prefix": "\"Mr.\"",
    "first_name": "\"John\"",
    "last_name": "\"Smith\"",
    "company": "\"Smith Co.\"",
    "unused_3": "\"\"",
    "address_1": "\"456 Oak Ave\"",
    "address_2": "\"Floor 2\"",
    "postal_code": "\"10001\"",
    "unused_4": "\"\"",
    "phone": "\"2125557890\"",
    "city": "\"New York\"",
    "country": "\"US\"",
    "billing_prefix": "\"Mr.\"",
    "billing_first_name": "\"John\"",
    "billing_last_name": "\"Smith\"",
    "billing_company": "\"Smith Co.\"",
    "unused_5": "\"\"",
    "billing_phone": "\"2125557890\"",
    "billing_address": "\"456 Oak Ave\"",
    "billing_address_2": "\"Floor 2\"",
    "unused_6": "\"\"",
    "billing_city": "\"New York\"",
    "billing_city_extra": "\"US\"",
    "billing_country": "\"FedEx\"",
    "shipping_method_code": "\"FedEx Overnight\"",
    "shipping_method": "\"\"",
    "unused_7": "\"\"",
    "unused_8": "\"USD\"",
    "currency": "\"PREPAID\"",
    "payment_terms": "\"300\"",
    "subtotal": "\"300\"",
    "grand_total": "\"\"",
    "unused_9": "\"\"",
    "unused_10": "\"mobile\"",
    "unknown_p": "\"ONLINE-2024-0002\"",
    "order_source": "\"\"",
    "unused_11": "\"paypal\"",
    "payment_method": "\"txn_00987654321\"",
    "payment_id": "\"\"",
    "unused_12": "\"\"",
    "unused_13": "\"2025-04-02 09:15:00\"",
    "order_date": "\"NY\"",
    "state": "\"NY\"",
    "billing_state": "\"en_us\"",
    "locale": "\"204\"",
    "code_1": "\"750\"",
    "code_2": "\"000\"",
    "code_3": "\"285\"",
    "code_4": "\"000\"",
    "code_5": "\"000\"",
    "line_items": [
      {
        "line_number": "\"1\"",
        "sku": "\"SKU54321\"",
        "quantity": "\"3\"",
        "unit_price": "\"100\"",
        "unused_1": "\"\"",
        "discount": "\"0\"",
        "net_amount": "\"300\"",
        "tax": "\"24\"",
        "code_1": "\"TX2\"",
        "code_2": "\"000\"",
        "code_3": "\"000\"",
        "code_4": "\"000\"",
        "code_5": "\"000\""
      }
    ]
  }
]

Note about quoted values

If your file has quoted values like "ORD1001" or "SKU12345", the parser will retain those quotes in the output. You can remove them in a post-processing step using a script or handlebars transformation. For example, in a JavaScript hook:

function preSavePage(options) {
  function stripQuotes(value) {
    return typeof value === 'string' ? value.replace(/"/g, '') : value;
  }

  function cleanObject(obj) {
    const result = {};
    for (const key in obj) {
      const value = obj[key];
      if (Array.isArray(value)) {
        result[key] = value.map(item =>
          typeof item === 'object' && item !== null ? cleanObject(item) : stripQuotes(item)
        );
      } else if (typeof value === 'object' && value !== null) {
        result[key] = cleanObject(value);
      } else {
        result[key] = stripQuotes(value);
      }
    }
    return result;
  }

  const cleanedData = options.data.map(cleanObject);

  return {
    data: cleanedData,
    errors: options.errors,
    abort: false,
    newErrorsAndRetryData: []
  };
}

Example output

{
  "resourcePath": "orders",
  "fileDefinition": {
    "name": "Custom KOP/POS Format",
    "sandbox": false,
    "description": "Parses a semicolon-delimited file with KOP as header and POS lines as items",
    "version": "1",
    "format": "delimited",
    "delimited": {
      "rowSuffix": "\r",
      "rowDelimiter": "\n",
      "colDelimiter": ";"
    },
    "rules": [
      {
        "name": "orders",
        "maxOccurrence": 999,
        "container": true,
        "children": [
          {
            "required": true,
            "skipRowSuffix": true,
            "elements": [
              {
                "name": "\"KOP",
                "value": "\"KOP"
              },
              {
                "name": "order_number",
                "value": "1"
              },
              {
                "name": "unused_1",
                "value": "2"
              },
              {
                "name": "customer_number",
                "value": "3"
              },
              {
                "name": "email",
                "value": "4"
              },
              {
                "name": "unused_2",
                "value": "5"
              },
              {
                "name": "prefix",
                "value": "6"
              },
              {
                "name": "first_name",
                "value": "7"
              },
              {
                "name": "last_name",
                "value": "8"
              },
              {
                "name": "company",
                "value": "9"
              },
              {
                "name": "unused_3",
                "value": "10"
              },
              {
                "name": "address_1",
                "value": "11"
              },
              {
                "name": "address_2",
                "value": "12"
              },
              {
                "name": "postal_code",
                "value": "13"
              },
              {
                "name": "unused_4",
                "value": "14"
              },
              {
                "name": "phone",
                "value": "15"
              },
              {
                "name": "city",
                "value": "16"
              },
              {
                "name": "country",
                "value": "17"
              },
              {
                "name": "billing_prefix",
                "value": "18"
              },
              {
                "name": "billing_first_name",
                "value": "19"
              },
              {
                "name": "billing_last_name",
                "value": "20"
              },
              {
                "name": "billing_company",
                "value": "21"
              },
              {
                "name": "unused_5",
                "value": "22"
              },
              {
                "name": "billing_phone",
                "value": "23"
              },
              {
                "name": "billing_address",
                "value": "24"
              },
              {
                "name": "billing_address_2",
                "value": "25"
              },
              {
                "name": "unused_6",
                "value": "26"
              },
              {
                "name": "billing_city",
                "value": "27"
              },
              {
                "name": "billing_city_extra",
                "value": "28"
              },
              {
                "name": "billing_country",
                "value": "29"
              },
              {
                "name": "shipping_method_code",
                "value": "30"
              },
              {
                "name": "shipping_method",
                "value": "31"
              },
              {
                "name": "unused_7",
                "value": "32"
              },
              {
                "name": "unused_8",
                "value": "33"
              },
              {
                "name": "currency",
                "value": "34"
              },
              {
                "name": "payment_terms",
                "value": "35"
              },
              {
                "name": "subtotal",
                "value": "36"
              },
              {
                "name": "grand_total",
                "value": "37"
              },
              {
                "name": "unused_9",
                "value": "38"
              },
              {
                "name": "unused_10",
                "value": "39"
              },
              {
                "name": "unknown_p",
                "value": "40"
              },
              {
                "name": "order_source",
                "value": "41"
              },
              {
                "name": "unused_11",
                "value": "42"
              },
              {
                "name": "payment_method",
                "value": "43"
              },
              {
                "name": "payment_id",
                "value": "44"
              },
              {
                "name": "unused_12",
                "value": "45"
              },
              {
                "name": "unused_13",
                "value": "46"
              },
              {
                "name": "order_date",
                "value": "47"
              },
              {
                "name": "state",
                "value": "48"
              },
              {
                "name": "billing_state",
                "value": "49"
              },
              {
                "name": "locale",
                "value": "50"
              },
              {
                "name": "code_1",
                "value": "51"
              },
              {
                "name": "code_2",
                "value": "52"
              },
              {
                "name": "code_3",
                "value": "53"
              },
              {
                "name": "code_4",
                "value": "54"
              },
              {
                "name": "code_5",
                "value": "55"
              },
              {
                "name": "code_6",
                "value": "56"
              }
            ],
            "children": [
              {
                "name": "line_items",
                "maxOccurrence": 999,
                "container": true,
                "children": [
                  {
                    "required": false,
                    "skipRowSuffix": true,
                    "elements": [
                      {
                        "name": "\"POS",
                        "value": "\"POS"
                      },
                      {
                        "name": "line_number",
                        "value": "1"
                      },
                      {
                        "name": "sku",
                        "value": "2"
                      },
                      {
                        "name": "quantity",
                        "value": "3"
                      },
                      {
                        "name": "unit_price",
                        "value": "4"
                      },
                      {
                        "name": "unused_1",
                        "value": "5"
                      },
                      {
                        "name": "discount",
                        "value": "6"
                      },
                      {
                        "name": "net_amount",
                        "value": "7"
                      },
                      {
                        "name": "tax",
                        "value": "8"
                      },
                      {
                        "name": "code_1",
                        "value": "9"
                      },
                      {
                        "name": "code_2",
                        "value": "10"
                      },
                      {
                        "name": "code_3",
                        "value": "11"
                      },
                      {
                        "name": "code_4",
                        "value": "12"
                      },
                      {
                        "name": "code_5",
                        "value": "13"
                      }
                    ]
                  }
                ]
              }
            ]
          }
        ]
      }
    ]
  }
}

This approach works great for structured flat files that repeat a header/detail pattern. You can expand the parser to capture every field in your data, and apply post-processing logic in your flows if needed.

Screenshots of setup:

The image shows a flowchart box labeled "Parse orders" with an "FTP" tag and a "Transfer" button. (Captioned by AI)
The image shows a flowchart box labeled "Parse orders" with an "FTP" tag and a "Transfer" button. (Captioned by AI)384×162 4.26 KB

The image shows a Celigo interface for editing a data transfer, including parsing options, file settings, and a JSON output preview. (Captioned by AI)
The image shows a Celigo interface for editing a data transfer, including parsing options, file settings, and a JSON output preview. (Captioned by AI)2560×1277 258 KB

An EDI parser interface displaying file definitions, a sample file, and parsed output in a three-panel layout. (Captioned by AI)
An EDI parser interface displaying file definitions, a sample file, and parsed output in a three-panel layout. (Captioned by AI)2560×1277 308 KB

The image shows a software interface for creating a process flow with options for hooks and settings on the side panel. (Captioned by AI)
The image shows a software interface for creating a process flow with options for hooks and settings on the side panel. (Captioned by AI)2560×1277 132 KB

The image shows a script editor interface with code on the left and JSON data for function input and output on the right. (Captioned by AI)
The image shows a script editor interface with code on the left and JSON data for function input and output on the right. (Captioned by AI)2560×1277 286 KB

1 Like
2

Note that you need to choose the X12 format, and then you can choose any template since you will override the template with what I placed above.