This tutorial is a detailed guide to creating CSV files in Microsoft Excel. We will also learn to open file types such as CSV, VCF, and XML files in Excel. Let’s get started!
What is a CSV file?
CSV, also known as Comma Separated Values file, is a plain text file that can be opened in an application or OS. CSV files can be opened in text files Word or Notepad, spreadsheet files Excel or Google Sheets, and OS like Mac and Windows.
Steps to Create a CSV file in Excel
Let’s get started with creating a CSV file in Excel, following the steps below.
Let us take an example of an Excel file that is going to be converted into a CSV file. Notice that the Excel file has a few formatting added to it.
Once your data is saved in Excel, follow the steps below.
- Go to the File tab.
- Click Save As.
- Click Browse.
- Select CSV (Comma delimited) file type before saving.
- Choose a location to save your file.
- Click Save.
The Excel file saves as a CSV file on your system. Reopen the file, and you will find that the data has lost all formatting applied to it. Your data retains as plain text in an Excel worksheet.
All merged cells have been unmerged, text formatting such as bolds, italics, cell colors, and borders have been cleared.
Steps to open a CSV file in Excel
If you already have an existing CSV file on your system and want to open it in Excel, follow the steps below.
Also read: How to Insert PDF in Excel?
Importing a CSV file in Excel
We’re exporting the data from a CSV file to an Excel workbook. We’re using the same CSV file that we just created in the example above.
- Open a blank Excel worksheet or workbook.
- Go to the Data tab.
- Pull down on Get External Data.
- Click From Text.
- Choose the CSV file..
- The text import wizard opens for you.
- Choose Delimited and press Next.
- Unselect all selected options and check “Comma” because we’re importing a CSV file.
You can see that the data has been arranged appropriately. You can adjust the vertical lines by yourself if you are not satisfied.
- Click Next.
- Choose General and click Finish.
You will be carried forward to a new window named Import Data.
- Choose Existing File.
- Select the cell where you want the data to be applied.
- After selecting add $ before the column and row name. For example, =$A$1.
- Click OK.
You can see that the CSV file is imported into the Excel file successfully. Your data will now be saved as an Excel file.
Steps to open an XML file in Excel
An XML or extensible markup language file, like HTML, is used to store and transport data. The data in it is saved as codes that can be read by both humans and machines.
You can find XML documents in folders that contain the data files of a specific application.
Let’s begin opening an XML file in Microsoft Excel. This is the XML file we want to open in Excel.
- Open a random Excel file.
- Go to the File tab.
- Click Browse.
- Choose the XML file.
- Choose As a read-only workbook.
- Click OK.
You can see that the XML file is exported in the Excel file.
Steps to open a VCF file in Excel
A VCF or a vCard file is a file type that stores the contact information of a person. It contains contact details like email addresses, phone numbers, postal addresses, full name, organization’s name, etc.
Let’s begin opening a VCF file in Excel.
- Open an Excel file.
- Go to the File tab.
- Click Browse.
- Change from All Excel files to All files.
- Choose the VCF file.
- The Text Import Wizard opens to help you get started.
- Select Delimited.
- Click Next.
- Check Tab, Semicolon, and Other.
- Type : in the Other box.
- Check if the contact details are arranged properly.
- Click Next.
- Click Finish.
You can see that the VCF file data has been exported to the Excel file successfully.
Conclusion
This article was a detailed guide to opening CSV, XML, and VCF files in Microsoft Excel. We also learned to create a CSV file from scratch. If you still have any doubts on this topic, email us at contact@quickexcel.com.
References: Constant Contact, Software Testing Help, SpreadsheetWeb