Python Tutorial: CSV Module – How to Read, Parse, and Write CSV Files


Hey there. How’s it going everybody in this video? We’re going to be looking at how to read parse and write CSV files now if you don’t know what CSV files are it stands for comma separated values basically CSV files allow us to put into a plain text file Some data and use some type of delimiter usually a comma to separate the different fields now I have a sample CSV file here that we can work with and if we look at this then we can see how these are Usually structured so this can kind of look like a mess, but it’s not really meant to be read directly This is just how the data is stored And then we can use our programs to parse out the information that we want, but we can see that the top Line here has our fields now our fields in this file are first name Last name and email so that tells us the information that we should expect to see on every line so if I go to the next line here then we can see that John is the first name and then a comma though is the last name and then a comma and then This long email here is the email so that’s why these are called comma separated values and what separates two values is called a delimiter So the comma is a common delimiter But you can use just about anything so sometimes you’ll see files with tab delimited values or dashes or things like that But they’re all called CSV files. So now let’s see what it’s like to read parts and write to CSV files So I have a file here called Parse CSV pie and within this file We’re just going to import CSV now you may have looked at that Data and wondered Why we’re just not using it the strings split method on each line of the file to parse out the data and you could do That but the CSV module just makes parsing these files so much easier so for example if someone puts a comma or something in their name for some reason then we wouldn’t want to split on that and also the CSV module will handle new lines and all those things so it just makes it a lot easier to parse out all the Information that we want without writing something complicated from scratch okay, so to read the CSV file We’re just going to open this file. Just like any other file So we’ll use a context manager here And we’ll say with open and the name of that file that I was just looking at is now CSV, and it’s in the same directory as the file that I’m currently writing and we want to read this file So we’ll put an r there as the second value and now what we want to call this so we’ll say CSV file So to read this file We can say CSV reader and that can be any variable name that you like, but that’s what I like And then we can say CSV and then use this reader method and then pass that CSV file in to that reader method now in the background that reader method is using something called a dialect that has some preset parameters for what it expects the format of our CSV file to be so by default it’s expecting values to be Separated by a comma and a few other things that we’ll look at in Just a bit, but since our CSV file is pretty simple We don’t need to pass any additional arguments right now So the CSV reader variable that we just created is going to be something that we need to iterate over So for example if we just print this out as is so print CSV reader and run that then we can see that right now. It’s just an object in memory, so instead We need to loop over all these lines in the reader and see what we get so we can say for line in CSV reader and then print out each line And we’ll run that ok so that looks better so each line that We’re printing out is a list of all the values so the first value in the list is the name as the first name The second value in the list is the last name and the email is the third value if I scroll all the way up to the top you can see that our first line is the field names so it tells us that the you know first value this first name last name is the Second value and Third value is email so for example if we’re going by the Index like this would be index 0 and then 1 and then the email would be index 2 if we only wanted to print out All of the indexes then on this line here, we could say let’s print out index 2 of each line And if we run that and we can see that now we get all of the emails printed out now if you don’t want this First line of the field names and only want the values then we can just skip that first line so if anyone has seen my video on generators and we can actually step over value an iterable by calling next and running next we’ll Return the next value if we want to capture that in a variable But if we just want to step over the value then we can come up here before our list. We can just say next CSV reader and that will loop over that first line And then when we iterate through this it should start at the second value Which is the first person in the list so now if we rerun this and scroll up to the top? Now we can see that that John doe is now the first value, okay? So now let’s see how we can write to a CSV file now We can do this with any list values, but since we’re already have a list of values here from our original CSV file Let’s go ahead and just use those so let’s say that we wanted to save these same values into a new CSV file But use dashes instead of commas for the delimiter now Dash is probably isn’t a great delimiter, but I just want to show you something that happens when we do this now first We’re going to want to write the field name headers into the new file So let’s take out this next statement where we’re skipping over those so Now I’ll come down here and now actually above our loop we’re going to want to open a new file for writing and So we’ll say with open and we’ll call this file new underscore names CSV We want to open this for writing so the second argument is aw, then we’ll say as and we’ll just call this a variable new file and to write to this file We’re going to use a CSV writer so we can say CSV writer and that can be any variable name But that makes sense to me and we’ll do CSV and then use this writer Method and we’re going to pass in new file to that writer method now if we left it like this then it would just write the same comma Separated file that we currently have now but if we want to use dashes as our delimiter Then we need to pass that in as an argument So it’s going to be the second argument to that writer method and we can say delimiter equals And we’ll just go use a dash now We want to write each line of our original CSV file into this new file, so let’s indent our for loop over here So that now we’re within the context manager of this new file and for each Line in this CSV reader. Which is our original file We want to write that to a new file so we can do that by saying CSV writer dot write Row and The row that we want to write is that line from the original reader? So real quick before I run this we are opening the original file to be read and then we’re creating this CSV reader variable and we are using the CSV reader method to read that original CSV file and then we’re opening a new file for writing called new Names CSV and Then we’re creating a CSV writer variable and we’re using this writer method of the CSV module to Open up a writer using that new file with a delimiter of a dash and then for each line in this original CSV data we are writing out to the new file each line of the original file So now if we run this then we don’t have any output here at the bottom But it should have created this new file called new named CSV and I’ll go ahead and open that up now We can see in this new file that. It’s using dashes instead of commas for the delimiter now This makes it pretty hard to read, but I wanted to show you what it did with two of our values here So in our first value the email actually contained a dash so we can see here that our CSV writer knew to put quotes around the email Since it can’t contain that delimiter And that’s so when the CSV is read back in that it would know that the email is One whole value and that it shouldn’t be split on the dash within the email itself and likewise here We can see that our second person here has a hyphenated last name of Smith Robinson so again the CSV writer knew to put quotes around the last name so that it can tell the difference between the delimiters and the values that just happen to contain dashes So now that we’ve seen how that works let’s actually change this delimiter for the new file to something that’s a bit more common, so aside from Commas tabs are very common Des limiters So let’s use tab instead and in python the tab can be represented with this backslash t and if we rerun that and then open up the new names file again Then we can see that now all the values are separated by tabs instead. That’s a lot more easier That’s a lot more easy to read now Just like we passed the delimiter into our writer if we wanted to read in that tab delimited file then you could pass the delimiter argument into the reader as well, and Real quick let me show you what that would look like if we tried to read a CSV file with the wrong delimiter So let me copy part of this here where we’re reading in this file And now I’m just going to comment out everything else for now now instead of reading the original file names CSV We’re instead going to read the new tab delimited file that we just created which is new underscore names CSV now Let’s pretend that we forgot to Specify the Tab delimiter and just try to read this as is so let’s print out the lines that we get from this reader So we’ll say four line in CSV reader and we will print out each line So we can see that each line only has one value And it didn’t split on the values on the tab because it was expecting commas So instead you have to explicitly pass in that we want the delimiter to be a tab so I’ll pass that into the reader method here and say delimiter equals a Backslash t for tab and then rerun that and now you can see that we get the correct parsing okay? so now I’m going to delete these lines here and Uncomment out what we had before Okay, so the way that we’ve been working with CSV files using the reader and writer is probably the more common way to work with CSV data since they’re the first things that come up in the python documentation But my preferred method is working with CSV data using the dictionary reader and the dictionary writer So let’s take a look at those and I’ll explain why I prefer them over the regular reader and writer okay, so first Let’s take a look at the dictionary reader So to use this we’re just going to replace the regular reader method here with a dict Reader and now let’s print out the lines that we give with this so I’ll say four line in CSV reader and we’ll just print out each line Okay, so at first glance this may look a little more complicated each of the values is now an ordered dictionary and if we scroll Up here to the top then we can see that that first line no longer contains the field names It starts off immediately with the first person So the reason is that the field names are now the keys of each of these values here now the reason I like this is because it makes it a lot easier to parse out the information that we want so for example remember when we Use the regular reader if we wanted to print out the email Address then we printed out the second index of our line well for anyone reading your code It isn’t obvious what that second index is so they’d have to go into the CSV file to find that information out But now that we have those Fields as our dictionary keys then we can get the email here by saying I just want the Email of that line So we just access that key so now if we rerun that we can see that now we have all of the email information Okay, and now let’s look at how to use the dictionary writer So I’m going to remove this loop and then uncomment out the rest of this information here Now with the dictionary reader. We really didn’t need to change anything, but with the dictionary writer We actually have to provide the field names of our file, so one line above our writer here I’m just going to create a list of the field names And now instead of using this writer method. We’re instead going to use dict writer Now one thing that we need to change there here is that after the file that we’re going to be writing to we need to? Pass in those field names, so I’ll say field names is equal to field names Okay, and now we’re ready to write the data so with the dictionary writer you have the option of whether or not you want to Write out those headers. Which are the field names in the first row so if we want those headers, which most of the time I do then we can say CSV writer Dot right header So that’s going to write out those field names as the first line and once the header is written out We can loop through the lines of the original file Just like we did before and say CSV writer dot right row and then pass in that line so all of that stays the same so if we run this and then look over here at our new names CSV file then we can see that that still worked and Like I said before the reason I like working with the dictionary reader and writer is because it’s more obvious what you’re doing So let’s say for example that in our new CSV file We actually only wanted the first and last names and wanted to leave off the email well with the regular reader and writer We’d be modifying the indexes of those list and like I mentioned before it’s not obvious by looking at an index What value it’s supposed to hold but with our dictionary writer? We can just remove the email from the field names up here and Before we write each line within our loop here We can just remove the email key and value and one way to do that is to just delete it so we can say delete the email of That line so now when it writes that row it’s only going to be writing the first name and the last name and the email no longer exists So now if we save that and run it and then I open up the new names dot CSV file here then you can see That now we just have a tab delimited file of first names and last names and that email is no longer there Now there are several ways that we could have written this row. We could have deleted the email key from Line Just like we did here or we could have created a new dictionary? With only the first name and last name keys and passed that into the right Road method So whichever way works for you in this case. I think it was easier Just to remove the email key okay, so I think that is going to do it for this video I hope that now you have a pretty good idea for how you can read parse and write CSV files But if anyone does have any questions about what we covered in this video then feel free to ask in the comment section below and I’ll do my best to answer those and if you enjoy these tutorials and Would like to support them and there are several ways you can do that the easiest ways to simply like the video and give it a thumbs up and also It’s a huge help to share these videos with anyone who you think would find them useful and if you have the means you can contribute through Patreon And there’s a link to that page in the description section below be sure to subscribe for future videos and thank you all for watching you

