CODEWITHSUNDEEP
pythoncsvnewline
Cyndi Bussey
Cyndi Bussey

Reputation: 1

Trying to replace newline characters in a csv with commas using python

I have a jotform form that I export into a csv file. I am using an unordered list in the form so the responders can rank school shops in order of interest. The CSV file looks like the following in notepad:

"Submission Date","First Name","Last Name",Email,"Student ID","Orderable List"
"Aug 2, 2022",Cyndi,Bussey,[email protected],12345,"1: Programming and web
2: Animal Science
3: Landscape
4: Health Tech
5: Culinary
6: Carpentry
7: Electrical
8: Advanced Manufacturing
9: Auto body
10: Auto Tech
11: Cosmo
12: Welding
13: one more
14: two more"

I need the row to look like: "Aug 2, 2022",Cyndi,Bussey,[email protected],12345,"1: Programming and web","2: Animal Science","3: Landscape","4: Health Tech","5: Culinary","6: Carpentry","7: Electrical","8: Advanced Manufacturing","9: Auto body","10: Auto Tech","11: Cosmo","12: Welding","13: one more","14: two more" (newline for end of row)

I need each shop to be in a separate column for each responder, not in separate rows, which is what is happening now. There are apparently newline characters at the end of every shop. I want to replace the newline with a comma. How would I do this in python?

Note: I am not real versed on lambda....

Thanks in advance.

Upvotes: 0

Views: 1366

Answers (1)

Kache
Kache

Reputation: 16677

Although it's a bit unclear what you're really asking for, I'll err on the side of answering the question in your title over any other interpretations of the rest of your question.

Use a proper csv reader and writer to handle formatting. It'll save you a lot of trouble.

import csv


def transform(row):
    date, fname, lname, email, sid, ordlist = row
    return date, fname, lname, email, sid, ordlist.replace('\n', ',')


with open('file.csv', newline='') as file, open('transformed.csv', 'w') as newfile:
    rows = csv.reader(file)
    csv.writer(newfile).writerows(map(transform, rows))

You'll get:

Submission Date,First Name,Last Name,Email,Student ID,Orderable List
"Aug 2, 2022",Cyndi,Bussey,[email protected],12345,"1: Programming and web,2: Animal Science,3: Landscape,4: Health Tech,5: Culinary,6: Carpentry,7: Electrical,8: Advanced Manufacturing,9: Auto body,10: Auto Tech,11: Cosmo,12: Welding,13: one more,14: two more"

Note this quick script also replaces newlines in the 6th header, but since there aren't any, it has no effect.

Upvotes: 1

Related Questions