Rapid data retrieval from Excel (or similar)

  • Thread starter Thread starter Steven Ellet
  • Start date Start date
  • Tags Tags
    Data Excel
AI Thread Summary
The discussion centers around the inefficiency of using Excel for encoding and decoding messages, particularly for encryption purposes. Participants suggest that the homegrown process is cumbersome and recommend transitioning to more efficient methods. They advocate for using established encryption tools like PGP, which offer robust security without the complexities of manual coding. Python is highlighted as a suitable programming language for creating custom encryption algorithms, with suggestions to utilize existing libraries for ease of implementation. The conversation also touches on the challenges of developing secure encryption methods, emphasizing that creating a truly unbreakable code is difficult. Concerns about the future of encryption in the face of quantum computing advancements are also noted, stressing the importance of using proven encryption standards. Overall, the consensus is to move away from Excel and leverage more effective, specialized tools for encryption tasks.
Steven Ellet
Messages
85
Reaction score
3
I have a spreadsheet with quite a bit of information. This spreadsheet is designed to encode words and create a encrypted message. Unfortunately, the process of coding and decoding is long and tedious. I am looking for a way to make this process quick (depending upon message length) and easy.

Example, if I want to code “My dog is old.” I go to M1 Y1 D2 O2 G2 ... etc
As you can imagine, this is really annoying.
 
Technology news on Phys.org
There are lots of encryption apps out there. Most windows applications like your browser use encryption (Ex: https: == SSL encryption is used on a website with that string at the start of the URL).

You not need a clunky homegrown process, one that you think does not meet your needs.

Consider something outside of Excel.

PGP for windows is free, and very good. May I suggest that you lose the Excel process: put the .xls file on a thumb drive, then finally, put the drive in your underwear drawer. Then install something that really does encrypt well and does not require another program to run it. And many people use very successfully.

In other words, you or your friends created a dinosaur, that was probably lots fun to build. Good. But next to useless. Not so good.
 
  • Like
Likes StoneTemplePython
In VBA you can read the contents of cells into an array by doing something like
Code:
Dim pad As Variant
pad = Range("A1:Z100").Value ' Must put the Value member here

' Code letter D in third word
wordNumber = 3
letter = "D"
codedLetter = pad(wordNumber, Code(UCase(letter)) - Code("A")+1)
(Warning: I may have the array indices the wrong way round.) That will be faster than going to the cells, as long as you aren't trying to encrypt War and Peace.

You could consider storing the data in a text file (export it as a CSV file, for example) and read it into a language that runs faster than VBA - almost anything, I should think. I like python.

Honestly, the easiest way to do this is to download a copy of PGP and use that. There's a truism in cryptographic circles that anybody can invent a code that they themselves cannot break. Inventing one that anybody else cannot break is extremely difficult. What you are describing appears to be a half-way house between a true one-time pad and a simple substitution cypher. I'd be wary of trusting it with anything remotely important.
 
  • Like
Likes jim mcnamara
Maybe some sort of SQL?
 
Let me see if I understand correctly: each column in the excel table from A to Z has a a value and you have a row for each word in the message.

For each letter in the plaintext you access ROW[WORD_NUMBER], COLUMN[LETTER] to produce an encoding for the plaintext?

Unless you have some good reason for using excel that I am unaware of this would probably be better done in something like Python.
 
As the previous poster asked: "What are you trying to accomplish?"
If you need to locate a text you can use dictionaries in almost any language I know and can think of. A dictionary is a structure that allows you to find an entry based on a key and then retrieve the value. If the number of items gets to big for memory, the next step would be a database which does this for a living and can be much faster that most of the things you can code if you have the correct indices.
 
Steven Ellet said:
I have a spreadsheet with quite a bit of information. This spreadsheet is designed to encode words and create a encrypted message. Unfortunately, the process of coding and decoding is long and tedious. I am looking for a way to make this process quick (depending upon message length) and easy.

Example, if I want to code “My dog is old.” I go to M1 Y1 D2 O2 G2 ... etc
As you can imagine, this is really annoying.

As already noted, using Excel to do encryption / decryption is not the most efficient way. Depending on the goal(s) of using such an application it would be far better either to code one yourself - if you know some programming, or use a ready-to-go application. For the former case I'd recommend using a decent algorithm like XOR cipher - just an example, and code it using whichever language you know (I'd use C or C++ but Python does the job very well too). For the latter case I think that PGP that jim McNamara recommends is a very good way to go.
 
