Creating columns from multi-line data

I have an excel file from a user that I have put into Data Prep. One of the columns contains multi lines of data. Can Data Prep split those lines into separate columns?

------------------------------
Janet Grimsley
VP, Business Intelligence
The Fauquier Bank
Warrenton VA
(540) 349-0215
------------------------------

Find more posts tagged with

English

Monarch

Accepted answers

CPorthouse

Yes. Use the drop down menu option for the column with the multi line data you would like to split and select "Split Into Parts from the Left..."

When the Left Split dialog pops up, select the checkbox "Interpret \n as newline and \t as tab"

From the Split Delimiter drop down option, select \n (or type in \n in the box)

Choose the number of columns you would like to split the column data into. By default, DPS will choose the how many columns it thinks you need based on the sample row data in the column you chose. Keep in mind you can increase or decrease this number to allow for data that may not be available yet. For instance, in my example it only found row data that had at most 3 lines but if I know that the data could contain 5 rows, I would increase this number to 5.

You may also select the "Remove original column" checkbox at this time if you no longer require the original column.

Click "OK" and your data will now be displayed:

Just as an FYI, you can also achieve the same results in Monarch Classic by creating a new Formula Field and using the "Textline()" function. You will manually have to create each column for each row of data you would like to extract.

------------------------------
Chris Porthouse
Senior Implementation & Integration Engineer
Altair
------------------------------
-------------------------------------------
Original Message:
Sent: 05-15-2020 03:23 PM
From: Janet Grimsley
Subject: Creating columns from multi-line data

I have an excel file from a user that I have put into Data Prep. One of the columns contains multi lines of data. Can Data Prep split those lines into separate columns?

------------------------------
Janet Grimsley
VP, Business Intelligence
The Fauquier Bank
Warrenton VA
(540) 349-0215
------------------------------"

All comments

CPorthouse

Yes. Use the drop down menu option for the column with the multi line data you would like to split and select "Split Into Parts from the Left..."

When the Left Split dialog pops up, select the checkbox "Interpret \n as newline and \t as tab"

From the Split Delimiter drop down option, select \n (or type in \n in the box)

I have an excel file from a user that I have put into Data Prep. One of the columns contains multi lines of data. Can Data Prep split those lines into separate columns?

------------------------------
Janet Grimsley
VP, Business Intelligence
The Fauquier Bank
Warrenton VA
(540) 349-0215
------------------------------"

Janet_21505

Yes. Use the drop down menu option for the column with the multi line data you would like to split and select "Split Into Parts from the Left..."

When the Left Split dialog pops up, select the checkbox "Interpret \n as newline and \t as tab"

From the Split Delimiter drop down option, select \n (or type in \n in the box)

Choose the number of columns you would like to split the column data into. By default, DPS will choose the how many columns it thinks you need based on the sample row data in the column you chose. Keep in mind you can increase or decrease this number to allow for data that may not be available yet. For instance, in my example it only found row data that had at most 3 lines but if I know that the data could contain 5 rows, I would increase this number to 5.

You may also select the "Remove original column" checkbox at this time if you no longer require the original column.

Click "OK" and your data will now be displayed:

Just as an FYI, you can also achieve the same results in Monarch Classic by creating a new Formula Field and using the "Textline()" function. You will manually have to create each column for each row of data you would like to extract.

------------------------------
Chris Porthouse
Senior Implementation & Integration Engineer
Altair
------------------------------
-------------------------------------------
Original Message:
Sent: 05-15-2020 03:23 PM
From: Janet Grimsley
Subject: Creating columns from multi-line data

I have an excel file from a user that I have put into Data Prep. One of the columns contains multi lines of data. Can Data Prep split those lines into separate columns?

------------------------------
Janet Grimsley
VP, Business Intelligence
The Fauquier Bank
Warrenton VA
(540) 349-0215
------------------------------"

Works beautifully! Thanks, Chris.

------------------------------
Janet Grimsley
VP, Business Intelligence
The Fauquier Bank
Warrenton VA
(540) 349-0215
------------------------------
-------------------------------------------
Original Message:
Sent: 05-16-2020 06:14 AM
From: Chris Porthouse
Subject: Creating columns from multi-line data

Yes. Use the drop down menu option for the column with the multi line data you would like to split and select "Split Into Parts from the Left..."

When the Left Split dialog pops up, select the checkbox "Interpret \n as newline and \t as tab"

From the Split Delimiter drop down option, select \n (or type in \n in the box)

Just as an FYI, you can also achieve the same results in Monarch Classic by creating a new Formula Field and using the "Textline()" function. You will manually have to create each column for each row of data you would like to extract.

------------------------------
Chris Porthouse
Senior Implementation & Integration Engineer
Altair
------------------------------

Original Message:
Sent: 05-15-2020 03:23 PM
From: Janet Grimsley
Subject: Creating columns from multi-line data

I have an excel file from a user that I have put into Data Prep. One of the columns contains multi lines of data. Can Data Prep split those lines into separate columns?

------------------------------
Janet Grimsley
VP, Business Intelligence
The Fauquier Bank
Warrenton VA
(540) 349-0215
------------------------------"