Understand what the CSV format is and learn how to import and export these files – WAU

The CSV format is a type of text file fundamental for transferring information between different applications, such as a CRM platform and Microsoft Excel. To do this kind of information exchange successfully, you need to know how to import and export these files. Have questions about how to do this? Then keep reading this post!

If you work with any data management, be it the most complex or the most basic (like using spreadsheets on a daily basis), you’ve probably heard of CSV format.

Generally speaking, CSV is a format used to store data and can be imported and exported in programs such as Microsoft Excel, Google Sheets, Apple Numbers, OpenOffice Calc and other applications.

Even though the CSV is well known, it is common to have doubts about how to import or export files in this way.

In addition, we often do not know if it is the ideal format for what we need, and what the differences are for other types of files.

So if you want to learn a little more about how to use CSV correctly, continue reading this content!

What is CSV?

Before entering the walkthrough of how to import and export CSV files, it is important to understand what this acronym consists of.

By definition, CSV is a file format that means “çom-separated-values ​​”(values ​​separated by commas).

This means that the data fields indicated in this format are usually separated or comma-delimited.

To understand it in a more practical way, let’s assume that you have a spreadsheet that contains the following data:

CSV1

This data could be read in a CSV file separated by commas and a line spacing, as in the following example:

João, 2018, Belo Horizonte

Maria, 2019, Rio de Janeiro

Now that you understand the basic concept of CSV, you may be wondering: when, after all, choose to use this format? Keep reading and find out in the next topic!

When to use the CSV format?

The great advantage of a CSV file is the fact that it makes it possible to import and export files from a language that multiple applications can read.

That said, the most common use situation for CSV is when you want to export information from one tool and transfer it to another (example: from your CRM software to an Excel spreadsheet, or vice versa).

Often, when you are going to import data on a system, you will notice that right away he asks for a file in CSV format. Therefore, already have that in mind!

How to create a CSV file in Excel

Have you analyzed what you need and saw that CSV is the ideal format for this? So now it’s time to create your CSV file!

The easiest and most common creation of files in this format is done through spreadsheet software, such as Microsoft Excel, Google Sheets and Libreoffice.

Next, we’ll take a step by step how to do this in Microsoft Excel.

1. Open the file you want to save in .csv in Excel

image2

2. Click on file -> save as and select CSV.

image3

After saving, you can open it in a text editor or import it into an application.

How to create a CSV file in Google Sheets

“But I use Google Sheets in my work, not Excel. How do I create a CSV file? ” Do not worry!

Creating a CSV file in Google Sheets is practically the same process to create a CSV file in Microsoft Excel, which we explained above. Check out the step by step below.

1. Open the file you want to save to .csv in Google Sheets

image4

2. Click on file -> download as -> select CSV.

image5

After saving, you can open it in a text editor or import it into an application.

With this guidance, you will be able to export a CSV file. If you now need import a CSV file, we’ll cover these points in the following topics!

How to open a CSV file in a text editor

To open a CSV file on a computer, simply double-click the file’s icon.

It may be opened in text software in a spreadsheet, depending on what your computer has as a default setting.

As a text file, CSV can be easily opened in any text editor, such as Editpad, Notepad, Mac Text Editor, among others.

See how to do this, and how this data is shown, in the next step by step.

1. Open a new file in your text editor

CSV 3

2. The file will be opened separating each value with commas:

CSV 6

If you use Notepad, it is important to mention that this tool sometimes has difficulties in read very large CSV files. In that case, you can use Notepad ++ or another text editor.

You may be interested in these other contents!
Picture formats: you probably don’t know any of this list!
How to compress PDF: step by step and free tools
How to use Excel: step by step to use Excel with excellence
Excel VLOOKUP function: what it is and how to use it in your spreadsheets

How to open a CSV file in a spreadsheet

You learned how to open a CSV file in a text editor, but we know that this is not always going to be the way you want to view data.

So if you want to import a CSV file into a spreadsheet, you can also do this with a few clicks!

See how to do this in the image below.

CSV 7

How to edit a CSV file in a text editor

Often, when opening a CSV file, you will need to make edits or additions to the data you export.

To do this in a spreadsheet, simply add other rows and columns with the values ​​you want to assign.

On the other hand, to add data to a CSV file through the text editor, it is important that you have some parameters and rules in mind!

Below we have listed for you the main rules for creating a valid CSV file.

Create each record on separate lines

This separation is delimited by a line break, as in the following example:

João, 2018, Belo Horizonte

Maria, 2019, Rio de Janeiro

As you can see in the example above, the data for João are on one line, while the data for Maria are on another.

When organizing your data in this way, the moment you import your csv file into a spreadsheet or other software, each data group will be organized in a row:

image 6

Create a header to indicate the fields

This header is optional and would be the first line of your file.

The purpose of the header is indicate the corresponding fields to the values ​​you specify, so it must contain the same number of fields as the data number. For example:

Name, Year, City

João, 2018, Belo Horizonte

Maria, 2019, Rio de Janeiro

In the example above, João is a name, 2018 is a year and Belo Horizonte is a city.

To make this clear in our CSV file, and for an import that data is correct and below the fields that concern it, it is essential to use a header.

Still in the example above, because we put “name”, “year” and “city”, in a spreadsheet the data would appear like this:

CSV 8

Use commas correctly

Commas must indicate the separation of each field (field) and each value, but it is important to mention that the last record in a row must not contain a comma. Example:

João, 2018, Belo Horizonte

Maria, 2019, Rio de Janeiro

Note that we don’t insert any commas after Belo Horizonte or Rio de Janeiro.

Another point to consider in order to have the information you need processed correctly is when own data have commas.

In these cases, to differentiate from the comma that separates data, it is important to use quotation marks (“). Here is an example:

CSV 9

Note that in the third column there is a comma in the data, which talks about other cities that João or Maria have lived or will live.

To keep the data organized in this way, we will use the quotes. It would look something like this:

Name, Year, City

João, 2018, ”Belo Horizonte, but will change cities”

Maria, 2019, ”Rio de Janeiro, but already lived in São Paulo”

Now that you know how to import and export CSV files, you can work with more data flexibility between different applications!

As you can see in this content, the management of files in CSV can be done easily from the moment we understand the parameters and rules for this type of format.

If you want more tips on how to use spreadsheets in the best possible way in your day a day, be sure to check out our complete list of shortcuts and formulas for Excel!