Align tab-delmited data into columns (like a table or excel)

Home Forums Multi-Edit User Created Macros Align tab-delmited data into columns (like a table or excel)

This topic contains 22 replies, has 29,046 voices, and was last updated by  Vincentcnbres 6 years, 2 months ago.

Viewing 15 posts - 1 through 15 (of 23 total)
  • Author
    Posts
  • #1074

    samej71
    Participant

    This is a copy-over from the TWiki site.

    This macro will take a tab-delimited file and adjust the ruler/tab settings to the largest bit of data it finds for each row, similar to the "auto column size" feature in Excel.

    I have this tied to a key combination, so I can re-run it as I update the data. I also have it set to auto-run as a post-load macro for .tsv files, so they automatically appear in columns.

    This should run in both pre 9.0 and in 9.10 versions of MultiEdit (I think).

    With some of the features planned in the update releases to 9.10 I should be able to update this macro to work for other-delimited files, as well as properly handle delimiters within quotes. But until then, I hope someone else besides me finds this useful for viewing data.

    Enjoy.

    BTW – even though the comments in the macro say to save the file as something else, leave the file named _aligntabs.s – I had issues with longer file names and I know this name works.

    –James
    _aligntabs.s

    #4533

    CharlesG
    Participant

    James and others,

    I need a routine to do the same as yours but for comma-delimited fields. Can this be done just by changing the 1st param in the search_fwd() call?

    Thanks!

    P.S. Also I am from Watseka IL; Are ytou still in Peoria?

    #4542

    samej71
    Participant

    James and others,

    I need a routine to do the same as yours but for comma-delimited fields. Can this be done just by changing the 1st param in the search_fwd() call?
    [/quote:vs5bfi4b]

    Sorry, but not at this time. If you change the parameter to a comma it will indeed mark the proper locations in the tab bar, but it will have no effect since there are no tabs present in the file to make use of the tab settings my macro sets up. It also doesn’t take into account commas that might legimately appear in a column of data. For example “Last, First” would not be treated as a single field, it’d be split into two columns.

    What you’re wanting to do (and others have asked too) should be possible (if I’m not mistaken) in version 10 of ME, possibly a subversion release of 9.10. Right now some functionality a macro like this needs to align columns on commas is not present in ME but is slated to be added in one of the upcoming releases.

    Thanks!

    P.S. Also I am from Watseka IL; Are ytou still in Peoria?[/quote:vs5bfi4b]

    I’m a little bit east of Peoria now, in Washington, IL.

    Once the functionality is in ME to enable me to align comma (or other) delimited data I’ll update the macro to take advantage of the new functionality.

    –James

    #4749

    ReidSweatman
    Participant

    It’s possible now, if you want to write a little macro. You just make two passes through the selected block (or entire file, if you’d prefer). In the first pass, you maintain a minimum spacing (starting at zero, of course) for each column in the block/file, incrementing it by as much as necessary to fit as you encounter each new line. After that pass, you’ll have one number built up for each column (however you want to delimit them) that tells how far the start of the next column must be from the current column start to let everything line up correctly. On the second pass, you read the lines in one at a time, separate out the tokens (probably with a regex, although a simple advance-and-test method will also work fine), and pad them with a number of spaces you calculate from the length of the token and the stored minimum length for that line, then write the reformatted line back out over the original.

    If I had more time right now, I’d write it instead of explaining it, but the explanation should be enough to let someone here fill it in. If you don’t know in advance how many columns you’ll have, you can either allocate more temporary variables than you’re likely to ever need, or make it adaptive, storing the numbers in a packed string global (see the database functions in DBTools.s) or in a hidden window (the Paste.s code should be informative there).

    A couple of code snippets that might help, and are quite general: to loop over all the lines in a file, use this:
    [code:2oclmya0]for(TOF; ! At_EOF; Down) {

    }[/code:2oclmya0]
    and to loop over all the lines in a block, this one:
    [code:2oclmya0]if(_NoBlock != Block_Stat) {
    for(Goto_Line(Block_Line1); C_Line <= Block_Line2; Down) {

    }
    }[/code:2oclmya0]
    Another general snippet you may find useful if you do work with blocks:
    [code:2oclmya0]switch(Block_Stat) {
    case _noBlock:
    break;
    case _linBlock:
    break;
    case _colBlock:
    break;
    case _strBlock:
    break;
    case _strBlockNI:
    break;
    default:
    Make_Message("ERROR – unrecognized block type.");
    }[/code:2oclmya0]

    #4766

    samej71
    Participant

    It’s possible now, if you want to write a little macro. [/quote:30j2j929]

    Yes, that’s definitely a possibility. :) It’s not that much different than what I’m doing now, actually. I even toyed with an idea similar to it a few months back.

    The problem I’d run into with a scenario like this is I need to be able to edit the file and save it back out as well, and make sure nothing was unintentionally changed along the way.

    This presents a couple of issues with a spaced-solution; as you add or remove text from a line all text from that point forward (to the end of the line) will no longer be properly aligned. A user would be responsible for adding or deleting spaces to adjust for their edit. Using real tabs the other columns stay put (except for when you increase the size of a field past the largest prior field size).

    The other problem I envision would be taking an “exapanded” file and converting it back into it’s “compressed” or tokenized form- you’d have to know somehow which series of spaces constitute a change in fields versus occuring in the data. I imagine you could trust the user kept the data aligned properly and then chop the lines up based on the calculated positions when you expanded the file, and then strip the trailing spaces off of each field (leading spaces would probably need to be kept as intentional space). This would also assume the user didn’t take it upon themselves to shift field positions or add/remove fields.

    For people who mostly view files those issues are moot. But I routinely edit delimited data used by programs so at least for me it’s imperitive that the data is identical when saved as it was when loaded (except for the intended changes, of course). That’s why having the ability to specify some other not-normally-occuring character as a ‘tab’ would be great. I could replace all non-quoted occurances of the delimited (tab, comma) with a extremely rare or even somehow impossible delimiter we’ll call X. I then set the tab character in the ruler to X and voila, the data lines up properly regardless if its commas, tabs, or something else. Then I simply replace all instances of X back to the original delimiter on a save. I think that would provide a very minimal chance of data corruption, assuming of course the delimiter used to line up the tabs doesn’t actually occur anywhere in the data. :)

    But I say all this not to diminish your idea; I think it’s a good one.

    Unfortunately I don’t have the time to exert to create that variant of the align-tab macro right now either. (and I still have a few reservations about data integrity when editing the file :) ).

    Talk with you soon,

    –James

    #5071

    CharlesG
    Participant

    Hi,

    What is I am 100% sure that no field data has any commas but that there is just one comma between fields, if the commas were all replaced by “@t” and then the macro is run; Would it still work ok or would it need to be modified further?

    the other thing is that what happens if the file extension setup is set to expand tabs to spaces or any other similar config data?

    And also if the struct is 5000 then wht is ther “(iteration++ > 9000)” test in there as 9000 and not 5000?

    thanks!

    James and others,

    I need a routine to do the same as yours but for comma-delimited fields. Can this be done just by changing the 1st param in the search_fwd() call?
    [/quote:2mzxk6ln]

    Sorry, but not at this time. If you change the parameter to a comma it will indeed mark the proper locations in the tab bar, but it will have no effect since there are no tabs present in the file to make use of the tab settings my macro sets up. It also doesn’t take into account commas that might legimately appear in a column of data. For example “Last, First” would not be treated as a single field, it’d be split into two columns.

    What you’re wanting to do (and others have asked too) should be possible (if I’m not mistaken) in version 10 of ME, possibly a subversion release of 9.10. Right now some functionality a macro like this needs to align columns on commas is not present in ME but is slated to be added in one of the upcoming releases.

    Thanks!

    P.S. Also I am from Watseka IL; Are ytou still in Peoria?[/quote:2mzxk6ln]

    I’m a little bit east of Peoria now, in Washington, IL.

    Once the functionality is in ME to enable me to align comma (or other) delimited data I’ll update the macro to take advantage of the new functionality.

    –James[/quote:2mzxk6ln]

    #5089

    ReidSweatman
    Participant

    All the options you mentioned are possible using the scheme I mentioned. To space all delimited columns to some fixed offset from the previous column, you just need one variable per column, to keep track of the offset required to guarantee adjacent columns don’t overlap; then add in the fixed offset. That was the scheme I originally proposed, where I thought you wanted the columns spread out and aligned vertically.

    If you just want one column aligned, and the others left as-is (where possible; obviously, it may not be, depending on the amount of shift), you use the same basic algorithm, but only do the trick in the original version for the current column. For following columns, you simply store the original column locations (or even simpler, cut the following text to a string variable first, adjust the one column, and paste the string back in at it’s original location (unless it’s been overrun by the column you just moved, in which case you can either simply overwrite–I’d guess not–or adjust that one column–and all the other trailing columns, if you want).

    The point is, however you want the column alignment to work, you can write a macro to do it that way, and most of the ways won’t even be all that different. So go ahead and buy the most recent version, because you can get what you want. ;)

    #7010

    CharlesG
    Participant

    Since it doesn’t handle comma delimited data – couldn’t I just write a macro that does a global Search for the commas and replace them with tabs, then call your macro?

    James and others,

    I need a routine to do the same as yours but for comma-delimited fields. Can this be done just by changing the 1st param in the search_fwd() call?
    [/quote:35wf4wn0]

    Sorry, but not at this time. If you change the parameter to a comma it will indeed mark the proper locations in the tab bar, but it will have no effect since there are no tabs present in the file to make use of the tab settings my macro sets up. It also doesn’t take into account commas that might legimately appear in a column of data. For example "Last, First" would not be treated as a single field, it’d be split into two columns.

    What you’re wanting to do (and others have asked too) should be possible (if I’m not mistaken) in version 10 of ME, possibly a subversion release of 9.10. Right now some functionality a macro like this needs to align columns on commas is not present in ME but is slated to be added in one of the upcoming releases.

    Thanks!

    P.S. Also I am from Watseka IL; Are ytou still in Peoria?[/quote:35wf4wn0]

    I’m a little bit east of Peoria now, in Washington, IL.

    Once the functionality is in ME to enable me to align comma (or other) delimited data I’ll update the macro to take advantage of the new functionality.

    –James[/quote:35wf4wn0]

    #7223

    samej71
    Participant

    Since it doesn’t handle comma delimited data – couldn’t I just write a macro that does a global Search for the commas and replace them with tabs, then call your macro? [/quote:1sbthars]

    I’m sorry, I’ve just gotten back to checking on things and I just now saw your message.

    Yes, if you do not have tabs within your data, you could do a global search and replace commas with tabs and then run my macro. After you finish editing the file and immediately before you save the changes you perform another global search and replace to replace all tabs with commas.

    –James

    #7642

    CharlesG
    Participant

    Hi. I did a manual S&R but still it doesn’t work. Seems the following snipet is never true:

    while ( search_fwd("@t", 0) )

    I have come up with a pre- align_tabs macro – or actually the shell. Just not sure why the WHILE above is never found to be true.

    void yahoo_preload(
    // do pre-convertion here
    rm("s_and_r"); // convert commas to tabs

    rm("align_tabs");

    // do post convertion here
    rm("s_and_r"); // convert tabs to commas
    )

    Any ideas?

    #7643

    deleyd
    Participant

    Could it be there are no TABS in your file?
    Check TOOLS -> CUSTOMIZE -> EDITING, see that Tab Expand is set to Tabs. though this only affects when you enter text, if set to Tabs Expand to Spaces, then any line you make a change to will have all tab characters replaced with the appropriate number of spaces.

    Check TEXT -> HEX MODE. Tabs will show as 09 hex followed by a number of 01 "virtual space" characters (or 0xFF if an earlier version of Multi-Edit).

    There may be problems beyond 255th char position on the line. Not sure if there are.

    #7644

    CharlesG
    Participant

    You said:
    Check TOOLS -> CUSTOMIZE -> EDITING, see that Tab Expand is set to Tabs. though this only affects when you enter text, if set to Tabs Expand to Spaces, then any line you make a change to will have all tab characters replaced with the appropriate number of spaces.

    Is there a way to save the current setting, change it if necessary, do a process, then change it back? I guess I just dont know the config item to work with.

    I am also relacing the commas with "@t" so that will work….

    #7645

    deleyd
    Participant

    [code:56v7n2q5] while ( search_fwd("@t", 0) ) [/code:56v7n2q5]
    uses Multi-Edit Classic RegEx, where "@t" means search for a tab character, and 0 means search starting here to the end of the file. Search_Fwd is depricated and it’s suggested to use Find_Text instead. A more up to date identical search statement would be:
    [code:56v7n2q5]find_text("\t",0,_RegExp);[/code:56v7n2q5]
    this one using Multi-Edit UNIX Regular Expressions.

    Forget that Tabs Expand to Spaces setting. It wouldn’t affect this. That would only affect if you were making changes to the file. Then each line you change will have all tabs replaced by spaces. If you did want to temporarily disable this:

    [code:56v7n2q5]int save = tab_expand;
    tab_expand = FALSE;
    ...
    tab_expand = save;[/code:56v7n2q5]

    #7899

    CharlesG
    Participant

    so then what would i need to do so I can get my needs met please?

    [code:fatmre9i] while ( search_fwd("@t", 0) ) [/code:fatmre9i]
    uses Multi-Edit Classic RegEx, where "@t" means search for a tab character, and 0 means search starting here to the end of the file. Search_Fwd is depricated and it’s suggested to use Find_Text instead. A more up to date identical search statement would be:
    [code:fatmre9i]find_text("\t",0,_RegExp);[/code:fatmre9i]
    this one using Multi-Edit UNIX Regular Expressions.

    Forget that Tabs Expand to Spaces setting. It wouldn’t affect this. That would only affect if you were making changes to the file. Then each line you change will have all tabs replaced by spaces. If you did want to temporarily disable this:

    [code:fatmre9i]int save = tab_expand;
    tab_expand = FALSE;
    ...
    tab_expand = save;[/code:fatmre9i][/quote:fatmre9i]

    #7909

    deleyd
    Participant

    Here’s a program that will align your comma separated values.
    Edit: Don’t use this version (attachment deleted). It had bugs. Use .

    (It assumes there are no tabs in your data.)

    Compile, then run align_commas

    (See for help on compiling CMAC code.)

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic.