Skip to main content

Hi, I’m trying to connect to a API using Timextender REST version 7.1.0.0 but my result is only one row of data. In Postman I get much more data. It might be because the API returns JSONL and TX tries to handle it as JSON. I’m trying to convert it to Text but it still doesn’t work. 

 

Earlier I managed to complete the goal in Power Query even with pagination. 

 

Now I’ve hit a brick wall and I need your help to proceed.
 

My result in ODX:

 


 

My result in Postman:



Here’s my connection profile:

{
  "Name": "MixPanel",
  "Description": "",
  "Provider": {
    "TemplateId": "d4aa4001-fd7b-4c65-b76d-f9710d551c1e",
    "DisplayName": "TimeXtender REST",
    "ProviderVersion": "7.1.0.0",
    "X64BitVersion": true,
    "ProviderType": 6
  },
  "RestProperties": {
    "Pills": r
      {
        "Name": "Authentication",
        "Description": "Configure authentication settings to securely access the API using tokens, keys, or other credentials.",
        "Id": "rest-general-authentication",
        "IsSelected": true
      },
      {
        "Name": "Headers",
        "Description": "Define custom request headers to include necessary metadata in API calls.",
        "Id": "rest-general-headers",
        "IsSelected": false
      },
      {
        "Name": "Pagination",
        "Description": "Pagination settings section configures how the connector handles paginated API responses.",
        "Id": "rest-general-pagination",
        "IsSelected": false
      },
      {
        "Name": "Certificate",
        "Description": "Certificates section allows configuration of client certificates used to authenticate secure connections to the REST API.",
        "Id": "rest-general-certificate",
        "IsSelected": false
      },
      {
        "Name": "Connection variables",
        "Description": "Define reusable connection variables to simplify configuration and maintain consistency across requests.",
        "Id": "rest-general-global-values",
        "IsSelected": false
      },
      {
        "Name": "Table flattening",
        "Description": "Create XSLT that will be applied to all endpoints, unless specifically disabled.",
        "Id": "rest-general-table-flattening",
        "IsSelected": false
      },
      {
        "Name": "Caching",
        "Description": "Configure caching settings to store API responses in memory or in a file.",
        "Id": "rest-general-caching",
        "IsSelected": false
      },
      {
        "Name": "Request limits",
        "Description": "Set limits on the number of API requests to control usage and prevent exceeding rate limits.",
        "Id": "rest-general-request-limits",
        "IsSelected": false
      },
      {
        "Name": "Culture",
        "Description": "Configure culture settings to control how data returned from the API is interpreted and parsed",
        "Id": "rest-general-culture",
        "IsSelected": false
      },
      {
        "Name": "Misc",
        "Description": "Configure miscellaneous settings, including request timeouts, retry attempts, and maximum concurrent tasks for optimized performance.",
        "Id": "rest-general-misc",
        "IsSelected": false
      },
      {
        "Name": "Debug logging",
        "Description": "Enable debug logging to capture detailed request and response data for troubleshooting and analysis.",
        "Id": "rest-general-debug-logging",
        "IsSelected": false
      }
    ],
    "EndpointPillsTemplate": " {\"Name\":\"Data format\",\"Description\":\"Specify the format in which data should be sent or received, such as JSON, XML, or CSV.\",\"Id\":\"rest-endpoint-{{_id_}}-data-format\",\"IsSelected\":false},{\"Name\":\"Dynamic values\",\"Description\":\"Define dynamic values that can be evaluated or replaced at runtime, allowing for flexible and context-sensitive configurations.\",\"Id\":\"rest-endpoint-{{_id_}}-dynamic-values\",\"IsSelected\":false},{\"Name\":\"Query parameters\",\"Description\":\"Define query parameters to customize API requests by filtering, sorting, or modifying data retrieval.\",\"Id\":\"rest-endpoint-{{_id_}}-query-parameters\",\"IsSelected\":false},{\"Name\":\"Table flattening\",\"Description\":\"Define an XSLT transformation to flatten nested table structures into a simplified format for easier processing.\",\"Id\":\"rest-endpoint-{{_id_}}-table-flattening\",\"IsSelected\":false},{\"Name\":\"HTTP request\",\"Description\":\"Specify the HTTP method (e.g., GET, POST, PUT) and define the request body or pagination replace request body for sending data to the API when applicable.\",\"Id\":\"rest-endpoint-{{_id_}}-http-request\",\"IsSelected\":false},{\"Name\":\"Search and replace\",\"Description\":\"Define search and replace patterns to modify request or response data by finding specific values and substituting them with new ones.\",\"Id\":\"rest-endpoint-{{_id_}}-search-replace\",\"IsSelected\":false},{\"Name\":\"Override headers\",\"Description\":\"Customize request headers to override default settings, allowing for manual specification of header values for specific requests.\",\"Id\":\"rest-endpoint-{{_id_}}-override-headers\",\"IsSelected\":false},{\"Name\":\"Override pagination\",\"Description\":\"Customize pagination settings to override default behavior.\",\"Id\":\"rest-endpoint-{{_id_}}-override-pagination\",\"IsSelected\":false},{\"Name\":\"Misc\",\"Description\":\"Configure additional settings, including using the endpoint name as the schema name, treating empty fields as null, and performing an exhaustive metadata scan for comprehensive data retrieval.\",\"Id\":\"rest-endpoint-{{_id_}}-misc\",\"IsSelected\":false},{\"Name\":\"Override caching\",\"Description\":\"Customize caching settings to override default behavior.\",\"Id\":\"rest-endpoint-{{_id_}}-override-caching\",\"IsSelected\":false},{\"Name\":\"Delay\",\"Description\":\"Set a delay between API requests to control the timing and prevent overloading the server or exceeding rate limits.\",\"Id\":\"rest-endpoint-{{_id_}}-delay\",\"IsSelected\":false}]",
    "IsLegacyConfiguration": false,
    "BaseUrl": "https://data-eu.mixpanel.com/api/2.0",
    "BaseUrlIsHidden": false,
    "AuthenticationTypeIsHardCoded": false,
    "AuthenticationConfiguration": {
      "AuthenticationType": 2,
      "BasicAuthentication": {
        "Username": "TBDVOX.cb0390.mp-service-account",
        "Password": null,
        "PasswordChanged": false
      },
      "BearerToken": null,
      "OAuth": {
        "AccessUrl": null,
        "RefreshTokenWorkingDirectory": null,
        "InitialAccessToken": null,
        "HeaderPrefixForBearer": null,
        "InitialAccessTokenCreatedUnixTimestampSeconds": null,
        "InitialAccessTokenExpiresInSeconds": null,
        "SendRequestAs": 0,
        "PassAuthorizationTokenIn": 0,
        "Parameters": r]
      },
      "AuthenticationEndpoint": {
        "EndpointName": null,
        "AuthenticationEndpointData": s],
        "ManipulationQuery": null,
        "ApplyAsHeaders": k],
        "ApplyAsQueryParameters": ],
        "ApplyAsDynamicValues": false,
        "Behavior": 0,
        "RunEveryXMinutes": null
      }
    },
    "Headers": 🙂,
    "PaginationIsHidden": false,
    "PaginationIsEnabled": false,
    "PaginationConfiguration": {
      "MaxNumberOfPages": null,
      "Parameters": >],
      "QueryExecutionTarget": {
        "TargetType": 0,
        "CustomSqlConnectionString": null,
        "TimeoutInSeconds": 30,
        "Username": null,
        "Password": null,
        "PasswordChanged": false
      },
      "Query": null,
      "ReplaceUrl": false,
      "ReplacedUrl": null,
      "ReplacePostBody": false,
      "AddAsQueryParameter": false,
      "QueryParameters": ]
    },
    "Certificate": {
      "CertificateFileType": 0,
      "CertificatePassword": null,
      "PasswordChanged": false,
      "CertificatePath": null,
      "CertificatePrivateKeyPath": null
    },
    "TimeoutInSeconds": 30,
    "MaximumRetryAttempts": 3,
    "RequestLimitIsHidden": false,
    "RequestLimitPerSecond": 0,
    "RequestLimitPerMinute": 0,
    "RequestLimitPerHour": 0,
    "RequestLimitPerDay": 0,
    "CachingOptions": {
      "CacheType": 0,
      "CachePath": null
    },
    "GlobalValues": s],
    "MaxConcurrentTasks": 4,
    "Endpoints": r
      {
        "Pills":
          {
            "Name": "Data format",
            "Description": "Specify the format in which data should be sent or received, such as JSON, XML, or CSV.",
            "Id": "rest-endpoint-ProjectID-data-format",
            "IsSelected": true
          },
          {
            "Name": "Dynamic values",
            "Description": "Define dynamic values that can be evaluated or replaced at runtime, allowing for flexible and context-sensitive configurations.",
            "Id": "rest-endpoint-ProjectID-dynamic-values",
            "IsSelected": false
          },
          {
            "Name": "Query parameters",
            "Description": "Define query parameters to customize API requests by filtering, sorting, or modifying data retrieval.",
            "Id": "rest-endpoint-ProjectID-query-parameters",
            "IsSelected": true
          },
          {
            "Name": "Table flattening",
            "Description": "Define an XSLT transformation to flatten nested table structures into a simplified format for easier processing.",
            "Id": "rest-endpoint-ProjectID-table-flattening",
            "IsSelected": false
          },
          {
            "Name": "HTTP request",
            "Description": "Specify the HTTP method (e.g., GET, POST, PUT) and define the request body or pagination replace request body for sending data to the API when applicable.",
            "Id": "rest-endpoint-ProjectID-http-request",
            "IsSelected": false
          },
          {
            "Name": "Search and replace",
            "Description": "Define search and replace patterns to modify request or response data by finding specific values and substituting them with new ones.",
            "Id": "rest-endpoint-ProjectID-search-replace",
            "IsSelected": true
          },
          {
            "Name": "Override headers",
            "Description": "Customize request headers to override default settings, allowing for manual specification of header values for specific requests.",
            "Id": "rest-endpoint-ProjectID-override-headers",
            "IsSelected": true
          },
          {
            "Name": "Override pagination",
            "Description": "Customize pagination settings to override default behavior.",
            "Id": "rest-endpoint-ProjectID-override-pagination",
            "IsSelected": false
          },
          {
            "Name": "Misc",
            "Description": "Configure additional settings, including using the endpoint name as the schema name, treating empty fields as null, and performing an exhaustive metadata scan for comprehensive data retrieval.",
            "Id": "rest-endpoint-ProjectID-misc",
            "IsSelected": false
          },
          {
            "Name": "Override caching",
            "Description": "Customize caching settings to override default behavior.",
            "Id": "rest-endpoint-ProjectID-override-caching",
            "IsSelected": false
          },
          {
            "Name": "Delay",
            "Description": "Set a delay between API requests to control the timing and prevent overloading the server or exceeding rate limits.",
            "Id": "rest-endpoint-ProjectID-delay",
            "IsSelected": false
          }
        ],
        "HtmlIdName": "ProjectID",
        "Name": "ProjectID",
        "Description": null,
        "Path": "export",
        "HttpMethod": "GET",
        "PostBody": null,
        "PaginationPostBody": null,
        "PerformExhaustiveMetaDataScan": false,
        "IsDisabled": false,
        "UseEndpointNameAsSchemaName": false,
        "EmptyFieldsAsNull": false,
        "OnlyUseEndpointTableFlattenings": false,
        "QueryParameters":
          {
            "Name": "project_id",
            "Value": "3417196",
            "Description": null,
            "IsRequired": false,
            "IsSensitive": false,
            "ValueChanged": false
          },
          {
            "Name": "from_date",
            "Value": "2025-06-01",
            "Description": null,
            "IsRequired": false,
            "IsSensitive": false,
            "ValueChanged": false
          },
          {
            "Name": "to_date",
            "Value": "2025-06-02",
            "Description": null,
            "IsRequired": false,
            "IsSensitive": false,
            "ValueChanged": false
          }
        ],
        "CustomInputFields": {},
        "CustomInputFieldsAdvanced": {},
        "DataFormat": 4,
        "CsvSettings": {
          "HasHeaderRecord": true,
          "Delimiter": null,
          "Quote": null
        },
        "OnlyListFlattenedTables": false,
        "TableFlatteningConfigurations": I],
        "DynamicValuesIsEnabled": false,
        "DynamicValuesConfiguration": {
          "DynamicValuesSource": 1,
          "EndpointTable": {
            "EndpointName": null,
            "TableName": null
          },
          "QueryExecutionTarget": {
            "TargetType": 0,
            "CustomSqlConnectionString": null,
            "TimeoutInSeconds": 30,
            "Username": null,
            "Password": null,
            "PasswordChanged": false
          },
          "Query": null,
          "SelectedEndpointWithTableFlattenings": b]
        },
        "OverriddenPaginationIsEnabled": false,
        "OverriddenCachingOptionsIsEnabled": false,
        "OverriddenHeadersIsEnabled": true,
        "OverriddenPaginationConfiguration": {
          "MaxNumberOfPages": null,
          "Parameters": ],
          "QueryExecutionTarget": {
            "TargetType": 0,
            "CustomSqlConnectionString": null,
            "TimeoutInSeconds": 30,
            "Username": null,
            "Password": null,
            "PasswordChanged": false
          },
          "Query": null,
          "ReplaceUrl": false,
          "ReplacedUrl": null,
          "ReplacePostBody": false,
          "AddAsQueryParameter": false,
          "QueryParameters": "]
        },
        "OverriddenCachingOptions": {
          "CacheType": 0,
          "CachePath": null
        },
        "OverriddenHeaders": e
          {
            "Name": "Accept",
            "Value": "text/plain",
            "IsSensitive": false,
            "ValueChanged": false
          },
          {
            "Name": "Accept-Encoding",
            "Value": "gzip",
            "IsSensitive": false,
            "ValueChanged": false
          }
        ],
        "DelayBetweenRequestsInMilliseconds": null,
        "DelayBeforeFirstRequestInMilliseconds": null,
        "SearchReplaces": e
          {
            "Search": "}\\r\\n{",
            "Replace": "}\\r\\n|||ROW|||\\r\\n{"
          },
          {
            "Search": "}\\n{",
            "Replace": "}\\n|||ROW|||\\n{"
          }
        ]
      }
    ],
    "CustomInputFields": {},
    "CustomInputFieldsAdvanced": {},
    "CultureName": null,
    "DebugLoggingIsEnabled": null,
    "DebugLoggingFilePath": null,
    "TableFlatteningConfigurations": s]
  }
}

 

