How to create csv file python

Writing to CSV in Python

Artturi Jalli

To write to CSV in Python, use Python’s csv module.

For example, let’s write a list of strings into a new CSV file:

As a result, you see a file called example.csv in the current folder.

This was the quick answer. But there is a lot more to cover when it comes to writing CSV files in Python. Let’s jump into the details and see some useful examples.

By the way, if you are interested in becoming a data scientist, check out these awesome Python Data Science courses!

Table of Contents

Writing to CSV in Python in Detail

CSV or comma-separated values is a text file. It consists of comma-separated data.

CSV is a useful data format for applications to transfer data. The CSV is a simple lightweight text file. This makes it a perfect fit for sending and receiving data in an efficient way.

For example, you can retrieve data from a database as a CSV. Or you can add a new entry to a database using CSV format.

Here is an example of what CSV data looks like:

However, in this guide, I’m using Mac’s built-in CSV viewer. Thus the CSV data looks more like this:

In the introduction, you saw a simple example of how to write a piece of data into a CSV file. Let’s now take a deeper look at how to write CSV files with Python.

The 4 Steps to Writing a CSV in Python

To write to a CSV file in Python:

Here is an example that illustrates this process:

This piece of code creates a file called test.csv into the current folder.

The open() function opens a new file if the file specified does not exist. If it does, then the existing file is opened.

The Shorthand Approach

To make writing to CSV a bit shorter, use the with statement to open the file. This way you don’t need to worry about closing the file yourself. The with takes care of that part automatically.

This creates a new CSV file called example.csv in the current folder and writes the list of strings to it.

How to Write Non-ASCII Characters to a CSV in Python

By default, you cannot write non-ASCII characters to a CSV file.

To support writing non-ASCII values to a CSV file, specify the character encoding in the open() call as the third argument.

The rest of the process follows the steps you learned earlier.

How to Create a Header for the CSV File

So far you have created CSV files that lack the structure.

In Python, it is possible to write a header for any CSV file using the same writerow() function you use to write any data to the CSV.

Example. Let’s create an example CSV file that consists of student data.

To structure the data nicely, create a header for the students and insert it at the beginning of the CSV file. After this, you can follow the same steps from earlier to write the data into a CSV file.

Here is the code:

This creates students.csv file into the folder you are currently working in. The new file looks like this:

How to Write Multiple Rows into a CSV File in Python

In Python, you can use the CSV writer’s writerows() function to write multiple rows into a CSV file on the same go.

Example. Let’s say you want to write more than one line of data into your CSV file. For instance, you may have a list of students instead of only having one of them.

To write multiple lines of data into a CSV, use the writerows() method.

Here is an example:

This results in a new CSV file that looks like this:

How to Write a Dictionary to a CSV File in Python

To write a dictionary into a CSV file in Python, use the DictWriter object by following these three steps:

Example. Let’s write a dictionary of student data into a CSV.

Now the result is the same students.csv file as in the earlier example:

Conclusion

CSV or comma-separated values is a commonly used file format. It consists of values that are usually separated by commas.

To write into a CSV in Python, you need to use the csv module with these steps:

Here is a practical example.

Thanks for reading. I hope you find it useful.

How to create, read, update and search through Excel files using Python

This article will show in detail how to work with Excel files and how to modify specific data with Python.

First we will learn how to work with CSV files by reading, writing and updating them. Then we will take a look how to read files, filter them by sheets, search for rows/columns, and update cells of xlsx files.

Let’s start with the simplest spreadsheet format: CSV.

Part 1 — The CSV file

A CSV file is a comma-separated values file, where plain text data is displayed in a tabular format. They can be used with any spreadsheet program, such as Microsoft Office Excel, Google Spreadsheets, or LibreOffice Calc.

CSV files are not like other spreadsheet files though, because they don’t allow you to save cells, columns, rows or formulas. Their limitation is that they also allow only one sheet per file. My plan for this first part of the article is to show you how to create CSV files using Python 3 and the standard library module CSV.

This tutorial will end with two GitHub repositories and a live web application that actually uses the code of the second part of this tutorial (yet updated and modified to be for a specific purpose).

Writing to CSV files

First, open a new Python file and import the Python CSV module.

CSV Module

The CSV module includes all the necessary methods built in. These include:

In this guide we are going to focus on the writer, DictWriter and DictReader methods. These allow you to edit, modify, and manipulate the data stored in a CSV file.

In the first step we need to define the name of the file and save it as a variable. We should do the same with the header and data information.

Now we need to create a function named writer that will take in three parameters: header, data and filename.

The next step is to modify the writer function so it creates a file that holds data from the header and data variables. This is done by writing the first row from the header variable and then writing four rows from the data variable (there are four rows because there are four tuples inside the list).

The official Python documentation describes how the csv.writer method works. I would strongly suggest that you to take a minute to read it.

And voilà! You created your first CSV file named imdb_top_4.csv. Open this file with your preferred spreadsheet application and you should see something like this:

Using LibreOffice Calc to see the result.

The result might be written like this if you choose to open the file in some other application:

Using SublimeText to see the result.

Updating the CSV files

To update this file you should create a new function named updater that will take just one parameter called filename.

This function first opens the file defined in the filename variable and then saves all the data it reads from the file inside of a variable named readData. The second step is to hard code the new value and place it instead of the old one in the readData[0][‘Rating’] position.

The last step in the function is to call the writer function by adding a new parameter update that will tell the function that you are doing an update.

csv.DictReader is explained more in the official Python documentation here.

For writer to work with a new parameter, you need to add a new parameter everywhere writer is defined. Go back to the place where you first called the writer function and add “write” as a new parameter:

Just below the writer function call the updater and pass the filename parameter into it:

Now you need to modify the writer function to take a new parameter named option:

From now on we expect to receive two different options for the writer function (write and update). Because of that we should add two if statements to support this new functionality. First part of the function under “if option == “write:” is already known to you. You just need to add the “elif option == “update”: section of the code and the else part just as they are written bellow:

Bravo! Your are done!

Now your code should look something like this:

You can also find the code here:

In the first part of this article we have seen how to work with CSV files. We have created and updated one such file.

Part 2 — The xlsx file

For several weekends I have worked on this project. I have started working on it because there was a need for this kind of solution in my company. My first idea was to build this solution directly in my company’s system, but then I wouldn’t have anything to write about, eh?

I build this solution using Python 3 and openpyxl library. The reason why I have chosen openpyxl is because it represents a complete solution for creating worksheets, loading, updating, renaming and deleting them. It also allows us to read or write to rows and columns, merge or un-merge cells or create Python excel charts etc.

Openpyxl terminology and basic info

Openpyxl in nicely documented and I would advise that you take a look here.

The first step is to open your Python environment and install openpyxl within your terminal:

Next, import openpyxl into your project and then to load a workbook into the theFile variable.

As you can see, this code prints all sheets by their names. It then selects the sheet that is named “customers 1” and saves it to a currentSheet variable. In the last line, the code prints the value that is located in the B4 position of the “customers 1” sheet.

This code works as it should but it is very hard coded. To make this more dynamic we will write code that will:

This is better than before, but it is still a hard coded solution and it still assumes the value you will be looking for is in the B4 cell, which is just silly 🙂

I expect your project will need to search inside all sheets in the Excel file for a specific value. To do this we will add one more for loop in the “ABCDEF” range and then simply print cell names and their values.

We did this by introducing the “for row in range..” loop. The range of the for loop is defined from the cell in row 1 to the sheet’s maximum number or rows. The second for loop searches within predefined column names “ABCDEF”. In the second loop we will display the full position of the cell (column name and row number) and a value.

However, in this article my task is to find a specific column that is named “telephone” and then go through all the rows of that column. To do that we need to modify the code like below.

This modified code goes through all cells of every sheet, and just like before the row range is dynamic and the column range is specific. The code loops through cells and looks for a cell that holds a text “telephone”. Once the code finds the specific cell it notifies the user in which cell the text is located. The code does this for every cell inside of all sheets that are in the Excel file.

The next step is to go through all rows of that specific column and print values.

This is done by adding a function named get_column_letter that finds a letter of a column. After the letter of the column is found we loop through all rows of that specific column. This is done with the get_all_values_by_cell_letter function which will print all values of those cells.

Wrapping up

Bra gjort! There are many thing you can do after this. My plan was to build an online app that will standardize all Swedish telephone numbers taken from a text box and offer users the possibility to simply copy the results from the same text box. The second step of my plan was to expand the functionality of the web app to support the upload of Excel files, processing of telephone numbers inside those files (standardizing them to a Swedish format) and offering the processed files back to users.

I have done both of those tasks and you can see them live in the Tools page of my Incodaq.com site:

Also the code from the second part of this article is available on GitHub:

csv — CSV File Reading and Writing¶

Source code: Lib/csv.py

