Author Topic: Transpose column data to rows  (Read 256 times)

sakis_s

  • Community Member
  • Posts: 22
  • Hero Points: 0
  • Is everybody in? The ceremony is about to begin.
Transpose column data to rows
« on: January 30, 2021, 05:35:58 pm »
I'm trying to find a way to transpose column data to lines.
Exactly like "Paste Special" with "Transpose" checked in Excel.
Anyone knows how can i do that?

Thank you very much for your time.

Graeme

  • Senior Community Member
  • Posts: 2515
  • Hero Points: 327
Re: Transpose column data to rows
« Reply #1 on: January 31, 2021, 05:02:07 am »
depending on how your data is formatted you might be able to record a macro.  the macro below transposes ten rows of the digits 123456 into six rows
123456
123456
...
becomes
1111111111
...
6666666666

you need some blank lines below your data - 4 more rows than the number of rows in your data, and say 4 blank rows above the data

123456
123456
123456

so an example with 3 rows, put the cursor on the first 1, start record a macro,
press ctrl B to start a rectangular selection (select-block)
press down arrow twice so that the three 1s are selected
press ctrl x to cut
press cursor down until you get below your data
press ctrl v to paste the column
press (right arrow delete) repeatedly until the column has become a row
press home to go to the start of the row
press shift down arrow to select the row
press ctrl x to cut the row
press cursor up repeatedly until you are on a blank line above your original data
press ctrl v
press down arrow until you're on the first row of your original data

if that looks useful then you can customise the macro to let the user enter the number of rows and columns and column width etc.


Code: [Select]
_command last_recorded_macro() name_info(','VSARG2_MACRO|VSARG2_MARK|VSARG2_REQUIRES_EDITORCTL)
{
   _macro('R',1);
   select_block();
   cursor_down(9);
   cut();
   cursor_down(10);
   c_enter();
   c_enter();
   c_enter();
   c_enter();
   c_enter();
   c_enter();
   c_enter();
   c_enter();
   c_enter();
   c_enter();
   c_enter();
   cursor_up(10);
   paste();
   cursor_right();
   linewrap_delete_char();
   cursor_right();
   linewrap_delete_char();
   cursor_right();
   linewrap_delete_char();
   cursor_right();
   linewrap_delete_char();
   cursor_right();
   linewrap_delete_char();
   cursor_right();
   linewrap_delete_char();
   cursor_right();
   linewrap_delete_char();
   cursor_right();
   linewrap_delete_char();
   cursor_right();
   linewrap_delete_char();
   end_line();
   c_enter();
   cursor_up();
   deselect();
   _select_char('','E');
   cursor_down();
   select_it("CHAR",'','E');
   cut();
   cursor_up(13);
   paste();
   cursor_right(2);
}

Dennis

  • SlickEdit Team Member
  • Senior Community Member
  • *
  • Posts: 3226
  • Hero Points: 459
Re: Transpose column data to rows
« Reply #2 on: February 02, 2021, 05:11:51 pm »
My algorithm would be something more like this:

1) select the column using a block selection and copy (Ctrl+C)
2) Go to the bottom of the file (Ctrl+End), open a blank line, and paste
3) The cursor will be left on the top line of the pasted block
4) Select to the end of the file (Ctrl+Shift+End)
5) Convert the selection to multiple cursors (Ctrl+Shift+|)
6) Move all cursors to the end of the line (End) and unselect (Ctrl+U)
7) Then hit "Delete" to join all the lines.
8 ) You can then type space, or tab, or comma to delimit the columns.

Because of step 5, you can not record a macro to do this, but the process is simple enough to use of a one-off data reorganization like this.

Graeme

  • Senior Community Member
  • Posts: 2515
  • Hero Points: 327
Re: Transpose column data to rows
« Reply #3 on: February 03, 2021, 08:10:05 am »
I was trying to figure out where macro recording goes wrong for this but instead I found a way to hang slickedit.  Position the cursor on the first "1" below - run the macro "temp3"  - multiple cursors get created correctly.  Now run "last_recorded_macro" below to try and complete the transpose and slick hangs.  I only waited 20 seconds though  - twice.  Using 25.0.1 with no hotfixes.

Quote
123456
123456
123456
123456
123456
123456
123456
123456
123456




Code: [Select]
_command temp3() name_info(','VSARG2_MACRO|VSARG2_MARK|VSARG2_REQUIRES_EDITORCTL)
{
   _macro('R',1);
   select_block();
   cursor_down(8);
   last_event(name2event('C_C'));copy_to_clipboard();
   bottom_of_buffer();
   slick_enter();
   linewrap_rubout();
   linewrap_rubout();
   linewrap_rubout();
   paste();
   deselect();
   _select_char('','E');
   bottom_of_buffer();
   select_it("CHAR",'','E');
   execute("convert-to-multiple-cursors",'a');
   //cursor_left();
   //end_line();
   //linewrap_delete_char();
   //cursor_up();
}


_command last_recorded_macro() name_info(','VSARG2_MULTI_CURSOR|VSARG2_MACRO|VSARG2_MARK|VSARG2_REQUIRES_EDITORCTL)
{
   _macro('R',1);
   end_line();
   deselect();
   linewrap_delete_char();
   cursor_up();
}

Dennis

  • SlickEdit Team Member
  • Senior Community Member
  • *
  • Posts: 3226
  • Hero Points: 459
Re: Transpose column data to rows
« Reply #4 on: February 03, 2021, 03:03:27 pm »
Get hotfix 2 when it is available, that should fix the multiple cursor hang issue.