Viewing CSV in columns

[sakis_s]

Hi!
I have some files in CSV format. If i open them with Excel i got the column mode i want. It's like this:


Now if i open them with SlickEdit, i've got this view:


Even if i choose "Format Columns" from Document menu, i got a view like this:


Can i got the view of excel somehow?

[Tim Kemp]

I realize this isn't going to be very helpful, but I am constantly generating and opening CSV files. To get the Excel view, I use Excel. I use SlickEdit to select rows and columns and do other manipulations that are more text based. Then I save it with a CSV extension and open it in Excel.

[jporkkahtc]

If you use <TAB> instead of semicolon as a separator, the results in Slick are better.

To make it really work would then require an automated way to set the tabstops to get the columns to align nicely.

[Bamsen]

I had a similar problem and wrote a short macro to help me.
You select the separator and the new column marker and it calculates the correct width for each column.
Maybe you can customize it to your need. ( Just do a Google Translate for the Norwegian comments..  )

Code: [Select]

/**
 * Prosedyre: typeless MAS_Format_Column()
 *
 * @key Alt + B + F
 *
 * @author Morten (2012-06-26)
 *
 * @return typeless
 */
_command MAS_Format_Column() name_info(','VSARG2_MACRO|VSARG2_MARK|VSARG2_REQUIRES_MDI_EDITORCTL)
{
  int cur_mark = _alloc_selection('B');
  _select_char(cur_mark);
  int   left_edge = p_left_edge;
  int   cursor_y  = p_cursor_y;
  _str  search_options = "lh@";
  int   SearchStatus = 0;
  _str  Linje='',aMatch='';
  // int dlgResult = textBoxDialog ('Format Column',0,5000,'Perl regular expressions','','','Eksisterende skilletegn:\t','Nye  skilletegn: | ');
  _param1=Siste_Skilletegn;
  _param2=Siste_NySkille;
  int dlgResult = textBoxDialog ('Format Column',0,5000,'Perl regular expressions','','','Eksisterende skilletegn:'Siste_Skilletegn,'Nye  skilletegn:'Siste_NySkille);
  _param2=stranslate(_param2,' ','\t','I');
  Siste_Skilletegn=_param1;
  Siste_NySkille=_param2;
  int Start=0;
  int End=0;
  boolean  Selected=false;
  typeless Dummy;
  if (_get_selinfo(Start,End,Dummy)) {  // Sjekk om det er en selection og finn første kolonne
    top();
  } else {
    search_options = search_options :+ "m";
    Selected=true;
    _begin_select();
  }
  int KolonneBredde[];
  _str KolonneData[];
  int i=0;
  for (;;) { // Løp gjennom alle linjer for Ã¥ finne maks kolonnebredde.
    get_line(Linje);
    KolonneData=MST_Split_To_StrArray(Linje,_param1);
    for (i=0;i<KolonneData._length();i++) { // Sjekk alle kolonner
      if (i == KolonneBredde._length()) { // Første gang mÃ¥ array elementet initialiseres
        KolonneBredde[i]=KolonneData[i]._length();
      } else { // har kolonnedata
        if (KolonneData[i]._length() > KolonneBredde[i] ) { // Denne er større
          KolonneBredde[i]=KolonneData[i]._length();
        }
      }
    }
    if (down()) break;
    if (Selected) {
      if (End == 1) {
        if (_end_select_compare() >= 0) break; // Ikke ta med cursor linje hvis kolonne 1
      } else {
        if (_end_select_compare() > 0) break;
      }
    }
  }

  if (select_active()) {  // Sjekk om det er en selection, og begynn pÃ¥ nytt.
    _begin_select();
  } else {
    top();
  }

  _str NewLine='';
  int Delta=0;
  for (;;) { // Løp gjennom alle linjer for Ã¥ justere kolonner
    get_line(Linje);
    KolonneData=MST_Split_To_StrArray(Linje,_param1);
    NewLine='';
    for (i=0;i<KolonneData._length()-1;i++) { // Sjekk alle kolonner
      Delta=MST_Count_HighBit(KolonneData[i]); // MÃ¥ ta hensyn til norske tegn.
                                               // Hvert tegn mÃ¥ ha +1 til bredde
      NewLine = NewLine :+ _pad(KolonneData[i],KolonneBredde[i]+Delta,' ') :+ _param2;
    }
    NewLine = NewLine :+ KolonneData[i];
    replace_line(NewLine);
    if (down()) break;
    if (Selected) {
      if (End == 1) {
        if (_end_select_compare() >= 0) break; // Ikke ta med cursor linje hvis kolonne 1
      } else {
        if (_end_select_compare() > 0) break;
      }
    }
  }

  _begin_select(cur_mark);
  set_scroll_pos(left_edge, cursor_y);
  _free_selection(cur_mark);
}