The so-called CSV (Comma Separated Values) format is the most common import and export format for spreadsheets and databases. CSV format was used for many years prior to attempts to describe the format in a standardized way in RFC 4180. The lack of a well-defined standard means that subtle differences often exist in the data produced and consumed by different applications. These differences can make it annoying to process CSV files from multiple sources. Still, while the delimiters and quoting characters vary, the overall format is similar enough that it is possible to write a single module which can efficiently manipulate such data, hiding the details of reading and writing the data from the programmer.

The csv module implements classes to read and write tabular data in CSV format. It allows programmers to say, “write this data in the format preferred by Excel,” or “read data from this file which was generated by Excel,” without knowing the precise details of the CSV format used by Excel. Programmers can also describe the CSV formats understood by other applications or define their own special-purpose CSV formats.

The csv module’s reader and writer objects read and write sequences. Programmers can also read and write data in dictionary form using the DictReader and DictWriter classes.

The Python Enhancement Proposal which proposed this addition to Python.

Module Contents¶

The csv module defines the following functions:

Each row read from the csv file is returned as a list of strings. No automatic data type conversion is performed unless the QUOTE_NONNUMERIC format option is specified (in which case unquoted fields are transformed into floats).

A short usage example:

Return a writer object responsible for converting the user’s data into delimited strings on the given file-like object. csvfile can be any object with a write() method. If csvfile is a file object, it should be opened with newline=» 1. An optional dialect parameter can be given which is used to define a set of parameters specific to a particular CSV dialect. It may be an instance of a subclass of the Dialect class or one of the strings returned by the list_dialects() function. The other optional fmtparams keyword arguments can be given to override individual formatting parameters in the current dialect. For full details about dialects and formatting parameters, see the Dialects and Formatting Parameters section. To make it as easy as possible to interface with modules which implement the DB API, the value None is written as the empty string. While this isn’t a reversible transformation, it makes it easier to dump SQL NULL data values to CSV files without preprocessing the data returned from a cursor.fetch* call. All other non-string data are stringified with str() before being written.

A short usage example:

csv. unregister_dialect ( name ) В¶

Delete the dialect associated with name from the dialect registry. An Error is raised if name is not a registered dialect name.

csv. get_dialect ( name ) В¶

Return the names of all registered dialects.

csv. field_size_limit ( [ new_limit ] ) В¶

Returns the current maximum field size allowed by the parser. If new_limit is given, this becomes the new limit.

The csv module defines the following classes:

Create an object that operates like a regular reader but maps the information in each row to a dict whose keys are given by the optional fieldnames parameter.

If a row has more fields than fieldnames, the remaining data is put in a list and stored with the fieldname specified by restkey (which defaults to None ). If a non-blank row has fewer fields than fieldnames, the missing values are filled-in with the value of restval (which defaults to None ).

All other optional or keyword arguments are passed to the underlying reader instance.

A short usage example:

Note that unlike the DictReader class, the fieldnames parameter of the DictWriter class is not optional.

A short usage example:

The Dialect class is a container class whose attributes contain information for how to handle doublequotes, whitespace, delimiters, etc. Due to the lack of a strict CSV specification, different applications produce subtly different CSV data. Dialect instances define how reader and writer instances behave.

class csv. excel_tab В¶

class csv. unix_dialect В¶

New in version 3.2.

The Sniffer class is used to deduce the format of a CSV file.

The Sniffer class provides two methods:

Analyze the given sample and return a Dialect subclass reflecting the parameters found. If the optional delimiters parameter is given, it is interpreted as a string containing possible valid delimiter characters.

Analyze the sample text (presumed to be in CSV format) and return True if the first row appears to be a series of column headers. Inspecting each column, one of two key criteria will be considered to estimate if the sample contains a header:

the second through n-th rows contain numeric values

the second through n-th rows contain strings where at least one value’s length differs from that of the putative header of that column.

Twenty rows after the first row are sampled; if more than half of columns + rows meet the criteria, True is returned.

This method is a rough heuristic and may produce both false positives and negatives.

An example for Sniffer use:

The csv module defines the following constants:

Instructs writer objects to quote all fields.

Instructs writer objects to only quote those fields which contain special characters such as delimiter, quotechar or any of the characters in lineterminator.

Instructs writer objects to quote all non-numeric fields.

Instructs the reader to convert all non-quoted fields to type float.

Instructs writer objects to never quote fields. When the current delimiter occurs in output data it is preceded by the current escapechar character. If escapechar is not set, the writer will raise Error if any characters that require escaping are encountered.

Instructs reader to perform no special processing of quote characters.

The csv module defines the following exception:

exception csv. Error В¶

Raised by any of the functions when an error is detected.

Dialects and Formatting Parameters¶

