Convert from base 16 to base 62 and possible limits of VBA

[LPLA]

Hello,


I am currently trying to find a way to convert from base 16 to base 62 (and the other way around) [Yes base 62 is correct! NOT base 64] but from what I could gather this seems to be a bit tricky because of the restrictions in VBA concerning very large numbers.

An example would look like this:
0551a7be6aa14c6d93d014b02c4ec40d (base 16)
A2HFM1ZYZLfqTOc9Iv1cT (base 62)

As you can see the numbers are quite large.
I tried converting to base 10 first and then base 16 or base 62 but that did not work.

Does anyone ever encounter a similar problem and is willing to give me some hints on how to solve this?


Thank you,
LPLA

[6StringJazzer]

62? Are you sure you do not want base 64? I have never heard of any practical use for base 62.

[Special-K]

62? Are you sure you do not want base 64? I have never heard of any practical use for base 62.

Nor me........!

Googling threw up "shortening URLs"

[LPLA]

Base 62 is correct. I thought about explicitly saying this in my original post.
I will edit it to avoid further confusion.

[curiouscat408]

My first google attempts are of the form "wiki base62".
It immediately finds https://en.wikipedia.org/wiki/Base62.
And the google search "convert to base62" turns up some algorithms that might (or might not) be translatable into VBA.
Good luck with that!!

[6StringJazzer]

Base 62 is used for URL shortening, which I was not aware of. And it makes perfect sense since it uses exactly the set of characters that can be used in a (case-sensitive) URL.

I suspect that due to the length this will need an algorithm that deals with chunks of character strings rather than trying to treat the string as a single number, which would be quite large. A base 62 number will exceed the Long data type at about 11 digits. I do not have experience with those types of calculations but I'm sure it's been done in other languages for web development. I'll see if I can find something that can be converted to VBA.

[curiouscat408]

Yes, base62 __can_be__ used for URL encoding. But that is not its only use. "All men are Greek, but not all Greeks are men".

Using a variety of online sources, I have concluded that base16 0551a7be6aa14c6d93d014b02c4ec40d is converted to base62 A2HFM1ZYZLfqTOc9Iv1cT by interpreting the base16 encoding as a representation of an integer, namely base10 7070118273220996960834318043337114637.

The following describes how I reached that conclusion, and I provide a VBA __prototype__ of an implementation of the conversion, as "proof of concept".

PS.... That is not the only way to interpret the problem. I would have interpreted it differently, treating the data as a bit stream. But then, the base62 encoding would have been very different. OTOH, perhaps LPLA's example is incorrect, based on his misinterpretation and online converters that he stumbled across, as I did.

@LPLA, if there is any doubt, please provide some context for the conversion. That is, what are you doing that requires the base62 conversion of that data? For example, encoding blockchains?

-----

Caveat: I reference several websites. But I cannot vouch for their correctness.

In fact, note that https://en.wikipedia.org/wiki/Base62 is ambiguous.

The conversion table indicates that 0 through 61 correspond to the characters ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789.

But in fact(?), 0 through 61 correspond to 0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz, as implied at the beginning of the article.

The latter is consistent with the online base62 converter cited below, as well as with some online sources and the example in the OP. Moreover, it is consistent with what I would expect.

-----

When I enter base16 0551a7be6aa14c6d93d014b02c4ec40d, with and without the leading zero, into the base62 converter at https://math.tools/calculator/base/16-62, the result is indeed base62 A2HFM1ZYZLfqTOc9Iv1cT.

And when I enter the base16 value into the hex-to-decimal converter at https://www.scopulus.co.uk/tools/hexconverter.htm, the result is base10 7070118273220996960834318043337114637.

I cannot demonstrate that base10 7070118273220996960834318043337114637 can be converted to base16 551a7be6aa14c6d93d014b02c4ec40d and base62 A2HFM1ZYZLfqTOc9Iv1cT, because the base10 value is too large even for VBA type Decimal.

However, I can demonstrate the conversions by using the highlighted subset, base10 73220996960834318043337114637.

Enter that base10 value at https://www.scopulus.co.uk/tools/hexconverter.htm, and we get base16 EC96FD36D315FCF56C4EC40D.

Enter that base16 value at https://math.tools/calculator/base/16-62, and we get base62 1XE4kWaxLeHgOKoQP.

The following VBA procedure demonstrates the calculations.

Please Login or Register  to view this content.

The next step is to implement a multiprecision version.

"The exercise is left for the student".

PS.... There are third-party add-ons for doing basic arithmetic with very large numbers. I cannot vouch for any of them.

[curiouscat408]

The following VBA procedure demonstrates the calculations.

FYI, I edited the previous posting and replaced the VBA procedure to include base16-to-base10 conversion, as requested.

[LPLA]

Sorry for the late answer curiouscat408,

first of all thank you for your reply!

Basically I need the conversion to compare and verify IDs.
The provider used to send them in the same base but for some reason decided to switch.
Now we are left with IDs in base 16 and base 62 which we of course can't compare.
We do have a conversion tool that someone from a company we're working together with made.
Unfortunately that person retired (or switched jobs, I don't know) some time ago and we are not able to get the source code (not that it would matter much).

The point is that I am trying to get rid of any third party conversion tools or sites and have them automatically converted in Excel.
You can imagine that it is pretty tedious having to use a program or site just to convert an ID so you can compare it with another ID.
Especially if you are trying to compare or validate a bunch of IDs.

Unfortunately I don't have much time right now to thoroughly read your post but I will do so asap and answer (or edit this post).

[curiouscat408]

I don't have much time right now to thoroughly read your post but I will do so asap and answer (or edit this post).

Please post new responses. Do not edit the last response. It is difficult to discover that a posting has been edited.

( "Do as I say, not as I do". )

In any case, a "thorough reading" might not be productive.

What I posted is a "proof of concept" to demonstrate the __form__ of an algorithm to convert long integers, as I thought the encodings represent.

But the algorithm is not useful if you are not willing to install (or we cannot implement) functions that perform basic arithmetic on very large numbers. (The difficult one is division.)


------

I need the conversion to compare and verify IDs. The provider used to send them in the same base but for some reason decided to switch. Now we are left with IDs in base 16 and base 62 which we of course can't compare.

First, please be specific in your descriptions. Which is it:

1. The provider used to send IDs in base62, and now they are sending IDs in base16?

2. Or the provider used to send IDs in base16, and now they are sending IDs in base62?

3. And what form of IDs do you store: whatever encoding the provider used to send; or something else (what)?

4. Do __you__ ever originate encoded IDs from raw data (or very large integers)?

5. Or are the encoded IDs (base16, base62) always generated originally by the provider, and you merely store them for the purpose of comparisons later?

More to the point: do you really need to convert base16 to base62 or base62 to base16?

For comparison purposes, it is easier to convert base16 to base10 and base62 to base10, then compare the base10 IDs. ( No need for multiprecision division. )

Would that meet your needs?

-----

I am trying to get rid of any third party conversion tools or sites and have them automatically converted in Excel.

I understand that you do not want to use separate applications.

But what about __third_party__ add-ins that become __part_of__ your Excel file?

I could understand if that is not allowed, as well.

Some contractual agreements do not permit the use of software that cannot be "audited" and that is not well-accepted "mainstream" products, like Excel.

(And some contracts even require documentation of algorithms for some Excel functionality!)

[LPLA]

Finally got some time to reply to you. Sorry for taking so long...
I'll try to answer to the most important points of your post.

1. The provider used to send IDs in base62, and now they are sending IDs in base16?

2. Or the provider used to send IDs in base16, and now they are sending IDs in base62?

Yes
Okay so let me try to explain as best as I can because this change happened before I started working at the company.
We basically have two different providers which supply sets of data that we need to compare and look for differences or any kind of inconsistencies.
Every set of data has multiple attributes. One of them is the ID which is unique for every set of data.
Provider A uses base 16 for the ID while provider B uses base 62. (I don't know if A used to use 62 or if B used to use 16 but honestly that doesn't matter as they are unwilling to revert back anyway ).
So as I mentioned every set of data has a unique ID (among other attributes) and this ID is the only attribute that we can use to find a set from A in the database of B (and the other way around).
This means that we must be able to convert from base 16 to base 62 and also from base 62 to base 16.

3. And what form of IDs do you store: whatever encoding the provider used to send; or something else (what)?

We store everything as provided. So base 16 for provider A and base 62 for provider B (at least the IDs but as I mentioned above the IDs are the only thing we can use for comparison).

4. Do __you__ ever originate encoded IDs from raw data (or very large integers)?

No.

5. Or are the encoded IDs (base16, base62) always generated originally by the provider, and you merely store them for the purpose of comparisons later?

Yes.

More to the point: do you really need to convert base16 to base62 or base62 to base16?

For comparison purposes, it is easier to convert base16 to base10 and base62 to base10, then compare the base10 IDs. ( No need for multiprecision division. )

Would that meet your needs?

Unfortunately not. I need base 16 and base 62 to find the set of data in the providers database.
You are of course right that I would be able to compare them but unfortunately that's not the main point.
I need to verify if set X from provider A with an ID in base 16 is in the database of provider B who uses IDs in base 62.

I understand that you do not want to use separate applications.

But what about __third_party__ add-ins that become __part_of__ your Excel file?

I could understand if that is not allowed, as well.

Some contractual agreements do not permit the use of software that cannot be "audited" and that is not well-accepted "mainstream" products, like Excel.

(And some contracts even require documentation of algorithms for some Excel functionality!)

I have to check but my guess is that we are not allowed to do that.

[curiouscat408]

So as I mentioned every set of data has a unique ID (among other attributes) and this ID is the only attribute that we can use to find a set from A in the database of B (and the other way around).
This means that we must be able to convert from base 16 to base 62 and also from base 62 to base 16.

Not necessarily. I suppose it depends on how you do the database search.

Instead of "if tobase62(base16_ID) = base62_ID then", write "if tobase10(base16_ID,16) = tobase10(base62_ID,62)", where "tobase10" is the VBA function below.

Similarly for statements of the form "if tobase16(base62_ID) = base16_ID then".

If that is not how you do the comparison, please explain how you search the database and how that precludes base10 comparisons.

Alternatively, would it be feasible to store base10 IDs in the databases instead of the IDs in the form that the providers send?

Then, instead of "search for tobase62(base16_ID)", do "search for tobase10(base16_ID,16)" and "search for tobase10(base62_ID,62)".

-----

You are of course right that I would be able to compare them but [....] I need to verify if set X from provider A with an ID in base 16 is in the database of provider B who uses IDs in base 62

How does that affect the comparison of the IDs per se?

Are you saying that in addition to matching the IDs, you also need to confirm that associated data matches?

Be that as it may, the two comparisons (IDs and data) are separate.

If the data is also base16 or base62 encoded, you could compare them by converting each set of data to base10. That is: "if tobase10(base16_data) = tobase10(base62_data) then".

-----

I am not trying to "stubbornly" sell base10 comparisons.

I am saying that __I__ have no other solution for you (in an Excel/VBA context).

If you truly believe that you cannot make it work for you, then I am done here. Sorry.

-----

The "tobase10" VBA function....

Please Login or Register  to view this content.

[LPLA]

Not necessarily. I suppose it depends on how you do the database search.

Instead of "if tobase62(base16_ID) = base62_ID then", write "if tobase10(base16_ID,16) = tobase10(base62_ID,62)", where "tobase10" is the VBA function below.

Similarly for statements of the form "if tobase16(base62_ID) = base16_ID then".

If that is not how you do the comparison, please explain how you search the database and how that precludes base10 comparisons.

Alternatively, would it be feasible to store base10 IDs in the databases instead of the IDs in the form that the providers send?

Then, instead of "search for tobase62(base16_ID)", do "search for tobase10(base16_ID,16)" and "search for tobase10(base62_ID,62)".

Unfortunately the providers won't change the way they store the data.
We have asked them but they just won't.

How does that affect the comparison of the IDs per se?

Are you saying that in addition to matching the IDs, you also need to confirm that associated data matches?

Be that as it may, the two comparisons (IDs and data) are separate.

If the data is also base16 or base62 encoded, you could compare them by converting each set of data to base10. That is: "if tobase10(base16_data) = tobase10(base62_data) then".

Well let me try to explain because my previous explanations might not have been as clear as I thought.
Assume you have data A and data B.

The ID of data A is in base 62. Let us call the provider Alice.
The ID of data B is in base 16. Let us call the provider Bob.

I could compare both IDs by converting them to base 10.
That is correct.
But all that does is show me if data A is in Bobs database by converting all IDs of the data in Bobs database to base 10 too (and the other way around works too of course).

Now let us assume that there is some sort of problem with one of the data from the data set of Alice.
Like one of the other attributes (besides the ID) needs closer inspection.
If I convert the ID of data A to base 10 I can't find the data with that converted ID in Bobs database, because I would need the ID in base 16 to find it there.

The reason for that is that we are using two sources to get the data.
Alice provides the data on alice-data.com and Bob by using a program called "BobsDataPuller".
We export the data into an Excel file and start comparing.
What could happen now is that there is data from Alice which is not in our export from Bobs data, because of a timestamp difference or something like that.
What we do now is get the ID from data A which is missing in our export of Bobs data, convert it to base 16 and search for it in "BobsDataPuller".
If we would convert both of them to base 10 we could see that something is missing but we couldn't use that ID to search for the data.
I hope that explanation makes more sense.
We only compare data sets within a specific time frame and we ourselves do not manipulate data in Alices or Bobs database at all.
All we do is export and compare.
If we find anything suspicious we have to take a closer look, but the data we export might not have all the information we need to solve the issue.
Hopefully that makes sense...

I am not trying to "stubbornly" sell base10 comparisons.

I am saying that __I__ have no other solution for you (in an Excel/VBA context).

If you truly believe that you cannot make it work for you, then I am done here. Sorry.

Dont't worry. I wish I could do everything in base 10 or in binary.
You have still helped me a lot!
Thank you very much!

[karedog]

You can use the famous GMP library (The GNU Multiple Precision Arithmetic Library) for this purpose ( https://gmplib.org/ ).

The limitation of how big the integer is only the available RAM on your PC :
"There is no practical limit to the precision except the ones implied by the available memory in the machine GMP runs on."

And as a bonus, there is a builtin method to pull the number in any base number from 2 to 62 ( https://gmplib.org/manual/Converting-Integers ) :
Function: char * mpz_get_str (char *str, int base, const mpz_t op)
Convert op to a string of digits in base base. The base argument may vary from 2 to 62 or from -2 to -36.

I have make an exe file using this library which you can use (included in the attachment), and an Excel UDF as wrapper to call this program.

And a sample sub (using sample numbers from your post #1) :

Please Login or Register  to view this content.