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: newText=f.read() 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: f.write(newText)
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: newText=f.read().replace('Apples', 'Oranges') with open(FileName, "w") as f: f.write(newText)