How To Import A Plain Text File To Google Sheets

Posted by

Note: This post was originally written in Jan 6, 2017 and moved here on Sep 6, 2020.

Hi there, today I will share a small post with you on how I used python to solve (or automate) one of the problems in my life. So first, let’s talk about the problem –

My bank only allows me to download my bank statements in excel (.xls) and plaintext formats. But I use Google sheets and I have a Mac. The excel file which my bank provides doesn’t work well with Numbers app on my Mac and it also doesn’t work with Google sheets.

So I download my statements in plaintext and export them manually to Google sheets. Now again, the plaintext file isn’t ready for Google sheets. It looks like this (I have only included 3 rows to give you an idea) –

1    02/05/16    ATM/CASH -    400.00        61,611.83
2    03/05/16    eBanking -    9,626.95        51,984.88
3    07/05/16    PRCR -    367.00        51,617.88

The columns in the plaintext file are separated by multiple spaces. I want to convert this file into something like this –

1#02/05/16#ATM/CASH -#400.00#61,611.83
2#03/05/16#eBanking -#9,626.95#51,984.88
3#07/05/16#PRCR -#367.00#51,617.88

So I can copy paste that into Google sheets and use Data > Split text to columns with a custom separator symbol ‘#‘.

The final result would look something like this –

And the python code to convert first piece of text into the second one is given below –

with open('testfile.txt', 'U') as f:
    while '  ' in newText:
        newText=newText.replace('  ', '#')
    while '##' in newText:
        newText=newText.replace('##', '#')
    while '\n\n' in newText:
        newText=newText.replace('\n\n', '\n')
with open('testfile.txt', "w") as f:

In this code, we open the testfile.txt and read it in U mode (universal newlines mode). Then replaces all instances of two spaces with one #. Then we replace all instances of two hashes with one hash. And finally we replace all instances of two line breaks with single line break.

A Simple Example

If all of the above is complicated, try this simple example to replace all instances of “Apples” in a file with “Oranges”.

with open(FileName) as f:'Apples', 'Oranges')
with open(FileName, "w") as f:

I used this, this, this and this awesome StackOverflow thread to learn this stuff.

Leave a Reply

Your email address will not be published. Required fields are marked *