Importing Excel Sheets with wrapped cells content

martindemel

Hi,

 

though I know it's better to avoid wrapped cells in Excel for Analysis, I have a customer who has this kind of data. Importing with the Excel import wizard imports also the wrapped cells, though you'd have to span the rows larger to see all the entries. These columns are character and nominal by default. However could not be used for Analysis. Imagine th first column has multiple countries in one cell (DE, US, ES), the second column has in one cell the related numeric values (e.g. scores like 10, 15, 7). That means the scorefor Germany is 10, for US 15 and ES 7.

 

 How to deal with that kind of data? Is there a way of importing it into seperated lines?

(This is just a simplified version of the data, you can think of additional variables and grouping variables like study/project and so forth where one of these cells belongs to one of the studies each.)

 

What I've tried so far:

To use this data in JMP you can use a multiple response variable and set the property "Multiple Response" to a delimiter "", because a wrap does not have a delimiter as "," or ";". So far so good, but the data is now analyzed by frequencies rather than numeric values. So a bunch of analysis methods cannot be used.

 

Stacking, Splitting and other table manipulations did not work for me as they don't account for a multiple response column and put the whole entry as one value. Expanding to columns I was also not successful, though I just might not found the trick. However this seems to have already many steps involved for a nice solution, but better any solution than none 

 

I guess the best solution is to provide the data in a different structure/format in Excel already. Or do you have any other suggestion?

 

Attached the excel file and a script to import and change the modeling type to Multiple response.

Thanks,

Martin

 

Import.jsl

Excel_ImportWrapCells.xlsx

brady_brady

This data is really ugly due to the wrapping. See if this works; it seems to work for me.

 

The main ideas are:

 

Collapse whitespace, replace newlines with commas, text to columns, contiguous multiple series stack.

 

In addition there are a few cleanup operations like column deletions, empty row deletions, & type conversions.

 

Names Default To Here(1);

dt = Open(pickfile(),
	Worksheets( "Sheet1" ),
	Worksheet Settings(
		1,
		Has Column Headers( 1 ),
		Number of Rows in Headers( 1 ),
		Headers Start on Row( 1 ),
		Data Starts on Row( 2 ),
		Data Starts on Column( 1 ),
	)
);

dt << new column("C2", formula(collapse whitespace(substitute(:Country, "\!n",","))));

dt << new column("D2", formula(collapse whitespace(substitute(:Data, "\!n",","))));

dt << select columns({"C2", "D2"});

dt << text to columns(delimiter(","));

dt << Delete Columns;

dt << Delete Columns (1,2);

collist = dt << get column names(string);
colExpr = insert(expr(columns()), colList);

stackExpr = Expr(
	dtStack = dt << Stack(
		_CCC_, 
		Source Label Column( "Label" ),
		Stacked Data Column( "Data" ),
		Number of Series( 2 ),
		Contiguous
	)
);

substituteInto(stackExpr, expr(_CCC_), nameexpr(colExpr));
stackExpr;

dtStack << delete Columns (1,3);

dtStack << select where (:Data == "") << delete rows();

close(dt, nosave);

column(dtStack,1)<<set name("Country");
column(dtStack,2)<<set name("Data")<<set data type (numeric)<< set modeling type(continuous);