Transforming SQL exports for NetSuite import

Integrator.io
, ,
1

Hello!
I'm trying to group my SQL export records by Purchase Order Number and move the shared/common fields to a header level to reduce redundancy for import into NS.

Original Query Output (Before Grouping)

Before grouping by PO Number, my SQL query returns individual records like this:

json

{
  "page_of_records": [
    {
      "rows": [
        {
          "ReceiptID": 101,
          "VendorID": 500,
          "NetSuiteVendorInternalID": 1500,
          "NSLocationInternalID": 25,
          "NetSuiteLocationName": null,
          "NetSuiteLocationID": null,
          "PurchaseOrderNumber": "PO1234",
          "PurchaseOrderInternalID": null,
          "InventoryID": 80001,
          "NetSuiteItemID": 20001,
          "ProductName": "Test Product Kit A",
          "LotNumber": "LOT001",
          "ReceivedDate": "2025-06-01T00:00:00.000Z",
          "QuantityRaw": 50,
          "PackSize": 25,
          "QuantityNormalized": 2,
          "CoupaIRLineID": null,
          "CoupaPOLineID": null,
          "Memo": null,
          "RecordStatus": "Pending",
          "RecordStatusDate": "2025-06-08T10:15:30.000Z",
          "RecordError": null,
          "PackNumber": 1
        },
        {
          "ReceiptID": 102,
          "VendorID": 500,
          "NetSuiteVendorInternalID": 1500,
          "NSLocationInternalID": 25,
          "NetSuiteLocationName": null,
          "NetSuiteLocationID": null,
          "PurchaseOrderNumber": "PO1234",
          "PurchaseOrderInternalID": null,
          "InventoryID": 80001,
          "NetSuiteItemID": 20001,
          "ProductName": "Test Product Kit A",
          "LotNumber": "LOT001",
          "ReceivedDate": "2025-06-01T00:00:00.000Z",
          "QuantityRaw": 50,
          "PackSize": 25,
          "QuantityNormalized": 2,
          "CoupaIRLineID": null,
          "CoupaPOLineID": null,
          "Memo": null,
          "RecordStatus": "Pending",
          "RecordStatusDate": "2025-06-08T10:15:30.000Z",
          "RecordError": null,
          "PackNumber": 2
        },
        {
          "ReceiptID": 103,
          "VendorID": 500,
          "NetSuiteVendorInternalID": 1500,
          "NSLocationInternalID": 25,
          "NetSuiteLocationName": null,
          "NetSuiteLocationID": null,
          "PurchaseOrderNumber": "PO1234",
          "PurchaseOrderInternalID": null,
          "InventoryID": 80001,
          "NetSuiteItemID": 20001,
          "ProductName": "Test Product Kit A",
          "LotNumber": "LOT001",
          "ReceivedDate": "2025-06-01T00:00:00.000Z",
          "QuantityRaw": 50,
          "PackSize": 25,
          "QuantityNormalized": 2,
          "CoupaIRLineID": null,
          "CoupaPOLineID": null,
          "Memo": null,
          "RecordStatus": "Pending",
          "RecordStatusDate": "2025-06-08T10:15:30.000Z",
          "RecordError": null,
          "PackNumber": 3
        }
      ]
    }
  ]
}

Current Grouped Output

After using Celigo to group by PO Number, I'm actually getting this structure where the records are still separate but I haven't successfully moved the shared fields to header level yet:

json

{
  "page_of_records": [
    {
      "rows": [
        {
          "ReceiptID": 101,
          "VendorID": 500,
          "NetSuiteVendorInternalID": 1500,
          "NSLocationInternalID": 25,
          "NetSuiteLocationName": null,
          "NetSuiteLocationID": null,
          "PurchaseOrderNumber": "PO1234",
          "PurchaseOrderInternalID": null,
          "InventoryID": 80001,
          "NetSuiteItemID": 20001,
          "ProductName": "Test Product Kit A",
          "LotNumber": "LOT001",
          "ReceivedDate": "2025-06-01T00:00:00.000Z",
          "QuantityRaw": 50,
          "PackSize": 25,
          "QuantityNormalized": 2,
          "CoupaIRLineID": null,
          "CoupaPOLineID": null,
          "Memo": null,
          "RecordStatus": "Pending",
          "RecordStatusDate": "2025-06-08T10:15:30.000Z",
          "RecordError": null,
          "PackNumber": 1
        },
        {
          "ReceiptID": 102,
          "VendorID": 500,
          "NetSuiteVendorInternalID": 1500,
          "NSLocationInternalID": 25,
          "NetSuiteLocationName": null,
          "NetSuiteLocationID": null,
          "PurchaseOrderNumber": "PO1234",
          "PurchaseOrderInternalID": null,
          "InventoryID": 80001,
          "NetSuiteItemID": 20001,
          "ProductName": "Test Product Kit A",
          "LotNumber": "LOT001",
          "ReceivedDate": "2025-06-01T00:00:00.000Z",
          "QuantityRaw": 50,
          "PackSize": 25,
          "QuantityNormalized": 2,
          "CoupaIRLineID": null,
          "CoupaPOLineID": null,
          "Memo": null,
          "RecordStatus": "Pending",
          "RecordStatusDate": "2025-06-08T10:15:30.000Z",
          "RecordError": null,
          "PackNumber": 2
        },
        {
          "ReceiptID": 103,
          "VendorID": 500,
          "NetSuiteVendorInternalID": 1500,
          "NSLocationInternalID": 25,
          "NetSuiteLocationName": null,
          "NetSuiteLocationID": null,
          "PurchaseOrderNumber": "PO1234",
          "PurchaseOrderInternalID": null,
          "InventoryID": 80001,
          "NetSuiteItemID": 20001,
          "ProductName": "Test Product Kit A",
          "LotNumber": "LOT001",
          "ReceivedDate": "2025-06-01T00:00:00.000Z",
          "QuantityRaw": 50,
          "PackSize": 25,
          "QuantityNormalized": 2,
          "CoupaIRLineID": null,
          "CoupaPOLineID": null,
          "Memo": null,
          "RecordStatus": "Pending",
          "RecordStatusDate": "2025-06-08T10:15:30.000Z",
          "RecordError": null,
          "PackNumber": 3
        }
      ]
    }
  ]
}

