I'm writing a map (Mercator vsn 6.71) to 'clean' a csv file that is sent with some fields containing commas or CR/LF values (which are the record delimiter and terminator respectively). The fields are surrounded by " marks (good old Excel).
After much messing around I've managed to get it written to do the above task, but it is failing at the last hurdle, which is to also map any fields that are null in the input (in order to accurately create a 'cleaned' copy of the file without the embedded commas or CR/LFs).
E.g. a record containing:
Field1,Field2,,Field4
maps as:
Field1,Field2,Field4
As you can see, the last field has shifted because of the lack of the null field on output, therefore completely changing the structure of the record.
The Group 'Track' parameter of 'Places' is supposed to fix this issue, but if the final group the text field is held in is 'Implicit' (which it needs to be for the map to work correctly)then this does not work.
I've even tried using the INDEXABS and CLONE functions to try and force it to write the NULL fields out, but it doesn't work.
Any ideas MUCH appreciated!
After much messing around I've managed to get it written to do the above task, but it is failing at the last hurdle, which is to also map any fields that are null in the input (in order to accurately create a 'cleaned' copy of the file without the embedded commas or CR/LFs).
E.g. a record containing:
Field1,Field2,,Field4
maps as:
Field1,Field2,Field4
As you can see, the last field has shifted because of the lack of the null field on output, therefore completely changing the structure of the record.
The Group 'Track' parameter of 'Places' is supposed to fix this issue, but if the final group the text field is held in is 'Implicit' (which it needs to be for the map to work correctly)then this does not work.
I've even tried using the INDEXABS and CLONE functions to try and force it to write the NULL fields out, but it doesn't work.
Any ideas MUCH appreciated!