SharePoint XML Import - Handling semicolon and pound sign

[Anonymous]

My multi-value fields coming from SharePoint XML are very strange.  Sample: ;#Product1;#Product2;#Product3;#

The semicolon and pound sign prefix the first value, delimit all inner values, and appear after the last value.  My current load script reads the first or the last ;# as a NULL and creates a new record in the associated table with a NULL that I don't want.

Hard to explain so please see attached.  Someone probably has a better load script but in the end I am trying to store the multiple value field in a separate table that joins to the main table on Order ID.

[settu_periasamy · Accepted solution]

May be like this?

Orders:
LOAD * INLINE [
    OrderID, Products
    1, ;#Word;#QlikView;#Chrome;#
    2, ;#Firefox;#Visual Studio;#Chrome;#
    3, ;#Visual Studio;#QlikView;#Excel;#
    4, ;#QlikView;#Word;#Excel;#
    5, ;#Outlook;#Excel;#PowerPoint;#
    6, ;#Excel;#SnagIT;#PowerPoint;#
    7, ;#PowerPoint;#Firefox;#SnagIT;#
    8, ;#QlikView;#PowerPoint;#
    9, ;#Chrome;#Word;#
    10, ;#Chrome;#Firefox;#
    11, ;#Excel;#Visual Studio;#
    12, ;#Firefox;#QlikView;#
    13, ;#PowerPoint;#
    14, ;#Excel;#
    15, ;#PowerPoint;#
];


Products:
LOAD OrderID,SubField(Mid(Products,3,Index(Products,';#',-1)-3),';#') as Product Resident Orders;

Edit:

This one also gives same result

Products:
LOAD OrderID,SubField(Mid(Products,3,len(Products)-4),';#') as Product
Resident Orders;

[Anonymous]

Testing this in a few of my fields now.  I don't understand how this is looping through the Products field to separate multi-values.

[settu_periasamy]

Subfield will give the multi values based on the separator.  Is that working or not?

[Anonymous]

Yes and no.  I'm marking it as correct but my requirement just expanded a bit and some records come in with a single value containing no ;# and those values are being messed up in the function.  I guess I could check for ;# and perform the separation if present, otherwise leave as is?

[settu_periasamy]

No problem Nick. If possible,  post the different samples, let's try to achieve