Forums

Ability to populate multi-relational fields during import using comma separated values in Excel Problem

SBM

Friday, February 01 2019, 09:29 AM

Offline

I have an issue importing an XLS spreadsheet with multi-values separated by comma into an AUX table.

Currently the only method that works is formatting the spreadsheet in this format.

Column 1 Column 2
--------------------------------------

Title 1 Value 1
Title 1 Value 2
Title 1 Value 3

------------------------------------

My current spreadsheet is in this format

Column 1 Column 2

Title 1 Value1, Value2, Value3

Is there anyway to enhance SBM to import XLS with this format as multi-relational values?

Like Steven Nguyen likes this post.

Responses (2)

Accepted Answer
David Sheaffe

Offline
Sunday, February 03 2019, 04:01 PM - #Permalink
0

I would suggest you add this to the "Ideas" section as an enhancement request. As you rightly point out, the current "out-of-the-box" solution is to have multiple rows in your spreadsheet.

https://www.serenacentral.com/community/ideas

Sorry can't help further.
The reply is currently minimized Show
Accepted Answer
Paul Thompson

Offline
Saturday, February 09 2019, 11:40 PM - #Permalink
0

You could create a script in Excel to parse the comma-delimited fields in the second column and create additional rows in the spreadsheet.

And of course you can use AppScript or ModScript do the parsing and record creation after importing the .XLS into your aux table.

Any scripting effort needs to be balanced with how much time it would take to manually do the work, and often the script would be run. If this is a one-time only import, it might be more cost-effective to have a volunteer manually edit the spreadsheet and split the delimited fields.
The reply is currently minimized Show