How to split a field in multiple fields with JavaScript?

[fsh22]

Hey guys,

Im new to Connect Designer and Im playing with Datamaper. Right know I have a txt file and after the extraction I want to split this field in multiple fields. Does anyone know how to split a field like this?

01|1|26874871|213124235345453|20.02.2020|SDASDASDA FLORICA|2600115400492|17700,33|67|25,79%|552,34|32985,12|15533,79|7|28.12.2020|N505330|29.05.2020|ASDASDASDA nr. 1A et.2 apt.6|SECTOR 1|BUCURESTI|010861|RI01110912608|B_BUCURESTI 12_7111

Thanks,
Alex

[Phil]

Here’s one way of loading each of the values in a detail table.

You first need to store the entire string in a variable that can be used later on:

• In the Preprocessor step, create a property named multiField, set its scope to Each record and its type to Object.
• Highlight the field in your data that contains your pipe-delimited file (in the example below, I have stored it in the element of an XML file, you will have to adjust according to your data type.
• Add an Action step
  • Set the Based on parameter to JavaScript. By default, the proper data.extract() statement will be generated according to the field you highlighted previously.
  • Set the step’s Action type to Set property.
  • Set the Property to multiField
  • In the Javascript expression, append .split(‘|’) to the current statement. So your entire expression should looke something like data.extract('./value[1]').split('|');

This will store an array of values in the multiField property. Now you need to extract those to individual fields. First, you’ll need to create a loop that repeats for each element in the array

• Add a Repeat step
  • Set the Repeat type to Until statement is true and its maximum iterations to sourceRecord.properties.multiField.length;
  • Set the Left operand to Extractor property and select the Counter property
  • Set the Operator to Is Equal To and the operand type to Auto-Detect.
  • Set the Right operand to JavaScript with the following expression: sourceRecord.properties.multiField.length;

Now that you’re looping on each value, you just need to extract each of them.

• Add an Extract step inside the Repeat loop
• Set the Based on parameter to Javascript with the following expression: sourceRecord.properties.multiField[steps.currentLoopCounter-1];

Finally, add a Goto step that goes back to the top of the document (the Goto step is mandatory inside a Repeat step, otherwise the DataMapper will error out).

Sounds fairly complicated, especially if you’re new to it, but once you implement it you’ll see it’s not that hard.

[fsh22]

Hi Phil,

Following your detailed instructions was easy and I had a result, but not as I expected. Further more, I`ll explain in a detailed way. So my txt files looks something like this:

The first line marked by 01| represents client details such as (address, contractNum, Name,etc).
The below lines marked by 02| represents the detail/invoice payments.

So in my datamaper, I`ll need to create/extract record based on 01| and details on 02|

Again, following your steps was easy, but my result looks like this:

Indeed I have 23 fields in the concatenated line marked by 01|, at the beginning of each record, but the result is as shown.

Any ideas?

Thanks,
Alex

[Phil]

This is going to become much more complex because you will need to create an outer loop that extracts each line and then conditionally determines (based on the first field’s value which is either 01 or 02) if it should extract all fields to the main record (in the case of 01) or to a detail table (in the case of 02).

In both cases, each sub-field on each line has a specific meaning, so you will also have to add logic to determine which sub-field should go in which extracted field.

This goes beyond the scope of what we can achieve in the forum as it would require you to provide a detailed explanation of what each field is as well as some actual sample data files.

I strongly suggest you speak with your vendor to discuss your options in terms of obtaining professional services to design this data mapping configuration for you.

[Sharne]

Hi fsh22,

This is how I handle this sort of data. You can take a look and see if it is something you can use for yours.

• Add a Repeat Step to your data mapping steps pane. For Repeat Type, select Until No More Elements.
• Select the 01 in your data and in the Repeat Step add a Multiple Conditions step.
• The multiple Conditions step should now cause a green tick to appear next to line 1 in your data.
• Change the name of the first Case to suit what line you are targeting. 01 in your case.
• Now, select the entire first line of data in the Text Viewer. Drag that extraction under your 01 case so that you have an Extraction in the 01 case.
• Rename Extraction to 01.
• Select your 01 extraction and in the Step Properties change Location to JavaScript.
• You should have a JavaScript value like: data.extract(1,100,0,1,“
  ”);
• Change the second numerical value, the length, to 4000. i.e data.extract(1,4000,0,1,“
  ”);
• This future proofs your extraction should the length of the line change.
• Now modify the JavaScript as per below.

var array01 = data.extract(1,4000,0,1,"<br />").split("|");
array01[0].trim();

• Now you can rename your field to what you would like. I would suggest naming them a specific way so you can identify where the field was extract from. So if the field was a Name field/extraction, I would call it Name01.
• For the next field, in the Field List line, click the button with the green tick, that will Add JavaScript Field to create your next field.
• Add this value as its JavaScript Expression: array01[1].trim();
• You have now targeted the second index in the array01 created in the first extraction.
• You will now repeat doing this for all the other fields whilst incrementing the array index each time. i.e. array01[2].trim(); array01[3].trim(); array01[4].trim(); etc.

For non detail record lines, in the Extraction Step, the Data Table must be set to record. When you tackle the 02 lines your Data Table should be a detail table called something like record.detail02

Your data mapper would look something like this:

Hope this helps.

Regards,
S

*Thanks to OL for this solution a few years ago.

[fsh22]

Good morning,

That’s good to know! I`ll talk with my manager in order to check for optimal solutions.

Thanks,
Alex

[fsh22]

Hi Sharne,

I`ll give it a go and will be back with a feedback upon your instructions.

Thanks,
Alex

[fsh22]

Thanks man! It works really well.
Thumbs up!

[Sharne]

Glad to help.

Regards,
S