A CSV file is a text-based spreadsheet that uses the comma to separate its data records. They can be imported and exported in various formats, such as Microsoft Excel or Google Sheets. This article explores how you might use this type of file, whether it’s for personal use or work purposes.
A CSV file is a text file that uses comma-separated values. It can be created by using Microsoft Excel or other spreadsheet software. They have been around for years and are used to transfer data between programs. Read more in detail here: is a csv file an excel file.
When most people think of data, they don’t think of a CSV file (comma-separated value file). You presumably envision a database having fields and values in the database records. With a CSV file, you may store equivalent tabular data in a simple text file and remove the proprietary database.
Customer information like as name, address, and phone number is required by CRM systems. Each user of mailing list software requires a name and an email address. This data, as well as many others, may be stored in CSV files. It’s far faster to enter into a file than to fiddle with a database.
This tutorial will teach you how to create and modify CSV files, which are used to store tabular data.
What is a CSV file, exactly?
A CSV file is a text file that holds information in the same way as a table would be drawn on paper. Consider a table to be a collection of records. The characteristics of relevant information are then recorded in rows, with fields of attributes stored in columns, for each entry.
These columns in a CSV may and generally do include headers, as seen below (Color, Model and Make).
What makes a CSV file so unique? They’re a technique for various computer systems to share data tables.
In Microsoft Excel, an example CSV of automobiles is presented.
Definition formally
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 Definition formally for the CSV file was added by the Internet Engineering Task Force (IETF) in Request for Comments RFC4180.
The Internet Engineering Task Force (IETF) established RFC4180, a Request for Comment (RFC) that describes what a CSV file should look like, to guarantee that CSV files always follow a certain pattern.
In a nutshell, this RFC specifies the properties of a CSV file.
- It contains records on distinct lines, each separated by a line break (CRLF).
- There may or may not be an ending line break in the file’s final record (CRLF).
- The first line of the file, an optional header line, has the same structure as standard record lines.
- There may be one or more fields separated by commas in the header and each entry.
- Double quotes style quotation marks may or may not be used to surround each field.
- Double-quotes should be used to surround fields containing line breaks (CRLF), double quotes, and commas.
- A double-quote appearing within a field must be avoided by preceding it with another double quote if double-quotes are used to enclose fields.
CRLF stands for “Carriage Return/Line Feed” which is a Definition formally of the end of the line in a text file
Prerequisites
Now you must learn how to create and modify CSV files. However, before you go any farther, you must first complete a few conditions.
This post assumes you have a basic text editor installed, such as Notepad on Windows or Nano on Linux or Mac. You may use more powerful editors like Notepad++, VSCode, Vi, or EMACS if you want more advanced capabilities like syntax highlighting (various colors indicating different elements).
You’ll need Microsoft Excel (any current version) and PowerShell if you want to follow along with the examples in this tutorial (any version).
CSV File Creation
Let’s begin by making a basic CSV file to utilize as an example. This CSV file will be used in many examples throughout this essay.
Making Use of a Basic Text Editor
A CSV file may be created in a variety of ways. If you’re on Windows, you can use Notepad to accomplish it without any special software.
To create a basic text file using a plain text editor (Windows’ Notepad):
1. Start Notepad and create a new file.
2. Paste the following content into the blank document. This text is CSV data with three columns (Name, Address, and Email) and two data rows (one for Joe Bloggs and one for Jane Bloggs).
3. Name the file c:tempAddress Details.csv and save it.
Notepad will add.txt to the file extension by default; to prevent this, choose “All files” from the save as type drop-down and include the.csv extension in the filename.
To save a CSV file in Notepad, choose Save as type.
Congratulations! You have a CSV file now!
Working with Microsoft Excel
Most of the time, CSV File Creation with a plain text editor isn’t how to go. A text editor like Notepad doesn’t “understand” you’re CSV File Creation. It doesn’t know you’re building a specific table structure of rows and columns; it just sees text.
You’ll need a program that can work with CSV files. Microsoft Excel is one of these programs. One of the most popular apps for working with CSV files is Microsoft Excel. CSV files are even referred to be spreadsheets by some.
Assuming you’re using Windows and Excel, follow these steps:
1. Start Excel. As illustrated below, it should ask you to create a new worksheet.
New Document Dialog in Excel
2. Choose the Blank Workbook option.
You may be asking why a new workbook is being created rather than a CSV file. Workbooks and worksheets are supported by Excel. As you’ll see, creating a CSV file requires creating a workbook with a worksheet that you’ll save as a CSV file.
3. Fill in the following three data rows in the spreadsheet with the three headings given below.
“Joe Bloggs”,”1 Anystreet, Anytown”,”[email protected]” “Name”,”Address”,”Email” [email protected],”Jane Bloggs,”1 Anystreet, Anytown” “Anytown”,”John O’Brian”,”[email protected]”
Double-quotes surround the CSV fields above. Why? Because John O’Brian’s name includes an apostrophe and the addresses include a comma. If a value includes an apostrophe or a comma, the field must be enclosed in double-quotes for computers to comprehend the CSV file properly.
When finished, your Excel spreadsheet should look like this.
Output in Excel
The fields did not divide into columns if you ignored the tutorial’s instructions and immediately copied and pasted the text above into the spreadsheet (A, B, and C). If this is the case, utilize Excel’s split data tool.
4. Now save the worksheet as a CSV by clicking on File —> Save and provide a name for the CSV file.
5. Select CSV (comma delimited) as the file format in the Save dialog box and click Save.
Select a File Format
Scripting Scripting using PowerShell
Since this blog is for IT professionals and IT pros love PowerShell, let’s next create a CSV with PowerShell from Microsoft. Scripting Scripting using PowerShell, you can create CSV files in many different ways, with the most popular using the Export-Csv cmdlet.
Scripting using PowerShell and the Export-Csv cmdlet, produce a CSV file:
1. Start a PowerShell session.
2. To get any kind of output, use the Get-Process cmdlet. You’ll need a few PowerShell objects to use the Export-Csv cmdlet since it “converts” them to CSV files. Get-Process
After that, PowerShell displays a Processes Chart. Each process is a property-rich object.
Processes Chart
3. Finally, feed the Get-Process cmdlet output to the Export-Csv cmdlet. This code produces the processes.csv CSV file in the current working directory.
-Path processes.csv | Get-Process | Export-Csv
CSV File Editing
If you’ve followed along, you should now have three CSV files, each prepared using a different way. Let’s learn how to alter existing CSV files in a few different ways now that you have a few CSV files to work with.
Working with Microsoft Excel
You prepared a basic CSV file using Excel in the previous lesson. It stands to reason that you can edit CSV files in Excel as well, so let’s get started.
Assuming that you followed along in the CSV File Creation section using Excel, you should have a CSV file located in c:temp called Address Details.csv. Let’s open it.
1. Navigate to c:temp using Windows File Explorer.
2. To open the file in Excel, double-click it.
Excel opens a CSV file
When you double-click a CSV file on Windows, the file will be opened in Excel. The default program for opening CSV files is Excel.
The CSV data you submitted in the previous step included double quotes around each header and data column. Excel, unlike a text editor like Notepad, recognizes CSV files and eliminates those that are not part of the CSV structure.
3. Add Sophie O’Brian to row 5 as indicated below.
New Record in Excel
4. Save the file and exit Excel.
5. In File Explorer, right-click the Address Details.csv file. As illustrated below, you should see an Edit menu choice.
Edit CSV
6. Select Edit, and Notepad should open the CSV file. As you can see below, Excel appended the fourth data row to the CSV file, using double quotes only where appropriate (with spaces).
With the New Record, edit the CSV.
Change the Windows default application if you want the Edit menu option to open the CSV file in another software.
Scripting Scripting using PowerShell
For the final demo, let’s jump into Scripting Scripting using PowerShell to read and edit a CSV file. Similar to CSV File Creation using the Export-Csv cmdlet, PowerShell has an Import-Csv cmdlet which does the opposite; it reads an existing CSV file.
Import-Csv helps you manage CSV files in PowerShell.
Assuming you still have the c:tempAddress Details.csv CSV file you created before, follow these steps:
Start by launching a PowerShell 6+ console.
Users of Windows PowerShell: This example will work on Windows PowerShell as well. The only difference is that when you use the Export-Csv cmdlet to produce the CSV, it will include a line at the beginning that says #TYPE System.Management.Automation.PSCustomObject by default. Use the NoTypeInformation option to disable this.
2. Using the Import-Csv cmdlet, read the CSV file and assign the result to the variable $addr. You don’t need to assign the output to a variable to read a CSV file. This is only done in the lesson since you’ll be editing the CSV file afterwards.
Check that the CSV to Variable import worked.
PowerShell scans each row in the CSV file and turns each data row to an object using the Import-Csv cmdlet.
3. Create a hashtable and convert it to a new PSCustomObject object to represent a new row. Sophie O’Brian’s record is represented by the item below.
[PSCustomObject] $newrow “Name” = “Sophie O’Brian”; “Address” = “The Old Postoffice, Anytown”; “Email” = “[email protected]” @ “Name” = “Sophie O’Brian”; “Address” = “The Old Postoffice
At add a new item to the end of the list, use PowerShell.
Creating Objects from HashTables is related.
4. In the $Addr array of items previously read from the CSV file, add the object represented by the variable $newrow.
Check the box next to the new item in the list.
5. Use Export-Csv to export the array of objects back to the same CSV file once the $Addr array has the new item. The Force argument in the Export-Csv command will then overwrite the original CSV file, allowing the file to be rewritten.
‘.Address Details.csv’ -Force $Addr | Export-CSV
Export-Csv is used to create a new CSV file.
Use the Add option to append rows to a CSV file rather than overwriting it.
Understanding Import-Csv and the ForEach Loop is related.
6. In Notepad, open the CSV file. PowerShell has now inserted the row, however all of the fields are enclosed in double-quotes. CSV files are saved differently in Excel and PowerShell.
Export-CSV Notepad opened the newly generated file.
Conclusion
CSV files are one of the oldest file formats available. Its straightforward design makes it ideal for transferring tabular data across applications or machines.
CSV files may be produced in a text editor or in a spreadsheet for longer and more complicated tables.
Now, what projects do you have in mind where CSV files may be useful?
A CSV file is a text file that contains comma-separated values. They are often used for data importing and exporting. The “how to open csv file in excel” article will show you how to open a CSV file in Excel.
Related Tags
- how to create csv file in python
- create csv file powershell
- how to open csv file
- csv file download
