2 Replies Latest reply: Aug 16, 2016 12:55 PM by Clint Siewert RSS

    Trim Parse Substring data in load script?

    Clint Siewert

      Hello all,

       

      I am in a unique situation that I need guidance with. I currently have two load scripts for two separate excel work books.

      There are hundreds of rows of data in each workbook.

       

      The first data sheet has a list of names that reads like below:

      Rodgers, Aaron A ~ Last name - comma - space - First name - space - Middle Initial


      The load script is simply:

      [ExampleSheet1]:

      LOAD

        [Name],

           etc...,

           etc...;

      FROM [...]

      (ooxml, embedded labels, table is ...);

       

      My second data sheet needs to match the first, (make the two relational), but they are quite different.

       

      The second data sheet has a list of names that reads like below:

      "this is a space"Aaron Rodgers ~ SPACE - First name - space - Last name

      There is a space before the name..

       

      Here is the second load script

      [ExampleSheet2]:

      LOAD

           [Name],

           etc...,

           etc...;

      FROM [...]

      (ooxml, embedded labels, table is ...);

       

      #1. I will need to trim off the middle initial from the first data sheet. To do this I assume I need to search for the second space, then trim anything off after that. Make it change from:

       

      Rodgers, Aaron A ----> Rodgers, Aaron


      #2. Change the names from firstName, lastName to be lastName, firstName.  While removing the space before the name.

      Make it change from:

      ' 'Aaron Rodgers ----> Rodgers, Aaron


      So, not sure if this can be done in the load script?  The simple Ltrim, Rtrim, Trim, SubString etc... aren't working for me in the load scripts.  Ltrim([Name]) gives me an error when I try to use it.  No clue how to parse the second sheet accordingly. Thank you in advance!