100 thoughts on “Python Tutorial: CSV Module – How to Read, Parse, and Write CSV Files”

  1. May I ask you a question? When I use csv.writerow I alway have a blank row after each row what have data, can you help me to fix it?

  2. Good vid. I hope you don't mind, and I know I am being a touch pedantic here, but for your beginners who are not used to american pronunciation, it would be more clear if pronounced "writer" with a t, rather than it sounding like "rider". I am English and it gave me a moments confusion, only rectified by reading the text, so I have to think the international people who may only be listening, could be confused.

  3. How can I save my csv file in a folder and make my python script open it from there?
    For me it works only if I have saved my csv in my Desktop

  4. It was fantastic , i just want to learn about online data downloading , i am able to download history data from net but when i go to write in csv file it omits some record and place dot dot dot (omit data) and in last it write some lines i am confuse how to handle that data pleas help

  5. Hello Corey,

    Thanks for the tutorials, do you have a tutorial using csv module with tkinter? I would like to edit csv files using a GUI interface in python.

    Regards,
    Estephanie

  6. Thanks for the helpful video! I'm new to Python… here in the video I don't understand why the output is not a dictionary format with DictWriter. Can anyone explain it to me please? Thanks

  7. A message to those making python files. Don't name your file csv.py. It will not work. I fell for that. Also you need to add the argument newline='' after 'w' or your new csv file will write to every other line. That is single quotes with no space in between.

  8. I really enjoyed this tutorial as well as all of your other tutorials that I have seen. I was wondering if you had shown a method for updating csv files?

  9. I really need help on this
    I have two csv files

    dataA.csv and dataB.csv
    1 2
    2 3
    3 5
    4 2

    how do I merge them into 1 csv

    merged.csv
    1,2
    2,3
    3,5
    4,2

  10. when I try this by using your names file by copying the data into excel and saving as a CSV comma delimited file, my new names file adds a new line in between each row of my output?

  11. I am using a tab-delimited text file, but for some reason, it is not changing when I specify other parameters. I want to modify only the 3rd column and there are no headers. Can you help? Your videos are wonderful!!! Thank you.

  12. How do I read the CSV file line by line rather than row downwards?
    say I have a CSV file where the description of each row is on the first line, how do I see it ?

  13. I have got few issues and i am not getting any solution online, so would need your help. Anyways, first issue is i have 7 fields in csv file but output comes out just for 6 though header shows 7 names. If i don't use extrasaction = 'ignore', then i am getting below error, here is the snippet:

    import csv

    with open('test.csv','r+') as rf:
    csv_reader = csv.DictReader(rf)
    with open('new_test.csv','w+') as wf:
    field_names = ['Count', 'Time', 'Thread_ID', 'Process_ID', 'Session_ID', 'Module', 'Message']
    csv_writer = csv.DictWriter(wf, fieldnames = field_names, extrasaction = 'ignore', delimiter = 't')
    csv_writer.writeheader()
    for line in csv_reader:
    csv_writer.writerow(line)

    OUTPUT –> No value for Count key
    Count Time Thread_ID Process_ID Session_ID Module Message

    11:06:02:444 3896 1456 230 CDF_ENTRY DllMain: enter

    11:06:02:444 3896 1456 340 CDF_ENTRY DllMain: exit

    11:06:02:444 3896 1456 750 CDF_ENTRY Exiting

    ERROR
    Traceback (most recent call last):
    File "D:ProjectsPythonFindIndex.py", line 11, in <module>
    csv_writer.writerow(line)
    File "C:UsersmehakvAppDataLocalProgramsPythonPython37-32libcsv.py", line 155, in writerow
    return self.writer.writerow(self._dict_to_list(rowdict))
    File "C:UsersmehakvAppDataLocalProgramsPythonPython37-32libcsv.py", line 151, in _dict_to_list
    + ", ".join([repr(x) for x in wrong_fields]))
    ValueError: dict contains fields not in fieldnames: 'Count'

  14. Hi i've exactly copied your code but when i let python read the "written new" csv file as you explain at around 10:00 into the video it prints "empty" lines in between the entrys. Has anybody got an idea whats wrong there? The file seems identical to my original file. Using python3, atom, win7
    ['jane', ' doe', ' [email protected]']
    []
    ['john', ' mayer', '[email protected]']
    []

  15. if you don't use the newline parameter, writing the data to a new file will result in every other line being skipped. to alleviate this, use the newline parameter

    newline=''

    that is newline with two single quotes

    with open('new_names.csv','w', newline='') as new_file:

  16. Hi Corey. Great video. In the video you mention a way to parse the csv columns by creating a new dictionary rather than the del method. I have a read file with about 100 columns but only need to write 10 of them could you show the alternate solution?

  17. how i can store the values entered in the html form into the current csv file using above mentioned method.????

  18. The for loop is reading CHARACTERS instead of lines, so I see just one character when I print it. I guess this changed in 2019?

  19. Hello, I loved your video. I was wondering for instance if I have a to remove all the fields other than the first three in a file and store the first two as the key pair and the third as its value.. how do I do it?

  20. Corey, the only tricky thing about csv is when you have to deal with special characters within data fields. I don’t think you demonstrated how to handle quotes within strings, or other escape characters such as newlines.
    Also, it is really bad in an instructive piece of code to use variable names that too closely mimic language keywords. Fieldnames=fieldnames, for example. Or csv_reader = csv.reader(…).

  21. Excellent video as always. Question though? I placed each command in a function ex:

    def main():

    def readCsv():
    <Opens the csv and prints output to stdout as expected>

    def writeCsv():
    <does not write to a file>

    main()

    in Laymans terms, could I not place both of these in separate functions? I want to leave them in functions in case I import them later. Thanks again for the great content!

  22. Sir Archelot like Duke, please teach me pybibd 11 extend c++ and use python script with easy cmake. That will renew my birth

  23. Hello, very amzing vidéo 📼. I think its so easy to manage csv file now. Tank you alot. I have an value error : I/Ô opération on closed file when i run this code, can anyone help me plz

  24. can you share this things for importing CSV files in mysql from a directory by using python while using load infile command ?

  25. sir my question is that when we are entering some data in the registration form then that data. how to update in CSV file using python

  26. Really its a nice video. Can you please make a video in which you'll display the data of that csv file in django application? I'll wait for that.

  27. I wish you would have shown the alternative to deleting the email key instead of just explaining it. I don't understand what you meant by "we could have created a new dictionary with only the first name and last name keys and passed that into the write row method."

  28. hi Corey, thanks for your vedios, I've learnt a lot. However, when I practiced the codes to write csv files, there were one more empty line after each line. Seems that there are "return"s after each line. How to avoid this issue? Thanks!

  29. I have a question: if I have numbers in my CSV file and I need to take an average of 1st five numbers. Then what should we need to do? And then take that average result of five number and subtract each next number from the average value we have taken before? a bit confused can you help.

  30. Hi, why can't i do this if there is an integer in the csv file?
    i got this error:
    Error: iterator should return strings, not int (did you open the file in text mode?)
    please help
    thank you

  31. My fieldnames has multiple fieldnames that I dont want to include. I get a value error when I dont include them in fieldnames. Do you know how to change this?

  32. how can i made a code to read the lest line/colun of a csv file? a need make a program to read de lest line of a csv file and then use this values in the code of my python code

  33. Is it possible to include any special things in the excel spreadsheet after you have crawled a website??? Lets say that you have scraped a website, and everytime the scraper find a youtube video it turns the cell into green. If there is no video in the cell it turns the cell in the spreadsheet red. Is that possible?? Thanks

  34. I have a CSV file I want to modify. It is for a bill of materials each line has a part # and description PartA,description . Some lines have the same description but different part numbers "PartA,PartB,PartC",description. I want to read in these lines and if there are multiple parts give them individual lines. How do I do analysis on the information I read in ??? Any help appreciated

Leave a Reply

Your email address will not be published. Required fields are marked *