LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: dwayneramsay

Hi All,

I'm New to Lavastorm and BRE. I need to pull data from a number of varying excel formats - however, the fieldname may be difference...

For example one sheet may contain the same data but the field/column name for one might be Destination and the other DestinationName.

How can I configure the nodes to handle and normalize.

Ii was trying to use the following code but get the error:

input_fields = inputFields(1)
cc_exists = input_fields.find("Destination")

country_code = str(null)

if (cc_exists <> -1) then
{
country_code = field("Country Code").str().trim()
}
else
{
country_code = ""
}

However, I still get the following error, even though the condition evaluates to false:
ERROR initializing expert output:
unknown field for source '': 'Country Code'
2012-07-12 12:52:00.000; WARN: unknown field for source '': 'Country Code'
id: 0 chain: 0 group: 0
cppDetail: context: Salmon::NED::Field_Expr::findField() ..\expert\Field_Expr.cpp@140

2012-07-12 12:52:00.000; WARN: ...in combination starting on line 28
id: 0 chain: 0 group: 0
cppDetail: context: ..\expert\Combination_Expr.cpp@92

2012-07-12 12:52:00.000; WARN: ...in combination starting on line 28
id: 0 chain: 0 group: 0
cppDetail: context: ..\expert\Combination_Expr.cpp@92

2012-07-12 12:52:00.000; WARN: ...in combination starting on line 28
id: 0 chain: 0 group: 0
cppDetail: context: ..\expert\Combination_Expr.cpp@92

2012-07-12 12:52:00.000; WARN: ...exception seen
id: 0 chain: 0 group: 0
cppDetail: context: Salmon::NED::BrainScriptSet_ExprStmt::BrainScriptS et_ExprStmt() .\BrainScriptSet_ExprStmt.cpp@60

2012-07-12 12:52:00.000; WARN: ...in statement starting on line 28
id: 0 chain: 0 group: 0
cppDetail: context: Salmon::NED::Statement_Expr::tryParseStatement() ..\expert\ExprStatement.cpp@158

2012-07-12 12:52:00.000; WARN: ...in expression starting on line 28
id: 0 chain: 0 group: 0
cppDetail: context: Expr::parseExpr() ..\expert\Expr.cpp@89

2012-07-12 12:52:00.000; WARN: ...in expression starting on line 26
id: 0 chain: 0 group: 0
cppDetail: context: Expr::parseExpr() ..\expert\Expr.cpp@89

2012-07-12 12:52:00.000; WARN: ...handling expression #14 in file
id: 0 chain: 0 group: 0
cppDetail: context: Salmon::NED::ExprTableIo::initialize() .\ExprTableIo.cpp@86

2012-07-12 12:52:00.000; WARN: ...exception seen
id: 0 chain: 0 group: 0
cppDetail: context: Salmon::NED::NodeControl::initializeExpert() .\NodeControl.cpp@1311

Appreciate any assistance
Regards,
Dwayne

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: Tim Meagher

Hi,

Even though the field names may be different in the Excel workbooks, are the fields always in the same order?
If so, you can explicitly specify the field names with which this data should be output in the WorkbookSpec parameter in the Excel File node (look at the <field> tag in this parameter's help).
Then, you shouldn't need to dynamically rename the fields later.

Regards,
Tim.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: dwayneramsay

Hi Tim,

Thanks for the feedback.

However, the fields don't always flow in the same order; the spreadsheets are coming from varying partners which have their own templates/standards.

Any other ideas?

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: Tim Meagher

Hi,

There are probably a number of ways this can be addressed.
If you have these spreadsheets in different directory locations based on the different partners they are coming from, or with different filename patterns depending on the vendor, you could always setup multiple data processing paths which perform the data acquisition & cleansing, and manipulate the data such that it has a common format prior to bringing the data together with a cat node or something similar.

For instance, if you have data coming from 2 different partners, and you always receive spreadsheets from partner A in a directory /inputData/A/A*.xlsx and always receive spreadsheets from partner B in a directory /inputData/B/B*.xlsx, then you could construct a composite node with two directory list nodes.
The first directory list node would be setup with the /inputData/A directory, and the pattern A*.xlsx, and the second with /inputData/B/B*.xlsx.
The output of these directory list nodes could then be fed as the input to different Excel File nodes.
Then each of these Excel File nodes could output to subsequent filter nodes which would do the data manipulation prior to being fed into a Cat node which would bring the data together.

On the other hand, if the spreadsheets themselves are not able to be differentiated based on directory names or file name patterns, then you could continue as you are currently doing, however, rather than referring to the fields by their names, you can get the field name from the inputFields(..) function.

I think the problem that you are running into is that if you provide a hard-coded string in the field(...) operator, then this is evaluated up-front regardless of whether or not that line of code will actually be executed. The BRAINscript evaluator will then verify that the field doesn't exist on the input record and fail the node.
Therefore, you can simply use field(inputFields(1).getItem(itemIdx)) to get the value of the field.

I have attached an example graph of how you can do this.
Attachments:
FieldMapping.brg