Import T24 Temenos CSV file with double delimiters

[HWallays]

Is there a solution to import T24 files which hold 2 sets of delimiters ?

Below I give a fictive (hopefully clear) example

PART A  

If we export T24 data we have the following 4 fields : customer_id, invoice , type , country in a CSV file where a delimiter is used (here a ‘;’ )

CLIENT A; INVOICE 1- INVOCE5- INVOICE17; TYPE_CUSTOMER,COUNTRY

We can read this TXT/CSV file and adjust to this delimiter .. so no issues here

PART B

The 2^nd field , concerning the invoices unfortunately holds a concatenated variable number of invoices which are separated by another delimiter (in this example  ‘-‘ ), there could be no invoices or a few hundred ones …

If the number of invoices would be clear I would use the subfield command, but this seems not practical if we have a high number of invoices inside that field .  

Is there any way to fix this  so that we either have the split-up of the invoices in either

• Version 1 : various columns ( which later need conversion via a transpose to rows)
• Version 2 : directly import in multiple rows

In the end-run I would even try to split off the client/invoice as a separate table . So wanting to end up with 2 tables : one with static data on customer and one with customer / invoice information

I also noticed that T24 can export to XML format and wonder if an import via the XML formatter would just do that all in 1 sweep ( wishfull thinking ?) …  

[Undercover]

Probier es mal mit dem Befehl Subfield.

Tabelle:

Load Distinct

Kunden-ID,

Subfield(Rechnung, '-', 1) as RECHNUNG1,

Subfield(Rechnung, '-', 2) as INVOCE5,

Subfield(Rechnung, '-', 3) as RECHNUNG17,

Typ,

Land

From TEILA.CSV (...);

Du musst nur wissen, wie das Trennzeichen innerhalb des Feldes heißt und an welcher Stelle sich genau welche Subkategorie befindet.

Für deinen TEIL B würde ich mir mal den Befehl Crosstable näher ansehen oder mit For-Schleifen arbeiten.