Most don’t think of a CSV file (comma-separated value file) when you think of data. You probably think of a database with database records consisting of fields and values. But you can store similar tabular information in a simple text file and eliminate the proprietary database with a CSV file.
Not a reader? Watch this related video tutorial!CRM systems need customer details like name, address, contact phone number. Mailing list software needs a name and email address for each user. CSV files can hold this data along with many other types. It’s a lot quicker to type into a file or and then mess with a database.
In this article, you will learn how to create and edit CSV files to store tabular information.
What is a CSV File?
In its simplest form, a CSV file is a text file that stores the information you’d draw on paper as a table. Think of a table as having records. Each record then has attributes of related information stored in rows with fields of attributes stored in columns.
In a CSV, these columns can and usually do have headings as you see below (Color, Model and Make).
What’s so special about a CSV file? They are a way to exchange tables of data between different computer systems.
Formal Definition
CSV files have existed even before the personal computer being handled by IBM’s Fortran compiler as far back as 1972 and used in the SuperCalc spreadsheet as far back as 1983. It wasn’t until 2005 that a formal definition for the CSV file was added by the Internet Engineering Task Force (IETF) in Request for Comments RFC4180.
To ensure CSV files always follow a particular pattern, the Internet Engineering Task Force (IETF) defined a Request for Comment (RFC) that defines what a CSV file should look like with RFC4180.
In a nutshell, this RFC tells us that a CSV file has a few attributes.
- It has records located on a separate line, delimited by a line break (CRLF).
- The last record in the file may or may not have an ending line break (CRLF).
- An optional header line appears as the first line of the file with the same format as normal record lines.
- Within the header and each record, there may be one or more fields, separated by commas.
- Each field may or may not be enclosed in double quotes style quotation marks.
- Fields containing line breaks (CRLF), double quotes, and commas should be enclosed in double-quotes.
- If double-quotes are used to enclose fields, then a double-quote appearing inside a field must be escaped by preceding it with another double quote.
CRLF stands for “Carriage Return/Line Feed” which is a formal definition of the end of the line in a text file
Prerequisites
It’s now time to learn how to build and edit CSV files. But, before you get too far, the tutorial expects you to meet a few prerequisites first.
This article will assume you have access to a simple text editor such as Notepad on Windows or Nano on Linux or Mac systems. If you’d like more advanced features like syntax highlighting (different colors representing different items), you can use more advanced editors such as Notepad++, VSCode, Vi, or EMACS.
If you’d like to follow along with the examples in this tutorial, you’re expected to have Microsoft Excel (any recent version) and PowerShell (any version).
Creating a CSV File
Let’s start by creating a simple CSV file to use as a demonstration. Throughout this article, you’ll use this CSV file across various demos.
Using a Simple Text Editor
There are many ways to create a CSV file. One of the easiest without any fancy software is to use Notepad if you’re on Windows.
To create a simple text file using a plain text editor (Notepad on Windows):
1. Open Notepad which will open a blank file.
2. Copy and paste the text below into the blank file. This text represents CSV data and contains three columns (Name, Address, and Email) with two data rows; one row for Joe Bloggs and one row for Jane Bloggs.
Name,Address,Email
Joe Bloggs,1 Anystreet,[email protected]
Jane Bloggs,1 Anystreet,[email protected]3. Now save the file as c:\temp\Address Details.csv.
By default, Notepad will add .txt to the file extension; avoid this by selecting “All files” from the save as type drop-down and including the .csv extension with the filename.
Congratulations! You now have a CSV file!
Using Microsoft Excel
Most of the time, creating a CSV file with a plain text editor isn’t how to go. A text editor like Notepad doesn’t “understand” you’re creating a CSV file. It doesn’t know you’re building a specific table structure of rows and columns; it just sees text.
You need an application that understands CSV files. One of those applications is Microsoft Excel. Microsoft Excel is one of the most common applications to work with CSV files. Some people even call CSV files spreadsheets.
Assuming you’re on Windows and have Excel installed:
1. Open Excel. It should prompt you to create a new workbook as shown below.
2. Select Blank Workbook.
You may be wondering why you’re creating a new workbook instead of a CSV file. Excel works with workbooks and worksheets. To create a CSV file, as you’ll see, you must create a workbook which contains a worksheet that you’ll save as a CSV file.
3. In the worksheet, input the following three data rows with the three headers as shown below.
"Name","Address","Email"
"Joe Bloggs","1 Anystreet, Anytown","[email protected]"
"Jane Bloggs","1 Anystreet, Anytown","[email protected]"
"John O'Brian","Anytown","[email protected]"The CSV fields above are enclosed in double-quotes. Why? Because the name
John O'Briancontains an apostrophe, and the addresses contain a comma. When a value contains an apostrophe or comma, you must enclose the field with double-quotes for programs to understand the CSV file correctly.
When complete, your Excel worksheet will look like below.
If you decided to ignore the tutorial’s instruction and instinctively copied and pasted the text above into the spreadsheet, the fields did not split into columns (A, B, and C). If so, use the Excel split data feature.
4. Now save the worksheet as a CSV by clicking on File —> Save and provide a name for the CSV file.
5. In the save dialog box, choose CSV (comma delimited) as the file format and click Save.
Using PowerShell
Since this blog is for IT professionals and IT pros love PowerShell, let’s next create a CSV with PowerShell from Microsoft. Using PowerShell, you can create CSV files in many different ways, with the most popular using the Export-Csv cmdlet.
To create a CSV file with PowerShell and the Export-Csv cmdlet:
1. Open a PowerShell console.
2. Run the Get-Process cmdlet to return some kind of output. The Export-Csv cmdlet “converts” PowerShell objects to CSV files so you need a few objects. Get-Process
Get-ProcessPowerShell then returns a list of processes. Each process is an object with properties.
3. Now pipe the output of the Get-Process cmdlet to the Export-Csv cmdlet. This code below creates a CSV file called processes.csv in the current working directory.
Get-Process | Export-Csv -Path processes.csvEditing CSV Files
If you’ve been following along, you should have three CSV files at this point, all created with different methods. Now that you have a few CSV files to work with let’s now learn how to edit existing CSV files in a few different ways.
Using Microsoft Excel
In a previous section, you created a simple CSV file with Excel. It stands to reason; you can also edit CSV files with Excel, too, so let’s do that.
Assuming that you followed along in the Creating a CSV File section using Excel, you should have a CSV file located in c:\temp called Address Details.csv. Let’s open it.
1. Open the Windows File Explorer and navigate to c:\temp.
2. Double click the file to open in Excel.
If you double-click on a CSV file in Windows, Windows will use Excel to open the file. Excel is the default application to open CSV files.
In the previous section, the CSV data you entered had double quotes around each header and data field. Unlike a text editor like Notepad, Excel understands CSV files and removes these, knowing they are part of the CSV structure.
3. In row 5, add Sophie O’Brian as shown below.
4. Click Save and close Excel.
5. Now, right click the Address Details.csv file in File Explorer. You should see an Edit menu option as shown below.
6. Click on Edit, and the CSV file should open in Notepad. You can see below that adding the row in the CSV file with Excel added the fourth data row with double quotes only where necessary (with spaces).
If you’d rather the Edit menu option opens the CSV file in another program, change the Windows default application.
Using PowerShell
For the final demo, let’s jump into using PowerShell to read and edit a CSV file. Similar to creating a CSV file using the Export-Csv cmdlet, PowerShell has an Import-Csv cmdlet which does the opposite; it reads an existing CSV file.
Assuming you still have that c:\temp\Address Details.csv CSV file created earlier:
1. Open a PowerShell 6+ console.
Windows PowerShell users: This demo will work nearly the same with Windows PowerShell also. The only difference being when you create the CSV with the
Export-Csvcmdlet, it will, by default, add a line at the top of the CSV that looks like#TYPE System.Management.Automation.PSCustomObject. To remove this, use theNoTypeInformationparameter.
2. Read the CSV file with the Import-Csv cmdlet and assign the output to a variable called $addr. To read a CSV file, you don’t have to assign the output to a variable. The tutorial is only doing this because you’ll edit the CSV file later.
Using the
Import-Csvcmdlet, PowerShell reads each row in the CSV file and converts each data row to an object.
3. Now, create a hashtable and cast the hashtable to a new PSCustomObject object representing a new row. The object below represents a record for Sophie O’Brian.
$newrow = [PSCustomObject] @{
"Name" = "Sophie O'Brian";
"Address" = "The Old Postoffice, Anytown";
"Email" = "[email protected]"
}
4. Add the object represented via the variable $newrow to the existing $Addr array of objects previously read from the CSV file.
5. Once the $Addr array contains the new object, export the array of objects back to the same CSV file with Export-Csv. The Export-Csv will then overwrite the original CSV file using the Force parameter to allow the file to be overwritten.
$Addr | Export-CSV '.\Address Details.csv' -Force
Export-CsvTo append rows to a CSV file rather than overwriting, use the
Appendparameter.
6. Now, open the CSV file in Notepad. Now notice that PowerShell added the row, but all of the fields are surrounded by double-quotes. Excel and PowerShell save CSV files differently.
Conclusion
The CSV file format is one of the oldest file types there is. Its simple form makes it incredibly useful for exchanging tabular information between different programs or computers.
CSV files can be created in a text editor, or for longer and more complex tables, in a spreadsheet.
Now, what projects can you think of that CSV files may help out with?