To make it easier to specify the format of input and output records, specific formatting parameters are grouped together into dialects. A dialect is a subclass of the Dialect class having a set of specific methods and a single validate() method. When creating reader or writer objects, the programmer can specify a string or a subclass of the Dialect class as the dialect parameter. In addition to, or instead of, the dialect parameter, the programmer can also specify individual formatting parameters, which have the same names as the attributes defined below for the Dialect class.

Dialects support the following attributes:

On output, if doublequote is False and no escapechar is set, Error is raised if a quotechar is found in a field.

The reader is hard-coded to recognise either ‘\r’ or ‘\n’ as end-of-line, and ignores lineterminator. This behavior may change in the future.

Reader Objects¶

Reader objects ( DictReader instances and objects returned by the reader() function) have the following public methods:

Reader objects have the following public attributes:

A read-only description of the dialect in use by the parser.

The number of lines read from the source iterator. This is not the same as the number of records returned, as records can span multiple lines.

DictReader objects have the following public attribute:

If not passed as a parameter when creating the object, this attribute is initialized upon first access or when the first record is read from the file.

Writer Objects¶

Writer objects ( DictWriter instances and objects returned by the writer() function) have the following public methods. A row must be an iterable of strings or numbers for Writer objects and a dictionary mapping fieldnames to strings or numbers (by passing them through str() first) for DictWriter objects. Note that complex numbers are written out surrounded by parens. This may cause some problems for other programs which read CSV files (assuming they support complex numbers at all).

csvwriter. writerow ( row ) В¶

Changed in version 3.5: Added support of arbitrary iterables.

Write all elements in rows (an iterable of row objects as described above) to the writer’s file object, formatted according to the current dialect.

Writer objects have the following public attribute:

A read-only description of the dialect in use by the writer.

DictWriter objects have the following public method:

Write a row with the field names (as specified in the constructor) to the writer’s file object, formatted according to the current dialect. Return the return value of the csvwriter.writerow() call used internally.

New in version 3.2.

Changed in version 3.8: writeheader() now also returns the value returned by the csvwriter.writerow() method it uses internally.

Examples¶

The simplest example of reading a CSV file:

Reading a file with an alternate format:

The corresponding simplest possible writing example is:

Since open() is used to open a CSV file for reading, the file will by default be decoded into unicode using the system default encoding (see locale.getpreferredencoding() ). To decode a file using a different encoding, use the encoding argument of open:

The same applies to writing in something other than the system default encoding: specify the encoding argument when opening the output file.

Registering a new dialect:

A slightly more advanced use of the reader — catching and reporting errors:

And while the module doesn’t directly support parsing strings, it can easily be done:

How to create, read, update and search through Excel files using Python

This article will show in detail how to work with Excel files and how to modify specific data with Python.

First we will learn how to work with CSV files by reading, writing and updating them. Then we will take a look how to read files, filter them by sheets, search for rows/columns, and update cells of xlsx files.

Let’s start with the simplest spreadsheet format: CSV.

Part 1 — The CSV file

A CSV file is a comma-separated values file, where plain text data is displayed in a tabular format. They can be used with any spreadsheet program, such as Microsoft Office Excel, Google Spreadsheets, or LibreOffice Calc.

CSV files are not like other spreadsheet files though, because they don’t allow you to save cells, columns, rows or formulas. Their limitation is that they also allow only one sheet per file. My plan for this first part of the article is to show you how to create CSV files using Python 3 and the standard library module CSV.

This tutorial will end with two GitHub repositories and a live web application that actually uses the code of the second part of this tutorial (yet updated and modified to be for a specific purpose).

Writing to CSV files

First, open a new Python file and import the Python CSV module.

CSV Module

The CSV module includes all the necessary methods built in. These include:

In this guide we are going to focus on the writer, DictWriter and DictReader methods. These allow you to edit, modify, and manipulate the data stored in a CSV file.

In the first step we need to define the name of the file and save it as a variable. We should do the same with the header and data information.

Now we need to create a function named writer that will take in three parameters: header, data and filename.

The next step is to modify the writer function so it creates a file that holds data from the header and data variables. This is done by writing the first row from the header variable and then writing four rows from the data variable (there are four rows because there are four tuples inside the list).

The official Python documentation describes how the csv.writer method works. I would strongly suggest that you to take a minute to read it.

And voilà! You created your first CSV file named imdb_top_4.csv. Open this file with your preferred spreadsheet application and you should see something like this:

The result might be written like this if you choose to open the file in some other application:

Updating the CSV files

To update this file you should create a new function named updater that will take just one parameter called filename.

This function first opens the file defined in the filename variable and then saves all the data it reads from the file inside of a variable named readData. The second step is to hard code the new value and place it instead of the old one in the readData[0][‘Rating’] position.

The last step in the function is to call the writer function by adding a new parameter update that will tell the function that you are doing an update.

csv.DictReader is explained more in the official Python documentation here.

For writer to work with a new parameter, you need to add a new parameter everywhere writer is defined. Go back to the place where you first called the writer function and add “write” as a new parameter:

Just below the writer function call the updater and pass the filename parameter into it:

Now you need to modify the writer function to take a new parameter named option:

From now on we expect to receive two different options for the writer function ( write and update). Because of that we should add two if statements to support this new functionality. First part of the function under “if option == “write:” is already known to you. You just need to add the “ elif option == “update”: section of the code and the else part just as they are written bellow:

Bravo! Your are done!

Now your code should look something like this:

You can also find the code here:

In the first part of this article we have seen how to work with CSV files. We have created and updated one such file.

Part 2 — The xlsx file

For several weekends I have worked on this project. I have started working on it because there was a need for this kind of solution in my company. My first idea was to build this solution directly in my company’s system, but then I wouldn’t have anything to write about, eh?

I build this solution using Python 3 and openpyxl library. The reason why I have chosen openpyxl is because it represents a complete solution for creating worksheets, loading, updating, renaming and deleting them. It also allows us to read or write to rows and columns, merge or un-merge cells or create Python excel charts etc.

Openpyxl terminology and basic info

Openpyxl in nicely documented and I would advise that you take a look here.

The first step is to open your Python environment and install openpyxl within your terminal:

Next, import openpyxl into your project and then to load a workbook into the theFile variable.

This code works as it should but it is very hard coded. To make this more dynamic we will write code that will:

This is better than before, but it is still a hard coded solution and it still assumes the value you will be looking for is in the B4 cell, which is just silly 🙂

I expect your project will need to search inside all sheets in the Excel file for a specific value. To do this we will add one more for loop in the “ABCDEF” range and then simply print cell names and their values.

We did this by introducing the “ for row in range..” loop. The range of the for loop is defined from the cell in row 1 to the sheet’s maximum number or rows. The second for loop searches within predefined column names “ ABCDEF”. In the second loop we will display the full position of the cell (column name and row number) and a value.

However, in this article my task is to find a specific column that is named “telephone” and then go through all the rows of that column. To do that we need to modify the code like below.

This modified code goes through all cells of every sheet, and just like before the row range is dynamic and the column range is specific. The code loops through cells and looks for a cell that holds a text “telephone”. Once the code finds the specific cell it notifies the user in which cell the text is located. The code does this for every cell inside of all sheets that are in the Excel file.

The next step is to go through all rows of that specific column and print values.

This is done by adding a function named get_column_letter that finds a letter of a column. After the letter of the column is found we loop through all rows of that specific column. This is done with the get_all_values_by_cell_letter function which will print all values of those cells.

Wrapping up

Bra gjort! There are many thing you can do after this. My plan was to build an online app that will standardize all Swedish telephone numbers taken from a text box and offer users the possibility to simply copy the results from the same text box. The second step of my plan was to expand the functionality of the web app to support the upload of Excel files, processing of telephone numbers inside those files (standardizing them to a Swedish format) and offering the processed files back to users.

I have done both of those tasks and you can see them live in the Tools page of my Incodaq.com site:

Also the code from the second part of this article is available on GitHub:

Python Write CSV File

Summary: in this tutorial, you’ll learn how to write data into a CSV file using the built-in csv module.

Steps for writing a CSV file

To write data into a CSV file, you follow these steps:

The following code illustrates the above steps:

It’ll be shorter if you use the with statement so that you don’t need to call the close() method to explicitly close the file:

If you’re dealing with non-ASCII characters, you need to specify the character encoding in the open() function.

The following illustrates how to write UTF-8 characters to a CSV file:

Writing to CSV files example

The following example shows how to write data to the CSV file:

To remove the blank line, you pass the keyword argument newline=» to the open() function as follows:

Writing multiple rows to CSV files

To write multiple rows to a CSV file at once, you use the writerows() method of the CSV writer object.

The following uses the writerows() method to write multiple rows into the countries.csv file:

Writing to CSV files using the DictWriter class

If each row of the CSV file is a dictionary, you can use the DictWriter class of the csv module to write the dictionary to the CSV file.

The example illustrates how to use the DictWriter class to write data to a CSV file: