Rapid data retrieval from Excel (or similar)

  • #1
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.
 

Answers and Replies

  • #2
jim mcnamara
Mentor
4,010
2,446
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
  • #3
Ibix
Science Advisor
Insights Author
6,822
5,665
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
  • #4
Maybe some sort of SQL?
 
  • #5
DavidSnider
Gold Member
487
131
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.
 
  • #6
24
3
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.
 
  • #7
QuantumQuest
Science Advisor
Insights Author
Gold Member
926
485
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.
 
  • #8
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.
 
  • #9
DavidSnider
Gold Member
487
131
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.
 
  • #13
I’m rather new to programming, so, I don’t know.
 
  • #14
rbelli1
Gold Member
963
364
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
DavidSnider
Gold Member
487
131
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);
 
  • #18
Solved that problem, but others have come up
 
  • #19
DavidSnider
Gold Member
487
131
This is Python 3, python 2 won't work
 
  • #20
429
117
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
Filip Larsen
Gold Member
1,274
206
As they say, there is a xkcd for everything ...
bad_code.png
 

Attachments

Related Threads on Rapid data retrieval from Excel (or similar)

Replies
7
Views
754
Replies
5
Views
10K
Replies
4
Views
2K
Replies
7
Views
1K
Replies
4
Views
1K
Replies
2
Views
2K
Replies
9
Views
922
Replies
8
Views
43K
Replies
7
Views
2K
Top