Creating columns from multi-line data
------------------------------
Janet Grimsley
VP, Business Intelligence
The Fauquier Bank
Warrenton VA
(540) 349-0215
------------------------------
Best Answer
-
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
------------------------------"0
Altair EmployeeAnswers
-
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
------------------------------"0 -
Works beautifully! Thanks, Chris.Chris Porthouse said: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
------------------------------"
------------------------------
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)
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
------------------------------"0