270441_en-US

liliconcom

Unstacking a Stacked Excel Cell or Parsing Free-form Text to Generate Multiple Rows

We all run into some version of this: Excel table formatted to look nice, no thought given to analyzability.

In the free-form text cell, it looks like this:

 

m/d: text [alt+enter]
m/d: text [alt+enter]
m/d: text [alt+enter]
m/d: text [alt+enter]
...
m/d: text [alt+enter]

 

When the data are exported to JMP, the cell gets collapsed into: m/d: text m/d: text m/d: text ... m/d: text.
I want to parse that long string with n elements into a separate row for each element, not split into separate columns. n is variable. This would create a new datatable that copies the other columns and adds the parsed element. Instead of Text-to-Columns, this is Text-to-More-Rows.

Thankfully #/# is used consistently, so I could count those and loop n times.

Has anyone out there done something similar?

Mass CustomizationRe: Unstacking a Stacked Excel Cell or Parsing Free-form Text to Generate Multiple Rows

I do this sort of thing a lot; you just have to slog through it.  Here's an example where Column 1 has multiple values, and Column 2 is static.

dt = New Table( "Test", Add Rows( 3 ), Set Cell Height( 83 ),
 New Column( "Column 1", Character, "Nominal",
  Set Values(
{"5/3: ABCDE
5/4: DEF
5/5: GHI",
"5/6: xyz
5/7: foo
5/8: bar
5/9: Hello
5/10: World",
"4/16: The
4/17: Entire
4/18: Scheme
4/19: Of
4/20: Things
4/21: Is broken"} ),
  Set Display Width( 156 )
 ),
 New Column( "Column 2", Character, "Nominal", Set Values( {"A", "B", "C"} ) )
);

dtnew = new table("Text_to_Rows",
 new column("Column 1", Character, "Nominal"),
 new column("Column 2", Character, "Nominal"),
);

delim = "
";
m = 0;
for (i = 1, i <= nrows(dt), i++,
 c1_words = words(dt:column 1[i], delim);
 one_c2   = dt:column 2[i];

 for (k = 1, k <= nitems(c1_words), k++,
  dtnew << add rows(1);
  m++;
  dtnew:Column 1[m] = c1_words[k];
  dtnew:Column 2[m] = one_c2;
 );
);

 

Re: Unstacking a Stacked Excel Cell or Parsing Free-form Text to Generate Multiple Rows

Peter:

Thanks! That did the trick. I owe you a drink of your choice.
I am working on the creators of the data to input the data in flat format...

Re: Unstacking a Stacked Excel Cell or Parsing Free-form Text to Generate Multiple Rows

Ha ha sounds good.  Hope to see you at Discovery 2020.