Text to Columns Splitting one column to many

mustufa2019

I am trying to make multiple columns out of the field below (text to columns) based on the yellow highlighted dividers. I will eventually change the header name as well but first want separate the below.

Output of data:

SJOB: JG541ACS RUN DATE: 04/27/19 TIME: 1:58:31 BANK: 541 ID: 541
TS RSJ RUDY SAN JOSE PR14 ADHOC EBR POSTING = Y
TS RSJ RUDY SAN JOSE PR14 ADHOC EBR STATEMENTS = Y
TS RSJ RUDY SAN JOSE PR14 ADHOC RETROACTIVE PRICING = Y
TS RSJ RUDY SAN JOSE PR14 ADHOC BUYS AND SELLS = Y
TS RSJ RUDY SAN JOSE PR14 ADHOC LIST OF HOLDINGS FOR AUDITOR = Y
TS RSJ RUDY SAN JOSE PR14 ADHOC LOCATION RECONCILIATION = Y
TS RSJ RUDY SAN JOSE PR14 ADHOC REGISTRATION RECONCILIATION = Y
TS RSJ RUDY SAN JOSE PR14 ADHOC SECURITY CROSS REFERENCE = Y
TS RSJ RUDY SAN JOSE PR14 ADHOC SUSPENSE BALANCES = Y
TS RSJ RUDY SAN JOSE PR14 ADHOC UPCOMING MATURITIES = Y
TS RSJ RUDY SAN JOSE PR14 ADHOC BROKERAGE CHARGES DETAIL = Y
TS RSJ RUDY SAN JOSE PR14 ADHOC BROKERAGE CHARGES SUMMARY = Y
TS RSJ RUDY SAN JOSE PR14 ADHOC HOLDINGS = Y
TS RSJ RUDY SAN JOSE PR14 ADHOC VAULT AUDIT = Y
TS RSJ RUDY SAN JOSE PR14 CAPOST SCHEDULE CA POSTING = Y
TS RSJ RUDY SAN JOSE PR14 CAPOST MODIFY CA POSTING RECORD = Y
TS RSJ RUDY SAN JOSE PR14 CAPOST VIEW CA POSTING RECORD = Y
TS RSJ RUDY SAN JOSE PR14 CAPOST DELETE CA POSTING RECORD = Y
TS RSJ RUDY SAN JOSE PR14 CAPOST ENCUMBER SHARES FOR POSTING = Y
TS RSJ RUDY SAN JOSE PR14 CAPOST UNENCUMBER SHARES FOR POSTING = Y
TS RSJ RUDY SAN JOSE PR14 CAPOST RECORD A CORPORATE ACTION = Y
TS RSJ RUDY SAN JOSE PR14 CAPOST CA TAXABILITY POSTING = Y
TS RSJ RUDY SAN JOSE PR14 CAPOST REVERSE CA TRANSACTIONS = Y
TS RSJ RUDY SAN JOSE PR14 CAPOST SHOW RECORD DUMP OF CA = Y
TS RSJ RUDY SAN JOSE PR14 CAPOST GENERATE CA TRACKING = Y
TS RSJ RUDY SAN JOSE PR14 CAPOST CREATE POSTING EXPLANATIONS = Y
XB B55 CHRISTOPHER DO -SEI PR93 SMAC DISPLAY PENDING ITEM = Y
XB B55 CHRISTOPHER DO -SEI PR93 SMAC SMAC WORKSHEET = Y
XB B55 CHRISTOPHER DO -SEI PR93 SMAC RECORD SETTLEMENT = Y
XB B55 CHRISTOPHER DO -SEI PR93 SMAC RECORD MATURITY SETTLEMENTS = Y

Workflow

Datasets

Output

Tips and Tricks

Custom Tools

Common Use Cases

Transformation

Accepted answers

danilang

Hi @mustufa2019

It's a little difficult to see from your image but it looks like your fields are all delimited by at least 2 spaces. If this is the case you can use a single Regex parse tool to extract and rename your columns. The regex is this

(.*?)\s{2,}(.*?)\s{2,}(.*?)\s{2,}(.*?)\s{2,}(.*?)\s{2,}(.*)

Each of the (.*?) matches any number of characters until the next separator \s{2,}. This matches 2 or more spaces "\s". The final (.*) matches all the rest of the characters. In total this line matches 6 groups of characters each separated by at least 2 spaces.

The configuration of the Regex parse tool also also you to rename and set the type and size of each of the output fields corresponding to these groups

The result is this

If your fields aren't separated by at least to 2 spaces, please post an actual sample so that we can help you. Perhaps we'll find that @tonypreece's excellent solution is optimal.

Another possibility is that your data is actually column based. In this case, try importing it as a flat file and defining the columns directly in the input tool

Dan

Split.yxmd

All comments

tonypreece

Hey @mustufa2019

You can use a Text2Columns tool with a space delimiter, set it to eight columns and leave extra in the last column.

You then need a formula tool to join the three columns together for "Rudy San Jose". This works on your sample data because that column contains three words (two spaces) in all your data, but if that changes it won't work.

To clean it up, I've added in a data cleansing tool to remove any leading and trailing whitespace, a sort to put the data in the right order and a sample to remove the first row (headings).

danilang

Hi @mustufa2019

(.*?)\s{2,}(.*?)\s{2,}(.*?)\s{2,}(.*?)\s{2,}(.*?)\s{2,}(.*)

The configuration of the Regex parse tool also also you to rename and set the type and size of each of the output fields corresponding to these groups

The result is this

If your fields aren't separated by at least to 2 spaces, please post an actual sample so that we can help you. Perhaps we'll find that @tonypreece's excellent solution is optimal.

Another possibility is that your data is actually column based. In this case, try importing it as a flat file and defining the columns directly in the input tool

Dan

Split.yxmd

tonypreece

Nice solution @danilang. I tried to RegEx it first but failed! New to RegEx this week and need to practice more!

mustufa2019

@danilang Perfect solution! worked right away! thank you so much!

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

AdamR 335