Rapid data retrieval from Excel (or similar)

[Steven Ellet]

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.

 

[jim mcnamara]

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.

 

[Ibix]

In VBA you can read the contents of cells into an array by doing something like

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.

 

[Steven Ellet]

Maybe some sort of SQL?

 

[DavidSnider]

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.

 

[Michael27]

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.

 

[QuantumQuest]

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.

 

[Steven Ellet]

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.

 

[DavidSnider]

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.

 

[Steven Ellet]

I know

 

[Steven Ellet]

@QuantumQuest I am trying to make my own python based code to do the job

 

[QuantumQuest]

I am trying to make my own python based code to do the job

That's good. Which algorithm(s) do you use / implement?

 

[Steven Ellet]

I’m rather new to programming, so, I don’t know.

 

[rbelli1]

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

 

[Steven Ellet]

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)”

 

[DavidSnider]

I think this does what you're trying to do:

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);

 

[Steven Ellet]

@DavidSnider line 16
rows =
^
Invalid syntax

 

[Steven Ellet]

Solved that problem, but others have come up

 

[DavidSnider]

This is Python 3, python 2 won't work

 

[|Glitch|]

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!

 

[Filip Larsen]

As they say, there is a xkcd for everything ...