database - How to remove extra commas from data in Python

Question

I have a CSV file through which I am trying to load data into my SQL table containing 2 columns. I have 2 columns and the data is separated by commas, which identify the next field. The second column contains text and some commas in that text. Because of the extra commas I am not able to load data into my SQL table as it looks like it has extra columns. I have millions of rows of data. How can I remove these extra commas?

Data:

Number Address
"12345" , "123 abc street, Unit 345"
"67893" , "567 xyz lane"
"65432" , "789 unit, mno street"

I would like to remove the extra commas in the addresses in random rows.

Answer 1

If all your data will be in the same format, as Number Address "000" , "000 abc street, Unit 000", you can split the list, remove the comma, and put the list back together, making it a string again. For example using the data you gave:

ori_addr = "Number Address "12345" , "123 abc street, Unit 345""
addr = ori_addr.split()
addr[6] = addr[6].replace(",", "")
together_addr = " ".join(addr)

together_addr is equal to "Number Address "12345" , "123 abc street Unit 345" note that there is no comma between "street" and "Unit."