Databases
StackOverflow2013
Postsdbo
POSplit one Name Column into Two Columns using SSIS
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSplit one Name Column into Two Columns using SSIS
    text
    copied!<p>I am working with SQL Server 2012 and Visual Studio 2010 with Excel 2007. </p> <p>I am building a package that will pull information from a <code>.CSV</code> file and import it into a table in a DB that we are creating for an HRIS project. </p> <p>The <code>.CSV</code> file has the following fields starting in B9 and running through column J (row length will vary)</p> <pre><code>| PersNo | IDNo | Name | PArea | OrgUnit | OrgName | UserID | EntryDate | </code></pre> <p>The issue I have is that I need to take the <code>Name</code> column and split it into a <code>FirstName</code> and <code>LastName</code> columns. I have scoured the internet and found a number of forums that detail methods that use Derived Columns, Conditional Split, Script Component and MultiCast. None of them were helpful or fit my needs. </p> <p>The issue is complicated by the name arrangement. I have three different name types that can be displayed. </p> <pre><code>John Smith John A Smith John Smith III </code></pre> <p>The last two they will need to be split differently. </p> <p>If there is a middle initial they want it split as such. </p> <pre><code>| FirstName | LastName | ------------------------ | John | Smith A | </code></pre> <p>i.e. Smith and the Middle Initial will be in the <code>LastName</code> column</p> <p>If they have a suffix then they will need to be split as such. </p> <pre><code>| FirstName | LastName | ------------------------- | John | Smith III | </code></pre> <p>i.e. Smith and the suffix will be in the <code>LastName</code> column</p> <p>I need to know if there is a way that I can split this out in one package.</p> <p>Eventually this will be automated to run daily so I will also have to utilize a lookup I assume to filter out any duplicates and enter only new data.</p> <p>I appreciate anything information that anyone can provide. </p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload