In this lesson, you’ll see different ways to handle non-standard CSV files and how to deal with delimeters appearing in your data.
You learned how to:
quotecharescapechar00:00 Now you can open standard CSV files with Python, but what about the nonstandard cases? The default delimiter is a comma to separate items in a row, but what if you wanted to store an address that contained a comma?
00:12
Let’s take a look at an example. Here, I’ve got a CSV file that has a name, address, and date joined for a number of users.
00:20
If you notice, the address has a comma in here, so if you were to pass this into the csv.reader class, you would end up with this being split four times, because it would split at this comma also. Fortunately, we’ve got a couple of ways around this.
00:34 The first thing you can do is use a different delimiter in your CSV files. So looking at this example, I’ve just replaced all the commas with the pipe operator. This should work, so let’s go back to the CSV Python script that we’ve got and make the edits that are needed.
00:50
So now, it’s going to open 'different_delim.csv' as a CSV file, and then the main change here is just change this delimiter character from a comma to that guy. Alrighty, and just to make sure this makes sense, let’s say the "name" lives at—instead of "department", now this will be "address"—
01:20
I think this’ll just be "date joined". All right, let’s save this, open up a terminal. There we go! john smith lives at—this address.
01:32 It kept the comma in there, and everything lines up as it should. Awesome! Another thing you can do is wrap the data in quotes. So in this example, you can see that the delimiter is still the comma, but now this address is inside quotes.
01:47
You can then set up the CSV reader to use these quotes and ignore anything inside them. So, let’s go back, change the delimiter here back to a comma, and now add this quotechar, which I put an apostrophe—or, a quote—instead of the comma.
02:09
And set this quotechar equal to a single double quote, because this is wrapped in double quotes. All right, save this, try to run it. Ah, what did I do here?
02:24 And of course, the other thing you want to change is the name of the file. Since I’m using a different file here,
02:30 it didn’t see any of those quotes or the delimiter. All right. Let’s try to save that, rerun it. And look—there we go. Everything works. Finally, sometimes you’re not able to change the delimiter or wrap your data in different quote characters and you need to escape a delimiter character, so let’s take a look at this final example here where you can see the delimiter is still commas, there’s no quotes, and I just put a pipe operator in front of the comma.
02:57
You can then tell the CSV reader to ignore any delimiter character that appears after one of these escape characters. So, going back, get rid of this quotechar, and change it now to escapechar, and just put that pipe operator in there. This time, I’m going to actually change the filename to the right one.
03:22 Alrighty. Try to run this, and everything works! Awesome! So now you know three different ways to handle nonstandard CSV files and try to deal with your delimiters appearing in your data. Depending on how complex your data is, you can use all three of these techniques at the same time so that you’re able to store just about anything you need.
03:42 That’s enough for reading CSV files—now it’s time to start writing them with Python. I’ll see you in the next video.
shiv on July 3, 2020
i was wondering which IDE is best to code Python for Datascience coding?any experts here?
sweir12525 on Aug. 20, 2020
It would be very helpful if you let us beginners know what interpreter you are using. I don’t know how to bring files into Thorny or PYCharms in the same manner as you are doing in the demos. I have to make all my changes in my c:drive, save them to different file names and then change the coding. It is far more tedious than what you are using.
naomifos on March 30, 2021
What would you do in a case where your parameter is just a CSV reader object itself, and you want to read a CSV file? For the problem I have, it doesn’t want me to use the with...as statement, so I am kind of confused about this. It wants us to use the parameter to read this file.
acamposla on Dec. 16, 2021
I can’t find different_delim.csv in the “Supporting material”
Martin Breuss RP Team on Dec. 17, 2021
@acamposla thanks for flagging this, I’ve added to file to the Supporting Materials download. If you go ahead and download the ZIP file again, you’ll have different_delim.csv included as well.
wyper on April 28, 2023
I’m having a file with two characters “^;” as delimiter. How can we handle such a case with the csv module? Is it possible?
bart.jutte on July 3, 2026
quote_wrapping.csv is missing as well. Tried to get that working
bart.jutte on July 3, 2026
escape_char.csv is also missing. Difficult to follow along this way
Bartosz Zaczyński RP Team on July 5, 2026
@bart.jutte Thanks for flagging both of these, and you’re right on both counts. I checked the “Sample CSV Files” download and confirmed that quote_wrapping.csv and escape_char.csv were both missing from the ZIP. I’ve now added them, so if you download the Supporting Materials ZIP again, you’ll have all the sample files, including those two.
In case it’s handy, here are both files inline too. They use the same name and address data as the lesson, where the address includes a comma (Hoboken NJ, 07030), which is exactly the case these parameters are meant to handle.
quote_wrapping.csv, for the quotechar example (the comma stays a delimiter, but the address is wrapped in double quotes):
name,address,date joined
john smith,"1132 Anywhere Lane Hoboken NJ, 07030",Jan 4
erica meyers,"1234 Anywhere Lane Hoboken JN, 07030",March 2
import csv
with open("quote_wrapping.csv") as csv_file:
reader = csv.reader(csv_file, delimiter=",", quotechar='"')
for row in reader:
print(row)
escape_char.csv, for the escapechar example (no quotes, and the comma inside the address is escaped with a backslash):
name,address,date joined
john smith,1132 Anywhere Lane Hoboken NJ\, 07030,Jan 4
erica meyers,1234 Anywhere Lane Hoboken JN\, 07030,March 2
import csv
with open("escape_char.csv") as csv_file:
reader = csv.reader(csv_file, delimiter=",", escapechar="\\")
for row in reader:
print(row)
In both cases the address comes back as a single field even though it contains a comma, which is the whole point of the two parameters.
Thanks again for the careful reports :)
Become a Member to join the conversation.
Get a Python Cheat Sheet (PDF) and learn the basics of Python, like working with data types, dictionaries, lists, and Python functions:
rcruzalegui on March 10, 2020
How would I use the same script but load it to a dictionary to it can be used for later?