Excel merely provides the setup, that information could be copied to an external program for use.
@DavidSnider Almost, word 1 uses row 1, word 2 uses row 2...etc
As for “What are you trying to accomplish?” A code that is as unbreakable as possible, if possible.
 
Steven Ellet said:
Excel merely provides the setup, that information could be copied to an external program for use.
@DavidSnider Almost, word 1 uses row 1, word 2 uses row 2...etc
As for “What are you trying to accomplish?” A code that is as unbreakable as possible, if possible.
One time pads are only unbreakable if the key is as long as the entire message, is completely random and never reused. As you might imagine this limits their usefulness.
 
  • #10
I know
 
  • #11
@QuantumQuest I am trying to make my own python based code to do the job
 
  • #12
Steven Ellet said:
I am trying to make my own python based code to do the job

That's good. Which algorithm(s) do you use / implement?
 
  • #13
I’m rather new to programming, so, I don’t know.
 
  • #14
You won't be able to make an encryption algorithm even remotely close to the ones already out there. Encryption is a very highly specialized field that requires high level math and programming skills to understand.

Enter "python encryption" into your search engine of choice and pick a library that looks easy to use. You can make your application take data in any form you can imagine. Then run the resulting information through a ready made encryption library to protect it.

BoB
 
  • Like
Likes Ibix
  • #15
This is what I have so far (currently incomplete) based on Python programming language:
str = input()
print(len(str))
x=0
y="1"
while x<len(str):
print(str[x]+(y))
if str[x]==" ":
y="2"
x=x+1
print (x)

This program works ok but I want to change line 8 into y+=1
Unfortunately this causes an error and if I make it “y+=1” then it doesn’t error but doesn’t do what I want ether. What I am trying to do is make y change after every “(space)”
 
  • #16
I think this does what you're trying to do:

Code:
import string
import random

def generate_key():
    letters = list(string.ascii_letters)
    alpha_length = len(string.ascii_letters)
    for i in range(0,alpha_length):
        r = i
        r2 = random.randint(0,alpha_length-1)
        temp = letters[r]
        letters[r] = letters[r2]
        letters[r2] = temp
    return ''.join(letters)

def create_map(word_length):
    rows = []
    for i in range(0,word_length):
        key = generate_key()
        rows.append(key)
    return rows

def stevencode(plaintext,key):
    words = plaintext.split(' ')
    encrypted = ""
    for i in range(0,len(words)):
        word = words[i]
        for j in range(0,len(word)):
            c = word[j]
            if c in string.ascii_letters:
                idx = string.ascii_letters.index(c)
                encrypted += key[i][idx]
            else:
                encrypted += c

        encrypted += " "
       
    return encrypted[:-1]

def stevendecode(ciphertext,key):
    words = ciphertext.split(' ')
    decrypted = ""
    for i in range(0,len(words)):
        word = words[i]
        for j in range(0,len(word)):
            c = word[j]
            if c in string.ascii_letters:
                decrypted += string.ascii_letters[key[i].index(c)]
            else:
                decrypted += c

        decrypted += " "
       
    return decrypted[:-1]

plaintext = input("Enter string to encrypt: ")
word_count = len(plaintext.split(' '))
key = create_map(word_count)
crypt = stevencode(plaintext,key)
decrypt = stevendecode(crypt,key)
print("Plaintext:" + plaintext)
print("Encrypted:" + crypt);
print("Decrypted:" + decrypt);
 
  • #17
  • #18
Solved that problem, but others have come up
 
  • #19
This is Python 3, python 2 won't work
 
  • #20
Steven Ellet said:
Excel merely provides the setup, that information could be copied to an external program for use.
@DavidSnider Almost, word 1 uses row 1, word 2 uses row 2...etc
As for “What are you trying to accomplish?” A code that is as unbreakable as possible, if possible.
One thing you should always do before developing new software is to find out if there is software already on the market that will do what you want. In this case, there is. It is called Pretty Good Privacy (or more commonly referred to as PGP). Using their 4,096-bit encryption would take approximately 32 years for a Cray supercomputer to crack. So I think your e-mails would be safe, at least for now. When quantum computers become more than just prototypes then all bets are off. It would not take a 32-qubit quantum computer very long to break a 4,096-bit key. Everyone's encryption will become instantly obsolete with the advent of quantum computers. Fun times! :nb)
 
Last edited:
  • #21
As they say, there is a xkcd for everything ...
bad_code.png
 

Attachments

  • bad_code.png
    bad_code.png
    25 KB · Views: 425
Back
Top