OL Learn

Horizontal Goto in Data Map?

I have a badly designed data file, where instead of flattened records with the line items on subsequent records, they are simply fields in the current row (item01,item02, item03).

Is there a way to create a Loop to extract these fields into detail records? A “Goto” that moves field to field rather than record to record?

EDIT: I know I can manually create a Detail Table, and extract each “field set” in its own Extract Step into the detail table. I guess I’m making a Feature Request for a “horizontal goto step” to make this a bit more straightforward.

It really depends on your data file. It sounds like you’re dealing with some kind of CSV, but the way your describe it, there would be a variable number of fields on each row.

So you’d have to deal with this data file as a Text file, but then the concept of “fields” no longer applies.

If it is indeed a CSV and each record contains the same number of fields, then all you have to do is use the new 2021.1 feature to in the Repeat step that allows you to specify that the loop does not require a Goto step. This allows the loop to stay on the same line, with each iteration processing a different field if you want it to.

Note that also in 2021.1, you can set the values for all fields in a record (or in detail tables) through scripting, which may make this even more straightforward if you are comfortable with scripts.

For instance, consider the following data file, which looks like a csv, but is actually a pipe-delimited data file in which each row has a specific meaning, with a variable number of fields for each row type:

01|INV1867748|411350|CU63047838|2021-02-24
02|Twitterlist|188 Drewry Street|RaymondVille|AB|Canada|L5A 5D2
03|Dionysus|Moiser|1(555)873-0623|dionysus.moiser@example.com
04|53674|Thule Crossroad Railing Foot Pack M450|199.95|3|3|0|599.85
04|364678|Fischer XC Comfort Pro NNN Cross Country Ski Boots|111.2|3|3|0|333.6
04|389316|Hestra RSL Comp Vertical Cut Mens Ski Racing Gloves XL|119.77|4|4|0|479.08

To extract all fields, depending on their type (which is specified through the first 2 digits on each line), you could use a script like this one:

var oneLine = data.extract(1,200,0,1,"").split("|");
var rec;
switch (oneLine[0]){
	case "01":
		record.set({OrderNumber:oneLine[1],CustomerPO:oneLine[2],CustomerNumber:oneLine[3],Date:oneLine[4]}); 
		break;
	case "02":
		record.set({Company:oneLine[1],StreetAddress:oneLine[2],City:oneLine[3],State:oneLine[4],Country:oneLine[5],ZipCode:oneLine[6]}); 
		break;
	case "03":
		record.set({ContactFirstName:oneLine[1],ContactLastName:oneLine[2],ContactPhone:oneLine[3],ContactEmail:oneLine[4]}); 
		break;
	case "04":
		var index= record.tables.products.addRow({Number:oneLine[1],Description:oneLine[2],UnitPrice:oneLine[3],Ordered:oneLine[4],Shipped:oneLine[5],BackOrder:oneLine[6],Total:oneLine[7]}); 

}

Note sure if that helps you achieve what you’re looking for, but hopefully it will help put you on the right track.