Separating a merged list from Excel
Janet_21505
Altair Community Member
I have an excel file which is listing servers and the applications on the server. The applications are in a list format in a single cell in Excel and when imported to Data Prep remain as a list within one row/column. Any ideas on how to get this list to separate into columns or rows. It does not matter to me. I believe there is a carriage return at the end of each line.
I have tried to output to pdf from Excel and then discovered that there are more lines in the list in some cases then Excel can handle. The result of that exercise was that I was not getting all the data from the list in Excel. The list was larger than Excel could handle. I do have my IT trying different file outputs as a possible solution.
Any ideas would be appreciated!
------------------------------
Janet Grimsley
VP, Business Intelligence
The Fauquier Bank
Warrenton VA
5403490215
------------------------------
I have tried to output to pdf from Excel and then discovered that there are more lines in the list in some cases then Excel can handle. The result of that exercise was that I was not getting all the data from the list in Excel. The list was larger than Excel could handle. I do have my IT trying different file outputs as a possible solution.
Any ideas would be appreciated!
------------------------------
Janet Grimsley
VP, Business Intelligence
The Fauquier Bank
Warrenton VA
5403490215
------------------------------
0
Best Answer
-
If there are line breaks in there, you should be able to import the excel file and then use the split functions from the menu drop down arrows on that column:
Select- Into Parts from the Left....
- Chose the check box "Interpret \n as newline and \t as tab
- From the drop down for split delimiter select the \n
Hopefully the number of lines in each cell is not too crazy (my example shows 9). Optional to remove original column (should probably do it). Click Ok.
This will create a long row for each cell. Now select Transform->Unpivot. On the right side under the options drop down, select "All columns"
Click OK.
From here you should be able to do your normal data prep operations to break out the name and the version numbers (using splits).
------------------------------
Chris Porthouse
Senior Professional Services Consultant
Altair
------------------------------
-------------------------------------------
Original Message:
Sent: 08-11-2020 10:25 AM
From: Janet Grimsley
Subject: Separating a merged list from Excel
I have an excel file which is listing servers and the applications on the server. The applications are in a list format in a single cell in Excel and when imported to Data Prep remain as a list within one row/column. Any ideas on how to get this list to separate into columns or rows. It does not matter to me. I believe there is a carriage return at the end of each line.
I have tried to output to pdf from Excel and then discovered that there are more lines in the list in some cases then Excel can handle. The result of that exercise was that I was not getting all the data from the list in Excel. The list was larger than Excel could handle. I do have my IT trying different file outputs as a possible solution.
Any ideas would be appreciated!
------------------------------
Janet Grimsley
VP, Business Intelligence
The Fauquier Bank
Warrenton VA
5403490215
------------------------------"0
Altair EmployeeAnswers
-
If there are line breaks in there, you should be able to import the excel file and then use the split functions from the menu drop down arrows on that column:
Select- Into Parts from the Left....
- Chose the check box "Interpret \n as newline and \t as tab
- From the drop down for split delimiter select the \n
Hopefully the number of lines in each cell is not too crazy (my example shows 9). Optional to remove original column (should probably do it). Click Ok.
This will create a long row for each cell. Now select Transform->Unpivot. On the right side under the options drop down, select "All columns"
Click OK.
From here you should be able to do your normal data prep operations to break out the name and the version numbers (using splits).
------------------------------
Chris Porthouse
Senior Professional Services Consultant
Altair
------------------------------
-------------------------------------------
Original Message:
Sent: 08-11-2020 10:25 AM
From: Janet Grimsley
Subject: Separating a merged list from Excel
I have an excel file which is listing servers and the applications on the server. The applications are in a list format in a single cell in Excel and when imported to Data Prep remain as a list within one row/column. Any ideas on how to get this list to separate into columns or rows. It does not matter to me. I believe there is a carriage return at the end of each line.
I have tried to output to pdf from Excel and then discovered that there are more lines in the list in some cases then Excel can handle. The result of that exercise was that I was not getting all the data from the list in Excel. The list was larger than Excel could handle. I do have my IT trying different file outputs as a possible solution.
Any ideas would be appreciated!
------------------------------
Janet Grimsley
VP, Business Intelligence
The Fauquier Bank
Warrenton VA
5403490215
------------------------------"0 -
I just got a message from Mo suggesting the /n as well. I forget about those advanced split options. I had tried /r, but the /n worked! I have a total of 86 columns after the split. It is beautiful!Chris Porthouse said:If there are line breaks in there, you should be able to import the excel file and then use the split functions from the menu drop down arrows on that column:
Select- Into Parts from the Left....
- Chose the check box "Interpret \n as newline and \t as tab
- From the drop down for split delimiter select the \n
Hopefully the number of lines in each cell is not too crazy (my example shows 9). Optional to remove original column (should probably do it). Click Ok.
This will create a long row for each cell. Now select Transform->Unpivot. On the right side under the options drop down, select "All columns"
Click OK.
From here you should be able to do your normal data prep operations to break out the name and the version numbers (using splits).
------------------------------
Chris Porthouse
Senior Professional Services Consultant
Altair
------------------------------
-------------------------------------------
Original Message:
Sent: 08-11-2020 10:25 AM
From: Janet Grimsley
Subject: Separating a merged list from Excel
I have an excel file which is listing servers and the applications on the server. The applications are in a list format in a single cell in Excel and when imported to Data Prep remain as a list within one row/column. Any ideas on how to get this list to separate into columns or rows. It does not matter to me. I believe there is a carriage return at the end of each line.
I have tried to output to pdf from Excel and then discovered that there are more lines in the list in some cases then Excel can handle. The result of that exercise was that I was not getting all the data from the list in Excel. The list was larger than Excel could handle. I do have my IT trying different file outputs as a possible solution.
Any ideas would be appreciated!
------------------------------
Janet Grimsley
VP, Business Intelligence
The Fauquier Bank
Warrenton VA
5403490215
------------------------------"
As always, you guys ROCK!
Janet
------------------------------
Janet Grimsley
VP, Business Intelligence
The Fauquier Bank
Warrenton VA
5403490215
------------------------------
-------------------------------------------
Original Message:
Sent: 08-12-2020 08:57 AM
From: Chris Porthouse
Subject: Separating a merged list from Excel
If there are line breaks in there, you should be able to import the excel file and then use the split functions from the menu drop down arrows on that column:
Select- Into Parts from the Left....
- Chose the check box "Interpret \n as newline and \t as tab
- From the drop down for split delimiter select the \n
Hopefully the number of lines in each cell is not too crazy (my example shows 9). Optional to remove original column (should probably do it). Click Ok.
This will create a long row for each cell. Now select Transform->Unpivot. On the right side under the options drop down, select "All columns"
Click OK.
From here you should be able to do your normal data prep operations to break out the name and the version numbers (using splits).
------------------------------
Chris Porthouse
Senior Professional Services Consultant
Altair
------------------------------
Original Message:
Sent: 08-11-2020 10:25 AM
From: Janet Grimsley
Subject: Separating a merged list from Excel
I have an excel file which is listing servers and the applications on the server. The applications are in a list format in a single cell in Excel and when imported to Data Prep remain as a list within one row/column. Any ideas on how to get this list to separate into columns or rows. It does not matter to me. I believe there is a carriage return at the end of each line.
I have tried to output to pdf from Excel and then discovered that there are more lines in the list in some cases then Excel can handle. The result of that exercise was that I was not getting all the data from the list in Excel. The list was larger than Excel could handle. I do have my IT trying different file outputs as a possible solution.
Any ideas would be appreciated!
------------------------------
Janet Grimsley
VP, Business Intelligence
The Fauquier Bank
Warrenton VA
5403490215
------------------------------"0