[sakis_s]

I had a similar problem and wrote a short macro to help me.
You select the separator and the new column marker and it calculates the correct width for each column.
Maybe you can customize it to your need. ( Just do a Google Translate for the Norwegian comments..  )

Code: [Select]

/**
 * Prosedyre: typeless MAS_Format_Column()
 *
 * @key Alt + B + F
 *
 * @author Morten (2012-06-26)
 *
 * @return typeless
 */
_command MAS_Format_Column() name_info(','VSARG2_MACRO|VSARG2_MARK|VSARG2_REQUIRES_MDI_EDITORCTL)
{
  int cur_mark = _alloc_selection('B');
  _select_char(cur_mark);
  int   left_edge = p_left_edge;
  int   cursor_y  = p_cursor_y;
  _str  search_options = "lh@";
  int   SearchStatus = 0;
  _str  Linje='',aMatch='';
  // int dlgResult = textBoxDialog ('Format Column',0,5000,'Perl regular expressions','','','Eksisterende skilletegn:\t','Nye  skilletegn: | ');
  _param1=Siste_Skilletegn;
  _param2=Siste_NySkille;
  int dlgResult = textBoxDialog ('Format Column',0,5000,'Perl regular expressions','','','Eksisterende skilletegn:'Siste_Skilletegn,'Nye  skilletegn:'Siste_NySkille);
  _param2=stranslate(_param2,' ','\t','I');
  Siste_Skilletegn=_param1;
  Siste_NySkille=_param2;
  int Start=0;
  int End=0;
  boolean  Selected=false;
  typeless Dummy;
  if (_get_selinfo(Start,End,Dummy)) {  // Sjekk om det er en selection og finn første kolonne
    top();
  } else {
    search_options = search_options :+ "m";
    Selected=true;
    _begin_select();
  }
  int KolonneBredde[];
  _str KolonneData[];
  int i=0;
  for (;;) { // Løp gjennom alle linjer for Ã¥ finne maks kolonnebredde.
    get_line(Linje);
    KolonneData=MST_Split_To_StrArray(Linje,_param1);
    for (i=0;i<KolonneData._length();i++) { // Sjekk alle kolonner
      if (i == KolonneBredde._length()) { // Første gang mÃ¥ array elementet initialiseres
        KolonneBredde[i]=KolonneData[i]._length();
      } else { // har kolonnedata
        if (KolonneData[i]._length() > KolonneBredde[i] ) { // Denne er større
          KolonneBredde[i]=KolonneData[i]._length();
        }
      }
    }
    if (down()) break;
    if (Selected) {
      if (End == 1) {
        if (_end_select_compare() >= 0) break; // Ikke ta med cursor linje hvis kolonne 1
      } else {
        if (_end_select_compare() > 0) break;
      }
    }
  }

  if (select_active()) {  // Sjekk om det er en selection, og begynn pÃ¥ nytt.
    _begin_select();
  } else {
    top();
  }

  _str NewLine='';
  int Delta=0;
  for (;;) { // Løp gjennom alle linjer for Ã¥ justere kolonner
    get_line(Linje);
    KolonneData=MST_Split_To_StrArray(Linje,_param1);
    NewLine='';
    for (i=0;i<KolonneData._length()-1;i++) { // Sjekk alle kolonner
      Delta=MST_Count_HighBit(KolonneData[i]); // MÃ¥ ta hensyn til norske tegn.
                                               // Hvert tegn mÃ¥ ha +1 til bredde
      NewLine = NewLine :+ _pad(KolonneData[i],KolonneBredde[i]+Delta,' ') :+ _param2;
    }
    NewLine = NewLine :+ KolonneData[i];
    replace_line(NewLine);
    if (down()) break;
    if (Selected) {
      if (End == 1) {
        if (_end_select_compare() >= 0) break; // Ikke ta med cursor linje hvis kolonne 1
      } else {
        if (_end_select_compare() > 0) break;
      }
    }
  }

  _begin_select(cur_mark);
  set_scroll_pos(left_edge, cursor_y);
  _free_selection(cur_mark);
}


