Rapid data retrieval from Excel (or similar)

  • Thread starter Thread starter Steven Ellet
  • Start date Start date
  • Tags Tags
    Data Excel
Click For Summary
SUMMARY

The discussion focuses on optimizing the process of encoding and decoding messages using Excel, which is deemed inefficient for encryption tasks. Participants recommend transitioning to dedicated encryption tools, specifically highlighting PGP (Pretty Good Privacy) for its robust security features. Python is suggested as a superior alternative for implementing custom encryption algorithms, with examples provided for basic encryption and decryption functions. The consensus is that while Excel can serve as a data repository, it is not suitable for serious encryption needs.

PREREQUISITES
  • Understanding of basic encryption concepts and algorithms.
  • Familiarity with Python programming language.
  • Knowledge of PGP (Pretty Good Privacy) encryption standards.
  • Basic understanding of data structures such as arrays and dictionaries.
NEXT STEPS
  • Learn how to implement encryption algorithms using Python libraries such as PyCryptodome.
  • Explore PGP encryption and its implementation for secure communications.
  • Research the differences between symmetric and asymmetric encryption methods.
  • Study data structures in Python for efficient data handling and retrieval.
USEFUL FOR

Individuals interested in secure data transmission, software developers looking to implement encryption, and anyone seeking to improve their understanding of cryptographic practices.

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   Reactions: 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   Reactions: 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   Reactions: 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: 448

Similar threads

  • · Replies 1 ·
Replies
1
Views
12K
  • · Replies 94 ·
4
Replies
94
Views
12K
  • · Replies 1 ·
Replies
1
Views
12K
Replies
17
Views
7K
  • · Replies 13 ·
Replies
13
Views
10K
  • · Replies 7 ·
Replies
7
Views
3K
  • · Replies 1 ·
Replies
1
Views
4K
  • · Replies 7 ·
Replies
7
Views
4K