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!