Thank you so much for your suggestions, really!
I tried to run your script (i'm an absolute beginner in Slickedit) but i couldn't.
What i've tried:
1)Created a .txt, pasted your code and saved as "test.e"
2)Opened Slickedit, went to Macro / Load Module, selected the "test.e" file and when i'm trying to open it, i get the following error:


Do you know why this message appears?
I really appreciate your help

[Bamsen]

The code was just snipped from my macro library.
You may need the following lines at the top of your file:

Code: [Select]

#pragma option(strict,on)
#include "slick.sh"
#import "se/datetime/DateTime.e"

Hope that helps. 

[sakis_s]

The code was just snipped from my macro library.
You may need the following lines at the top of your file:

Code: [Select]

#pragma option(strict,on)
#include "slick.sh"
#import "se/datetime/DateTime.e"

Hope that helps. 

Perfect! That seemed to get the trick, Thank you!
Now.. how am i run it on my .csv file?   
So sorry for my noob questions but i haven't done this before. 
Thanks again

[Bamsen]

With your CSV file loaded, go to menu Macro and List Macros (or press ctrl+m) and select MAS-Format-Column and Run.
This should open a dialog box.
The first input is "Eksisterende skilletegn" -> "Current divider": Here you enter the divider in use in your file. This should be ; in your case.
This field takes regex expressions so for tabulator it would be \t
The second input field is the new divider. So from your example just a space should be ok.

Then just press OK, and your text file should be reformatted.

Note: This function can be limited by a selection, and has a bug the causes it to use any selection in any open document, so make sure your CSV file is the only open document, or that no other open documents have an active selection.

[sakis_s]

With your CSV file loaded, go to menu Macro and List Macros (or press ctrl+m) and select MAS-Format-Column and Run.
This should open a dialog box.
The first input is "Eksisterende skilletegn" -> "Current divider": Here you enter the divider in use in your file. This should be ; in your case.
This field takes regex expressions so for tabulator it would be \t
The second input field is the new divider. So from your example just a space should be ok.

Then just press OK, and your text file should be reformatted.

Note: This function can be limited by a selection, and has a bug the causes it to use any selection in any open document, so make sure your CSV file is the only open document, or that no other open documents have an active selection.

When i go to "List Macros" i get an error "No user macros defined". I believe macro is not saved?
If i go to Macro/Load Module and select file "test.e", it seems to load the macro correctly:


but when i go to Macro/ Save last-macro option is greyed out.


Any ideas how to appear it in "List Macros"?

Thank you so much @Bamsen for your help.

[Bamsen]

Since it is loaded, it is already saved.
What happens when you press CTRL+M

[sakis_s]

Since it is loaded, it is already saved.
What happens when you press CTRL+M

Nothing happens by hitting ctrl+m 

[Bamsen]

Do you have Skype?
Can you try to skype me at morten.steien@evry.com

[Dan]

What happens if you click at the bottom left of the editor and click to open the command line and then type the name?  It will have completion if it is loaded so you won't have to type the whole name.  If not, the module is probably not loaded correctly.

[sakis_s]

What happens if you click at the bottom left of the editor and click to open the command line and then type the name?  It will have completion if it is loaded so you won't have to type the whole name.  If not, the module is probably not loaded correctly.

Hi Dan and thanks for your response!
You're right, module wasn't loaded correctly. There were missing some procedures from the code and SlickEdit was generating errors but i didn't notice on the left bottom corner.
Thanks to "Bamsen", he helped me a lot through Skype and his code works like a harm for my needs!
So, problem solved!
Thanks everyone for your responses, i really appreciate your help!