asked on

rooting out commas in csv files python

Hi: Don't you just hate banks?
We have changed bank and I download all the data for analysis. Our new bank offers only csv format. No problem...EXCEPT that for direct debits and standing orders the text field has a comma inserted! It looks to me that the comma is inserted just prior to the word MANDATE.
Dopes anybody know how to write something in python that deletes (or replaces with a semi colon) a comma that appears just before the word MANDATE in a csv file?
Here is a snippet of the text field:
"DIRECT DEBIT PAYMENT TO XXXX PENSION REF 09999999991, MANDATE NO 9999"
See that stupid comma in the middle?
The text before the comma changes every time.

Thanks!

Richard Teasdale

ASKER

An adjunct to this problem; I found that the file opens in excel quite happily. THe difference is that text fields for standing orders and direct debits are in speech marks.
So a normal transaction looks like this:
09/08/2017,TRANSFER VIA FASTER PAYMENT TO EXPERTS EXCHANGE REFERENCE BACS,466.22,GBP,

direct debit looks like this:
08/08/2017,"DIRECT DEBIT PAYMENT TO EXPERTS EXCHANGE REF 01 9999999952, MANDATE NO 0036",6116,GBP,

What does excel do to ignore the comma if in speech marks, and how can I replicate this in python?

Thanks!

pepr

This is not a bank problem. It is a general problem that the CSV format solves by quoting the value. The comma inside the quotes should not be considered a delimiter. Try the Python standard csv module. Stay tuned, I will post a solution after a while.

What version of Python do you use? (I mean 2 or 3.)

Richard Teasdale

ASKER

Thank you very much! I use geany 1.23.1 and it does not refer to a python type but given the age would guess at 2?
and I apologise if being a bit dim here...I guess a simple replace ', MANDATE' with ';MANDATE' would suffice? still would be nice to see what you come up with!
Thanks again

ASKER CERTIFIED SOLUTION

pepr

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Richard Teasdale

ASKER

Thank you very much!
error message 'module' attribute has no attribute 'reader'

pepr

Try the following an show what it prints:

import sys
print sys.version

Open in new window

Richard Teasdale

ASKER

2.7.5. I am going to upgrade Geany....

Richard Teasdale

ASKER

Thanks for your help! I am taking your model and will try to fix it. I have to be away for a week and do not want to leave you hanging without recognition. If you can see my problem, please post and I will pick up later.
Thanks again

pepr

The 2.7.5 is a good version of Python 2. The question is whether Geany has all modules available that the standard Python distribution has. If it is "embedded Python", it need not to be the case.

Richard Teasdale

ASKER

Thanks pepr1

pepr

@ClaytonGlass: Was it possible to use the csv module with Geany?

Richard Teasdale

ASKER

yes; I had to import the module first. I also had to rewrite your answer as I needed all the arrays on one line but you certainly set me down the right path, thank you very much!