Desired Final Output

I want the rows field to be parsed as an actual JSON array instead of a string:

json

{
  "page_of_records": [
    {
      "rows": [
        {
          "PurchaseOrderNumber": "PO1234",
          "VendorID": 500,
          "NetSuiteVendorInternalID": 1500,
          "NSLocationInternalID": 25,
          "NetSuiteLocationName": null,
          "NetSuiteLocationID": null,
          "PurchaseOrderInternalID": null,
          "rows": [
            {
              "ReceiptID": 101,
              "InventoryID": 80001,
              "NetSuiteItemID": 20001,
              "ProductName": "Test Product Kit A",
              "LotNumber": "LOT001",
              "ReceivedDate": "2025-06-01",
              "QuantityRaw": 50,
              "PackSize": 25,
              "QuantityNormalized": 2,
              "CoupaIRLineID": null,
              "CoupaPOLineID": null,
              "Memo": null,
              "RecordStatus": "Pending",
              "RecordStatusDate": "2025-06-08T10:15:30.000",
              "RecordError": null,
              "PackNumber": 1
            },
            {
              "ReceiptID": 102,
              "InventoryID": 80001,
              "NetSuiteItemID": 20001,
              "ProductName": "Test Product Kit A",
              "LotNumber": "LOT001",
              "ReceivedDate": "2025-06-01",
              "QuantityRaw": 50,
              "PackSize": 25,
              "QuantityNormalized": 2,
              "CoupaIRLineID": null,
              "CoupaPOLineID": null,
              "Memo": null,
              "RecordStatus": "Pending",
              "RecordStatusDate": "2025-06-08T10:15:30.000",
              "RecordError": null,
              "PackNumber": 2
            },
            {
              "ReceiptID": 103,
              "InventoryID": 80001,
              "NetSuiteItemID": 20001,
              "ProductName": "Test Product Kit A",
              "LotNumber": "LOT001",
              "ReceivedDate": "2025-06-01",
              "QuantityRaw": 50,
              "PackSize": 25,
              "QuantityNormalized": 2,
              "CoupaIRLineID": null,
              "CoupaPOLineID": null,
              "Memo": null,
              "RecordStatus": "Pending",
              "RecordStatusDate": "2025-06-08T10:15:30.000",
              "RecordError": null,
              "PackNumber": 3
            }
          ]
        }
      ]
    }
  ]
}

Question

Is it possible to somehow add a transformation step to parse the JSON string in the PO's rows field into an actual JSON array? I need this to work with any number of receipt records per purchase order.

The goal is to eliminate the redundant shared fields (VendorID, PurchaseOrderNumber, etc.) that were repeated in every record, while making the receipt details easily accessible as a proper array.

Thanks for any help!

2

yes, you can make a transformation like so:

The image depicts a user interface for defining data transformations with mappings for fields such as 'id', 'customer', 'lines', 'item', and 'quantity'. (Captioned by AI)
The image depicts a user interface for defining data transformations with mappings for fields such as 'id', 'customer', 'lines', 'item', and 'quantity'. (Captioned by AI)1712×848 77.2 KB

Note the transform is set to 'create output record from input rows', that allows you to create a single object from the rows array. Be sure to set all 'header' fields like customer in the example to use $[0] instead of the default $[*], so the transform won't concat the customer values from all lines, but will just pick the first entry.

The example above has the downside that you need to add all fields in the query response to your transform, and if you ever add new fields to the query you must not forget to add them to the transform as well. A quick and dirty fix for this is to map it like below, but that won't remove the redundant fields from the header:

The image shows a data transformation interface with fields labeled "header" and "lines" for processing input rows. (Captioned by AI)
The image shows a data transformation interface with fields labeled "header" and "lines" for processing input rows. (Captioned by AI)1304×304 28.2 KB

For this mapping to work set both lines to copy as-is:

image
image594×154 8.36 KB

3

Thanks Bas!

I however don't see the 'create output record {} from input rows option. These are the rules that are available to me.

The image shows a user interface for defining data transformation rules, including options to modify input records and create output records from input records. (Captioned by AI)
The image shows a user interface for defining data transformation rules, including options to modify input records and create output records from input records. (Captioned by AI)828×497 37.2 KB

4

It's a known bug where you are seeing the rows structure before applying any grouping in Celigo for SQL Server. This should be fixed soon because you should see the record structure if no grouping is applied.

When you aren't seeing the create output record {} from input rows [] option, is that with your grouping applied?

5

Hey Tyler,

Yeah that's with the grouping applied, so my export data is going to look how I detailed out in "Current grouped output."

6

Are you able to swing by office hours next week so we can take a look?

7

Hey Tyler,

Apologies for the delayed response I was out for the remainder of last week. The end goal of the transformation was to adjust my data structure so that I could correctly receive items on a NS PO. I am currently trying to address that end goal on a different help thread in a way that I believe should be more straightforward. Thanks for the response here, I will follow up here if the transformation is still needed!

1 Like