How to decode an encoded excel file using python

Question

My java programmer converted an excel file to binary and sending the binary content to me.

He used sun.misc.BASE64Encoder and sun.misc.BASE64Decoder() for encoding.

I need to convert that binary data to a data frame using python.

the data looks like,

UEsDBBQABgAIAAAAIQBi7p1oXgEAAJAEAAATAAgCW0NvbnRlbnRfVHl........

I tried bas64 decoder but not helped.

my code:

import base64
with open('encoded_data.txt','rb') as d:
    data=d.read()
print(data)
`UEsDBBQABgAIAAAAIQBi7p1oXgEAAJAEAAATAAgCW0NvbnRlbnRfVHl........`
decrypted=base64.b64decode(data)
print(decrypt)
  'PK\x03\x04\x14\x00\x06\x00\x08\x00\x00\x00!\x00b\xee\x9dh^\x01\x00\x00\x90\x04\x00\x00\x13\x00\x08\x02[Content_Types].xml \xa2\x04\x02(\xa0\x00\x02\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00

Please help me to convert this binary data to a pandas dataframe.

Answer 1

You're almost there. Since the decrypted object is a bytes string, why not use BytesIO?

import io
import pandas as pd

toread = io.BytesIO()
toread.write(decrypted)  # pass your `decrypted` string as the argument here
toread.seek(0)  # reset the pointer

df = pd.read_excel(toread)  # now read to dataframe

Answering your question from your comment: How to convert a df to a binary encoded object?

Well, if you want to convert it back to an b64 encoded object with pandas converting it as an excel, then:

towrite = io.BytesIO()
df.to_excel(towrite)  # write to BytesIO buffer
towrite.seek(0)  # reset pointer
encoded = base64.b64encode(towrite.read())  # encoded object

To write the encoded object to a file (just to close the loop :P):

with open("file.txt", "wb") as f:
    f.write(encoded)