A CSV file refers to the ‘Comma-separated value’ file extension, that allows you to store tabular data for managing data sets or databases. It is a plain text file that can only contain numbers and text values that are separated or delimited by a comma.
CSV is a very popular and versatile file format which often used for exchanging data between different applications. Since they are easy to organize and manipulate, CSV files are widely used in consumer, business, financial, and scientific applications. Many programs offer options to import or export files in the CSV format.
In this article, we will discuss what is CSV file, how to create and edit them, how to open a CSV file, and how to import/export a CSV file.
What is a CSV file and What is its Structure?
A CSV file sometimes referred to as Character Separated Values or Comma Delimited File is any file that ends in ‘.csv’. It is a common data exchange format used by spreadsheet or database programs such as Microsoft Excel, Google Sheets, MySQL, etc. CSV makes it easy to transfer complex data between applications that can’t talk to one another, as long as both apps support CSV format.
CSV files can only contain letters, numbers, and characters (printable ASCII or Unicode characters), which are separated (or delimited) by comma characters. Since they are plain text files, CSV files are easy to import or export. With CSV files, you can easily export large and complex information from one program and then import the information in that CSV file into another program.
In CSV files, values are usually delimited by a comma but sometimes they can use other characters to separate values, including:
- Semicolon (;)
- Tab (\t)
- Space ( )
- Single or Double Quotation Marks (”) (“”)
- Pipe (|)
Structure of CSV file
A CSV file has a simple structure and it is actually spelled out by its name (Comma-separated values). CSV files use a comma to separate each specific data value (columns) on one line, and different rows appear on different lines. For example, when you convert a table of data into a CSV file, the first row/line contains column headings (delimited) of the table, then each line contains a row of the table.
It will be better understood with an example. Let’s say you have this spreadsheet containing a table of data:
And this is how the above data looks like in a CSV-formatted file:
First Name,Last Name,Gender,Country
Franklyn,Unknow,Male,France
Loreta,Curren,Female,France
Philip,Gent,Male,France
Shavon,Benito,Female,France
Shavonne,Pia,Female,France
CSV file could be more complicated than this with more fields or values on each line and could contain thousands of lines. CSV files will not support any fonts, design, or formatting, it only carries the plain text. This simplicity is what makes the CSV the file of choice for exporting and importing data into other programs.
Creating a CSV file
There are two ways to create a CSV file – one, you can create a CSV file by exporting or saving a file in CSV format using applications; two, create a CSV file using a text editor.
Create a CSV file using Text Editor
You don’t need a dedicated spreadsheet or database software to create a CSV file, you can create a CSV file using any capable text editor such as Notepad, Vim, Notepad++, etc. All you need to do is enter a string of values (columns), which are separated by commas, and rows are separated by newlines. Here, how:
First, open a text editor like Notepad, then enter the column names or first row of data (a record) separated by commas onto the first line. For example, we’re typing the following headers in the file line:
Name,Gender,Country,AgeAnd, make sure to type without any unnecessary space in the string.
Then, type your values onto the second line, using the same format as the first line. Here, we’re entering the actual name, followed by gender, followed by country, and then age.
Dulce,Female,United States,32
Continue writing your values for each item onto each following line. If you want to leave any fields (value) empty, make sure to add the comma, or the remaining fields in the line will be shifted left when you import the file into a table. In the below example, we left out the country field in the 7th line, but we added a comma to leave a blank cell when we covert the data into the table.
Rules for Creating CSV files
The Internet Engineering Task Force (IETF) defines how a CSV file should be structured and formatted with the ‘RFC4180’ standard. According to this, there are few more rules you need to follow for formatting data in a CSV file. Here, ‘CRLF’ stands for ‘carriage return’ and a ‘linefeed’, which means a line break.
- Keep each row of data on a separate line, separated by a line break (CRLF).
Philip,Male,France,36 <CRLF> - You don’t have to follow the last row of data (line) with a line break.
Philip,Male,France,36 <CRLF>
Mara,Female,Britain,25- You could include an optional header line with a list of the column names in the first line of the file with the same format as the rest of the lines. The headers can contain the names related to the fields in the file.
First Name,Last Name,Gender,Country <CRLF>
Philip,Male,France,36 <CRLF>
Mara,Female,Britain,25 <CRLF>- Make sure each field in the header list and each record is delimited by commas. Each record/line should contain the same number of fields throughout the file. And the last field in the line must not be followed by a comma.
First Name,Last Name,Gender,Country <CRLF>
Shavon,Benito,Female,France- If you enclose fields in double-quotes, then double-quotes may not appear inside the fields.
"Sherron","Female","Great Britain","65" <CRLF>
Belinda,Female,Iceland,68- If you want commas, double quotes, semicolons, or line breaks to appear in the fields, then enclose the fields in double-quotes.
Shavonne,Female,"Fra;nce","10,000" <CRLF>Once you finish tying your data in the Notepad (text editor), click the ‘File’ menu and select the ‘Save’ option or press Ctrl+S.
Then, type the name for your file and end the filename with the “.csv” extension.
Next, select the ‘All Files (*.*)’ option from the ‘Save as type’ drop-down, then click the ‘Save’ button.
That’s all it takes to create a CSV file using a text editor.
Create a CSV file using Spreadsheet Applications
The easiest way to create a CSV file is to export a file to CSV format or save it in CSV format. Most of the spreadsheets, databases and other applications provide you with options to either export the data into CSV files or save it as CSV files. You can also download the data set in a CSV file. In many applications, a CSV file is made by clicking on the ‘File’ menu and selecting the ‘Export’ or ‘Save As’ option.
Using Microsoft Excel
Microsoft Excel allows you to save a file as a CSV file using the ‘Save As’ feature. To create a CSV file using Microsoft Excel, open Excel and then open the existing file you want to save in CSV format or create a new document.
To create a new document for CSV, first, add a column header or a field name for each piece of data you want to insert (e.g. first name, last name, address, city, state, and zip code) into the cells located in row 1 at the top of the worksheet.
Then, enter your data into the spreadsheet in the appropriate columns. There should be only one record in a row.
To save your spreadsheet as a CSV file, click ‘File’ and choose the ‘Save As’ option.
In the Save As page or window, type the name of your file into the ‘File Name’ field.
Then, choose ‘CSV UTF-8(Comma delimited) (*.csv)’ or ‘CSV (Comma delimited) (*.csv)’ format from the ‘Save as type:’ drop-down and click the ‘Save’ button.
When you click ‘Save’, only the active sheet will be saved as a CSV file. You have now created a CSV file using Excel.
Using Google Sheets
Google Sheets allows you to download its spreadsheet as a CSV file using the download feature. To do that, open the spreadsheet that you want to download as a CSV file in Google Sheets. Or, you can create a new spreadsheet in Google Sheets as we did in the above section.
Once you are done entering your data in the spreadsheet, click the ‘File’ menu, then expand the ‘Download’ sub-menu and select the ‘Comma-separated values (.csv, current sheet)’ option.
This will download the current sheet in the spreadsheet as a CSV file. Excel and Google sheets may contain formulas, styling, images, formatting, and other things, but they don’t carry over to the CSV file.
Note: Although most spreadsheet programs support multiple sheets, the CSV format does not support ‘sheets’ or ‘tabs’ and the information on the addition sheets won’t be recorded into CSV when you export or save the file. Only the current sheet gets saved.
Opening a CSV File
Because CSV files are so simple, they can be easily opened with any text editor (like Notepad) or a spreadsheet program (like Microsoft Excel, Google Sheets). In most of these programs, you can just click File > Open and select the CSV file to open it.
Open a CSV File in a Text Editor (Notepad)
Due to its simplicity, any text editor that can open a ‘.txt’ file can open a ‘.csv’ file. When you open a CSV file in a text editor like Notepad, it won’t show you formatted or structured data. Instead, it’ll just show you the values separated by the comma or other delimiter.
However, Notepad may have trouble opening the CSV file if it’s a large CSV file (file with size in GBs). In that case, try using a more powerful text editor like Notepad++, Vim, Sublime Text, etc. Notepad gives a simple and easy way to view the CSV file.
To open a CSV file in Notepad or your default text editor, right-click the CSV file in File Manager or Windows Explorer, and then select the ‘Edit’ (If you have Windows 11, select ‘Show more options and then select ‘Edit’). Alternatively, you can right-click the file, move the cursor over to the ‘Open with’ option, and then select ‘Notepad’ or any other text editor.
Now, you’ll see the data inside the CSV file in plaintext.
You can also open the text editor of your choice, then click ‘File’ and then ‘Open’ option.
In the ‘File Open’ dialog box, navigate to the CSV file and select it. Then, click ‘Open’
Now, you can read and edit the data like you would with any other file.
Open a CSV file in Microsoft Excel
Although you can easily view a CSV file with a simple text editor without the need for a spreadsheet application like MS Excel, it is not aesthetically appealing. Most spreadsheet applications are capable of opening and modifying CSV files, which also make them easier to understand.
If you have Microsoft Excel installed on your computer, then it will be the default program to open the ‘.csv’ files. Simply double-clicking the CSV file will open it in Excel. If it doesn’t open in Excel, you can right-click the CSV file and choose ‘Open With’, and select ‘Excel’.
Alternatively, if you already have Excel open, click on the ‘File’ tab and then click ‘Open’.
In the ‘Open’ window, navigate to the location that contains your CSV file. If you can’t find the file you want to open, click the drop-down menu next to the ‘File name’ field and change the file type to ‘Text Files (*.prn, *.txt, *.csv)’. This will show only the text files in the selected location.
Once you found the file, select it, and click ‘Open’ to open it in Excel.
Note: If the values inside the CSV file are not delimited by a comma (,), the values may not get separated into individual cells in columns, and all the values in rows may be grouped into a single column.
Open a CSV file in Google Sheets
If you don’t have paid spreadsheet program like Microsoft Excel, you can use free spreadsheet programs like Google Sheets to view/edit your CSV file online. Opening a CSV file in Google Sheets is as straightforward as Microsoft Excel.
First, open a blank spreadsheet file in Google Sheets. In the menu bar, click ‘File’, and click ‘Open’.
In the Open a file dialog box, select the ‘Upload’ tab and click the ‘Select a file from your device’ button. Or, if you have already uploaded your CSV file to your Google Drive, then select it from the ‘My Drive’ tab and click ‘Open’. You can also drag and drop the file from your computer into the ‘Open a file’ dialog window.
Then, find the CSV file you want to open on your local drive, select it, and click ‘Open’ to upload the file.
Once the file finishes uploading, it will automatically be formatted and loaded into the current blank sheet. Now, you can edit the file as you would with any other file.
You can also install and use the free office suite LibreOffice Calc to open and edit your CSV files.
View a CSV file using PowerShell
You can also view and read an existing CSV file in Windows PowerShell using the Import-CSV cmdlet. Here’s how:
First, open Windows PowerShell and use the ‘cd’ command to change the directory to the one where your CSV file is located. For example, we’re using the following command change directory in our system:
cd C:\Users\rajst\Documents
Then enter the following command and press Enter:
Import-Csv <filename>.csvIn the above command make sure to replace <filename> with the file name of your CSV file:
Import-Csv Contact.csvAnd filename should not contain any delimiter (like space) inside the name. The above command will convert the contents inside the CSV file and show it to you in a list as shown below.
Importing a CSV File into an Application
Many programs use CSV files for importing data. Whether it’s a contacts list from Google Contacts, data set from a spreadsheet program, or a large amount of information from a database program, you can use CSV files to export data. Then, that CSV files can be used to import data into any program that supports that type of data. Also, if the fields or values in the CSV files are delimited by any other delimiters (other than a comma), then the best way to open the file is to import it into the application.
You can find ‘Import’, ‘Import CSV’, Import/Export, or ‘Import Comma Separated Values’ options on programs that support CSV files to import data into them. Let’s see how to import CSV files into different applications.
Import a CSV File into Excel
Occasionally, when you get files from the Internet or receive files from a co-worker, they may be in CSV format. Also, when you try to open the CSV files directly in Excel, it may cause problems. So the best way to open the file in Excel is to import data into Excel so that the data is not changed.
To import data in Excel, open the Excel program and create a new workbook or open an existing one. Go to the ‘Data’ tab and click the ‘From Text/CSV’ button from the Get & Transform Data section in the Ribbon.
In the Import Data dialog, select the CSV file that you want to import to your Excel program, and click ‘Import’.
Another window will appear, here, choose your delimiter (Comma), and click the ‘Load button’.
The CSV files will be imported into a new Excel sheet as a table.
Normally, when you import a CSV file into an Excel workbook, the data will be imported into a new worksheet with the same name. But if you want to import data into a specific location in the current spreadsheet, follow these steps:
To import a CSV file into a specific location in the current spreadsheet, open the worksheet where you want to import the data. Then, switch to the ‘Data’ tab and click the ‘From Text/CSV’ button. Then, select the CSV file you want to import.
In the next window, click the drop-down arrow next to the ‘Load’ button and select the ‘Load To’ option.
A small Import Data dialog box will appear. Here, select the ‘Existing worksheet’ option, and click the range selection button (Upward arrow) to select the range of cells.
Then, select the range of cells where you want to insert CSV file content in the current sheet. Once you select the range, click the small ‘downward arrow’ in the collapsed Import Data window.
Finally, click ‘OK’ to import the data.
Now, the CSV file is imported into an existing worksheet at the selected location.
Import a CSV File into Google Sheets
Importing CSV files into Google Sheets is easier than Excel. In addition, Google Sheets gives you a few more importing options than Excel. Here’s how you can import CSV files into Google Sheets:
First, open a new spreadsheet or an existing file in Google Sheets. In the menu bar, select the ‘File’ menu, then click on the ‘Import’ option.
In the Import file dialog window, select the ‘Upload’ tab, and click the ‘Select a file from your device’ button or drag and drop the file here from your local drive.
Once, the file is uploaded, another ‘Import file’ dialog box will appear. Here, select your import location and separator type (delimiter) and click the ‘Import data’ button to import the CSV file.
In the Import location drop-down, you have different options for where you want to load your CSV data in the Google Sheets. Select the appropriate option.
- Create new spreadsheet – Choose this option to create a new spreadsheet file on your Google drive and import your CSV file data into it. The file name will be the same as your CSV file name.
- Insert new sheet(s) – Select this option to insert a new sheet (with the CSV file name) into the current spreadsheet and load the data into it.
- Replace spreadsheet – This option will replace the current whole spreadsheet with only one sheet of CSV file data.
- Replace current sheet – This option will let you replace only the current sheet/tab of the spreadsheet with the contents of the CSV file.
- Append to current sheet – Choose this option if you append the CSV file data at the end of the current sheet data.
Let’s say you have an incomplete contact list or customer data, and your co-worker sent you a CSV file that contains the rest of the data. You can use this option to join your co-worker’s data at the end of your data in the current sheet.
For example, you have this incomplete contact list in your spreadsheet:
To append the CSV file to this current sheet, select the CSV file in the ‘Import data’ dialog window, select the ‘Append to current sheet’ option for Import location, and click ‘Import data’.
This will append the CSV file data to the end of the current sheet’s data set as shown below.
- Replace data at selected cell – This option will replace the data at the selected cell with the CSV file data.
To do this, first, select the cell or range where you want to replace data.
Then, choose the ‘Import’ option from the ‘File’ menu, and select the CSV file. In the ‘Import file’ dialog box, select ‘Replace data at selected cell’ for the Import location and select the ‘Import data’ button.
This will replace the data at the selected cell location with data from your CSV file.
Import a CSV file into Google Contacts
Google only accepts the ‘.CSV’ file or the ‘Vcard’ file for importing the contact list into Google Contacts. If you want to import a contact list from a CSV file into Google Contacts, follow these steps:
First, open Google Contacts by clicking the waffle button on the Google page and selecting ‘Contacts’ from the drop-down.
On the Google Contacts page, click the ‘Import’ button on the navigation panel.
An Import contacts window will appear. There, click the ‘Select file’ button.
Then, choose your CSV file in the File Upload window and click ‘Open’
Once the file is uploaded, your CSV filename will appear next to the ‘Select file button. Then, click the ‘Import’ option.
The contact list from the CSV file will be imported into your Google Contacts app.
Most of the spreadsheet applications, database programs, and other applications provide the option to import CSV files, but the only steps to access that option may slightly differ. You can find the Import feature under the ‘File’ menu of most apps.
That’s everything you need to know about CSV files, how to create them, how to open them, and how to import and use them.
Member discussion