http://qs321.pair.com?node_id=1199244

thanos1983 has asked for the wisdom of the Perl Monks concerning the following question:

Hello fellow Monks,

I am trying to translate int to ASCII (alphabetical) characters. I am having as an input int e.g. 1 - 26 everything works just fine.

My problem occurs in case that I am having an int as e.g 27, then if I translate this int + 64 (decimal base) to ASCII it comes as [ which of course is the correct translation for int 91.

I am trying to think of a way of splitting the int into segments with max int 26. I mean for example if int is 27 or greater somehow split the int into 26 and 1.

My progress so far is almost to zero, because I can not think any way of doing such a process, since the number can increment to "theoretically" max int. So my goal is to create a generic solution.

Sample of code to represent the problem / solution and how I encode / decode the int:

#!/usr/bin/perl
use strict;
use warnings;
use Data::Dumper;
use feature 'say';

my @characters = ("Z", "[");

my %hash;
for ( @characters ) {
    my @ascii_character_int = unpack("C*", $_);
    $hash{$_} = \@ascii_character_int;

    my $word = pack("C*", @ascii_character_int);
    say $word;
}
print Dumper \%hash;

__END__

$ perl test.pl
Z
[
$VAR1 = {
          'Z' => [
                   90
                 ],
          '[' => [
                   91
                 ]
        };

[download]

In my case I am using the chr function to translate the characters. For example I am using my $character = chr(64+$i). This works just fine as long as the int is bellow 26 after that I am getting the ASCII non alphabetical characters that I can not use.

To avoid confusion why I am trying to translate int to characters. I have a script that is reading and writing excel files and the columns e.g. (A-Z) are just fine for writing (where the problem occurs) but as soon as I need to write a column e.g. (AA) then I am having problems.

The error from my other script is:

Unknown cell reference [1

[download]

I debug the script and I found the problem:

Column Index: 27 Column Character: [ Text: Test Line at AA

[download]

I am not posting the code here as it is irrelevant and will only consume space, the problem that I am trying to resolve is to split int with max value 26 so I can translate them to ASCII alphabetical characters. In case that anyone wants to play around with the code here is the code Re: Merging 2 xlsx files.

Any ideas, are appreciated. Thanks in advance for the time and effort.

Seeking for Perl wisdom...on the process of learning...not there...yet!

Replies are listed 'Best First'.
Re: Split int into segments of max int 26
by LanX (Saint) on Sep 12, 2017 at 23:51 UTC
    I'm confused, this sounds like if you just need to calculate modulo 26 to be able to transform 27 to AA.

    Am I missing something?

    edit

    Something like this? 

    while ($n) {
    $i = $n % 26;
    unshift @vector, $i;
    $n -= $i;
    $n /= 26;
} 

print map { chr(64+$_) } @vector;

    [download]

    Untested!

    Cheers Rolf
    (addicted to the Perl Programming Language and ☆☆☆☆ :)
    Je suis Charlie!

[reply]
[d/l]

      Hello LanX,

      I was too tired last night to even think of the modulo. Thanks for the time and effort.

      Just for future reference Perl & modulus.

      BR / Thanos

      Seeking for Perl wisdom...on the process of learning...not there...yet!
[reply]
[d/l]
[select]
Re: Split int into segments of max int 26
by AnomalousMonk (Archbishop) on Sep 13, 2017 at 02:13 UTC

    I'm also a bit confused about what you want. I think one of the other replies may hold your solution, but have you forgotten the Perl "magic" string incrementer: 

    c:\@Work\Perl\monks>perl -wMstrict -le
"for my $s (qw(Y Z AA  ZY ZZ AAA  ZZY ZZZ AAAA)) {
   my $t = $s;
   printf qq{'$t' -> };
   $t++;
   print qq{'$t'};
   }
"
'Y' -> 'Z'
'Z' -> 'AA'
'AA' -> 'AB'
'ZY' -> 'ZZ'
'ZZ' -> 'AAA'
'AAA' -> 'AAB'
'ZZY' -> 'ZZZ'
'ZZZ' -> 'AAAA'
'AAAA' -> 'AAAB'

    [download]
    If you already have string 'ABC', you can always go to the next step.

    Give a man a fish:  <%-{-{-{-<

[reply]
[d/l]
[select]

      Hello AnomalousMonk,

      Thanks a lot for the clue, I had no idea. I was looking for something like that to be honest :), thanks for sharing.

      BR / Thanos

      Seeking for Perl wisdom...on the process of learning...not there...yet!
[reply]
[d/l]
[select]
Re: Split int into segments of max int 26 ( Spreadsheet::Read::cr2cell )
by beech (Parson) on Sep 13, 2017 at 00:39 UTC

    Hi,

    The spreadsheet modules have code to do this for you 

    use Data::Dump qw/ dd /;
use Spreadsheet::Read qw( cr2cell );
for my $pair (  [0,0],
                [1,1],
                [12,12],
                [25,25],
                [26,26],
                [66,66],
                [666,666],
                [6666,6666],
            ) {
    dd( cr2cell( @$pair ) , @$pair );
}
__END__
("", 0, 0)
("A1", 1, 1)
("L12", 12, 12)
("Y25", 25, 25)
("Z26", 26, 26)
("BN66", 66, 66)
("YP666", 666, 666)
("IVJ6666", 6666, 6666)

    [download]

    update:https://metacpan.org/pod/Spreadsheet::Read#cr2cell

[reply]
[d/l]

      Hello beech,

      I read quickly this function when I was reading the module and to be honest I did not even thought about going back and reading it again. I am so naive some times.

      Thanks a lot for pointing this out.

      BR / Thanos

      Seeking for Perl wisdom...on the process of learning...not there...yet!
[reply]
[d/l]
[select]
Re: Split int into segments of max int 26
by kcott (Archbishop) on Sep 13, 2017 at 04:43 UTC

    G'day thanos1983,

    Here's a recursive function that, I believe, achieves what you're after. 

    #!/usr/bin/env perl -l

use strict;
use warnings;

use constant BASE => ord('A') - 1;
use constant MAX  => ord('Z') - BASE;

my @tests = (
    0 .. 3, 24 .. 28, 
    26**2+24 .. 26**2+28,
    26**3+26**2+24 .. 26**3+26**2+28,
);

print "IN: $_ OUT: ", translate($_) for @tests;

sub translate {
    my ($in, $out) = @_;

    $out ||= [];

    return join '', @$out if $in == 0;

    my $mod = $in % MAX || MAX;

    unshift @$out, chr $mod + BASE;

    my $new_in = ($in - $mod) / MAX;

    translate($new_in, $out);
}

    [download]

    Output:

    IN: 0 OUT: 
IN: 1 OUT: A
IN: 2 OUT: B
IN: 3 OUT: C
IN: 24 OUT: X
IN: 25 OUT: Y
IN: 26 OUT: Z
IN: 27 OUT: AA
IN: 28 OUT: AB
IN: 700 OUT: ZX
IN: 701 OUT: ZY
IN: 702 OUT: ZZ
IN: 703 OUT: AAA
IN: 704 OUT: AAB
IN: 18276 OUT: ZZX
IN: 18277 OUT: ZZY
IN: 18278 OUT: ZZZ
IN: 18279 OUT: AAAA
IN: 18280 OUT: AAAB

    [download]
    "I am not posting the code here as it is irrelevant and will only consume space, ..."

    A cutdown version might have provided some context. Without that, I don't know exactly how you'd implement this solution. You'll probably want some validation of the input.

    — Ken

[reply]
[d/l]
[select]

      Hello kcott,

      This is exactly what I was trying to accomplish yesterday but for some reason I was so tired of thinking even.

      Thanks for sharing, great input.

      BR / Thanos

      Seeking for Perl wisdom...on the process of learning...not there...yet!
[reply]
[d/l]
[select]
Re: Split int into segments of max int 26
by choroba (Cardinal) on Sep 13, 2017 at 11:44 UTC
    Use Math::Base::Convert : 
    #! /usr/bin/perl
use warnings;
use strict;
use feature qw{ say };

use Math::Base::Convert;

my $converter = 'Math::Base::Convert'->new(10, ['A' .. 'Z']);
say $converter->cnv(1000);

    [download]

    The strings will be a bit different to what you proposed, as A stands for 0 (thus it's A, B, C, ..., Z, BA, BB, BC, etc.), but it's not clear whether it matters.

    ($q=q:Sq=~/;[c](.)(.)/;chr(-||-|5+lengthSq)`"S|oS2"`map{chr |+ord
}map{substrSq`S_+|`|}3E|-|`7**2-3:)=~y+S|`+$1,++print+eval$q,q,a,

    [download]
[reply]
[d/l]
[select]

      Hello choroba,

      That is a nice approach, I had not idea about the module.

      Thanks for sharing.

      BR / Thanos

      Seeking for Perl wisdom...on the process of learning...not there...yet!
[reply]
[d/l]
[select]
Re: Split int into segments of max int 26
by dasgar (Priest) on Sep 13, 2017 at 17:43 UTC

    Take a look at Number::Latin - and in particular, the int2LATIN function. I've used this module for this exact purpose, but in my case I was using Win32::OLE to directly control Excel to do the Excel file manipulation. I had wanted to keep the column stored as an integer to make it easier for iterating through columns, but needed the ASCII labels for the cell identification for the Excel.

[reply]
Re: Split int into segments of max int 26
by Anonymous Monk on Sep 13, 2017 at 06:57 UTC
[reply]

      Hello Anonymous Monk,

      Thanks a lot for the reference. I was so focused on my problem that I did not even think of doing a proper research to resolve my problem.

      Thanks again for sharing.

      BR / Thanos

      Seeking for Perl wisdom...on the process of learning...not there...yet!
[reply]
[d/l]
[select]
Re: Split int into segments of max int 26
by Anonymous Monk on Sep 13, 2017 at 01:34 UTC
    Pardon me for asking – but why do you think that you need to "add 64" to an integer in order to translate it to ASCII?
[reply]

      Hello Anonymous Monk,

      The reason is simple, read the ASCII Table and Description on the Decimal column you will see (Dec 65 = A) but when you read the int are coming as 1 so you need to add 64 to get the character. You can also read the chr function it contains the same example.

      Hope this helps, BR.

      Seeking for Perl wisdom...on the process of learning...not there...yet!
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom