Replacing String in a file using Python

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 –

Python Code to replace string in a File

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. 🙂

Replacing String in a file using Python

One thought on “Replacing String in a file using Python

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s