topic importing from excel with a new line in a cell in Discussions

importing from excel with a new line in a cell

ron_horne — Sat, 10 Jun 2023 20:42:48 GMT

Dear Members of the community,

Does anyone know how to import data from excel correctly when there are line brakes within cells?

in the attached excel sheet, cells B4 and B7 have a line brake that makes the import incorrect no matter how i try to import or just copy and paste into JMP. Is there an importing method that is robust to this?

line brakes can be entered into a cell in excel using Alt+Enter or if more than one paragraph is copied from other programs. It throws a spanner in the works and with large files causes havoc.

i am using JMP 14 but i do not think it is a version issue.

Many thanks,

Ron

Re: importing from excel with a new line in a cell

Byron_JMP — Mon, 01 Feb 2021 14:45:08 GMT

I just had this problem last week, drove me nuts!

I threw in some recode after the import to collapse whitespace and that fixed it.

Names Default To Here( 1 );
dt = Data Table( "Sheet1" );
dt << Begin Data Update;
dt << Recode Column(
	dt:Nominal,
	{Collapse Whitespace( _rcNow )},
	Target Column( :Nominal )
);
dt << End Data Update;

Re: importing from excel with a new line in a cell

ron_horne — Mon, 01 Feb 2021 18:37:43 GMT

Thank you @Byron_JMP for your suggestion. But it didn't work in my case, perhaps we have different import settings.

my original table in excel looks like this, with line brakes in cells B4 and B7

using the on the fly copy and paste with column names i get the following image. where every line brake is interpenetrated as a new table row,

using the import wizard i get the following where the original line brakes within the cells have been removed together with the reminder of its content.

Therefore, I do not get a whitespace which its removal would solve anything.

Ideally, this could be fixed within JMP as part of the way it reads the data while importing. JMP should

Interpret this as either a new line within a cell or just a space between characters.

when opening the excel file in SAS using default settings, the result is much better. The line brake in the cell was just removed. This maintains the overall integrity of the cells, rows and columns of the file.

Iit would have been better to add a space though. Not sure how i would be able to add it myself later if i had to use the text for analysis.

the way i did manage to solve it so far is by searching and replacing all the new lines in Excel prior to the import. the new line character to search for in excel is Ctrl+j. replacing it with a simple space has given me the the least confusing outcome.

Hope you can address this to the right person to fix it in future versions of the program.

If anyone has other solutions please do share. I am sure many files imported from excel and other formats that allow for line brakes within a cell has produced imperfect data sets.

Re: importing from excel with a new line in a cell

Byron_JMP — Mon, 01 Feb 2021 19:33:42 GMT

I opened your Excel file with the Excel Import Wizard.

Then used both Recode as well as a formula to collapse whitespace

Both approaches use the same function. Although the function is called Collapse Whitespace, it might as well be called, One space between each alphanumeric symbol in a string.

Although we aren't talking MS stuff here, diagnosing the issue in the Excel sheet is more important because it is a data entry error there, as well as a source of miserable problems in Excel too. The hard return can either look like a no space at all, or maybe a space if the wrap role it turned on. Either way its a S in S out kind of problem.

Re: importing from excel with a new line in a cell

ron_horne — Mon, 01 Feb 2021 21:53:34 GMT

Thank you very much @Byron_JMP

I have now noticed that the import did work, i just needed to increase the height of the rows to see it properly.

Re: importing from excel with a new line in a cell

Byron_JMP — Tue, 02 Feb 2021 02:15:33 GMT

Whew! Super glad I wasn't going crazy there. : )