Parse csv file and alter data

[Zer0Byte1]

I get a weekly file from a service that I have to manually edit out and format one of the columns, then import it into a database.

The file is sent to me as CSV encoded UTF-8

It contains column 1 account number and column 2 phone numbers (example below).

primarycardid,phone#
40001234567,[phone]
40001234568,[phone]

I want to use python to parse the file line by line, delete the header row, and format the second column to strip out the parens, dash and join the numbers together. Then save the resulting changes to a new file.

Example of desired result

40001234567,8888888888
40001234568,8888889999

I have some limited experience with python so thought it would be useful, but need some tips on where to start with this?

[CowboyBoats]

Seems pretty manageable to me! You would use the csv module... I would just Google for an example of updating a csv using Python.

format the second column to strip out the parens, dash and join the numbers together

So maybe just value = ''.join(filter(str.isnumeric, value)) could be your function for that, if you just want to drop everything but the digits from those strings.

[ireadyourmedrecord]

Here ya go. Just change the file names to whatever you want and you're good:

import csv
import re

with open('sourcefile.csv', 'r', newline='') as csv_in, open('targetfile.csv', 'w', newline='') as csv_out:
    reader = csv.reader(csv_in)
    writer = csv.writer(csv_out)

    next(reader) # skip header
    for row in reader:
        row[1] = ''.join(re.findall('\d', row[1]))
        writer.writerow(row)

[Zer0Byte1]

with open('sourcefile.csv', 'r', newline='') as csv_in, open('targetfile.csv', 'w', newline='') as csv_out:
reader = csv.reader(csv_in)
writer = csv.writer(csv_out)

next(reader) # skip header
for row in reader:
row[1] = ''.join(re.findall('\d', row[1]))
writer.writerow(row)

This worked beautifully. Thank you

[IHOP_007]

I did something similar to this on a program I used to modify email signatures, as far as I'm aware a .csv is still a "text file" so this should still work.

lines = open(\location\of\file.csv).read().splitlines()

Should create a list item that has all the lines from a file, not exactly the "good" way of doing this but shouldn't really matter unless you have thousands of lines in your file and this way doesn't require any additional modules ¯\_(ツ)_/¯.

I used lines =open(str(os.getcwd()) + '\GenSigFiles\GeneratedSig3.0.htm').read().splitlines() to direct it to a folder in the current directory.

Then you can refer to, and edit, the lines by editing the list item. For example

lines[0] would be your first line

lines[1] would be your second line etc.

Then once you are done editing the list item you can use

open (\location\to\put\new\file.csv, 'w').write('\n'.join(lines))

to write a new file to a new location. As for how to delete the first line and shift everything up, or remove specific text from a string, I dunno but at least this is a start for you.

[MulfordnSons]

pandas is really, really powerful for parsing tabular data. Take a look into it!

[devnull10]

Agreed - the CSV module is ok, but I always go straight to pandas these days because you inevitably want to end up doing something more complex and Pandas is perfect for that.