Hi ​@Leo Rogeman 

If you do not set it to Text, but keeps it auto and do not use the accept header, what sort of errors or data does it return?


Hi Thomas,

I get the same result

Setup:
 

 

Result:
 

 


Hi ​@Leo Rogeman 

I would like to replicate this on my end. I have made an Zendesk ticket for this.


Hi ​@Leo Rogeman 

I replicated the issue and got some help with a solution.

We are not equipped to handle JSONL yet, it would make sense to make the provider support this type in the future.

However currently you can use the following search replace in order to convert the line-JSON to normal JSON

\A
\z ]
}\n{ },{
"\$ "

 


Hello,

Thanks for the solution. I tried but still getting one row. Am I doing something wrong wit my setup - perhaps change or remove my ‘Override Headers’? 

 


Hi ​@Leo Rogeman 

This is my setup.

I did remove the headers. Another reason for why it is working for me could be that I am on the 7047.1 release using the 10.2.0.0 REST data source.

Aren’t you on version 7.1.0.0. The Search and replace function did not exist back then.


I’m on TDI 6848.1 and using Timextender REST 7.1.0.0. From what I can see the Search and replace exists in the portal (screen shot above) but perhaps it isn’t compatible with my TDI version? 

When I upgraded the Data Source Connection to 10.2.0.0 and removed the headers I get this error message:

“The assembly for the provider TimeXtender REST (7.1.0.0) was not found”

 

Do we need to upgrade TDI to 7047.1 to make it work? 


Hi ​@Leo Rogeman 

There is no versioning of the providers. So whenever you upgrade when there is a new version you will see that it can’t see what new fields there is if there is any. I have mentioned this for the developers as it is confusing that you can be on version 7.0.0.0 and have the look and fields of version 10.2.0.0. 

You can see here what fits with what.

It doesn’t show what new features was added, you will have to read the release notes to find when the search replace feature was added.

I wrote about it in the REST guide here

 


Aha all right thanks. So the only solution for me is to

  1. upgrade TDI to 7047.1
  2. and use the Data Source Connection TimeXtender REST 10.2.0.0
  3. in order to be able to use the Search and replace
  4. which in turn will give me more than one row?

Hi ​@Leo Rogeman 

Yes, because what makes it work is the search and replace feature.

In theory you could upgrade to version 6898.1 to be able to use version 8.0.0.0 and you would have that feature, but that seems a odd way to upgrade.


All right thanks for all the help Thomas. We’ll see if we can upgrade TDI. 


Reply