How to convert Excel to CSV?Microsoft Excel is one essential spreadsheet program that comes bundled with the Microsoft Office suite. It is a powerful spreadsheet program that lets users record huge datasets of various types. Additionally, the software supports almost all popular document formats, and there may be cases when we may need to convert between different formats. Excel supports various file formats; however, the default file extension for Excel files is XLS or XLSX. The CSV is another popular file format that is often used in Excel. When we talk about converting Excel to CSV, we often refer to converting XLS/XLSX file formats of Excel documents into CSV file formats. Before we learn the methods or solutions on how to convert Excel to CSV, let us first briefly discuss the introduction of XLS/XLSX and CSV files: Excel Spreadsheet (XLS or XLSX)The XLS and XLSX are the default file formats for Excel spreadsheets. The XLS file format is used for Excel 2007 and earlier versions, while the XLSX file format is used on Excel 2010 and higher versions. The data stored in these file formats can only be viewed using any spreadsheet programs like Excel, Google Spreadsheets, OpenOffice, etc. The primary advantage of these Excel formats is that we can store advanced formatting, functions, formulae, and other useful objects like pictures, clip arts, shapes, charts, etc. The Excel spreadsheet stores data within cells formed by the intersection of rows and columns. Due to all these features, the Excel spreadsheet formats XLS and XLSX cannot be accessed using a text editor program such as Notepad. Comma Separated Value (CSV)Generally, a CSV file format stores the tabular data (both text and numbers) in plain text that can be accessed or viewed using the modern text editor and spreadsheet programs. The main advantage of this file format is that we can use it in the most relevant programs and systems. In particular, the CSV file format makes it easier to import/ export data between different programs and diagnose problems. As the name suggests, the data within the CSV files are usually separated using commas. However, the encoding of CSV varies depending on its selected type. The latest version of Excel (Excel 365) supports the following CSV formats:
Not all of these CSV formats may be available for earlier versions of Excel. How do we convert Excel to CSV?Since both the formats, Excel's XLS/ XLSX and CSV, are widely used, there are a couple of useful ways to help us convert between these formats, be it Excel to CSV or CSV to Excel. Below, we discuss some easy-to-use methods to fulfil our needs for converting an Excel file to a CSV file format: Converting Excel to CSV using Excel's Save As FeatureWhenever we need to convert Excel files into CSV documents, the most common and straightforward way is to use this powerful spreadsheet software's 'Save As' feature. Here are the steps we need to perform to convert Excel documents into CSV:
Note: If we want to keep non-ASCII characters in our CSV files after converting them from Excel default formats (XLS/ XLSX), we must save the corresponding files using Unicode character encoding.Converting Excel to CSV using OpenOfficeLike the Microsoft Office Suite, the OpenOffice is an open-source software kit with six different programs: Text Document, Spreadsheet, Presentation, Drawing, Database, and Formula. The spreadsheet program is more like Excel and works offline similarly. It is often referred to as OpenOffice Calc. OpenOffice Calc is another good option after Excel software when we require converting Excel to CSV offline. This office kit is free to download and has relatively low in size. Also, the OpenOffice Calc offers more options (such as encodings, delimiters, etc.) than Excel or any other software during the conversion between Excel to CSV. We can execute the below steps to convert Excel documents into CSV files using OpenOffice Calc:
Note: There are many other Office software programs developed by different vendors. Most of such software allows the conversion of files similar to Microsoft Office and OpenOffice. So, we can also leverage them to convert Excel (XLS/XLSX) into CSV documents.Converting Excel to CSV using Google SpreadsheetsGoogle Spreadsheets is another popular spreadsheet software and a big competitor to 'Excel for Web'. Like Excel for Web (or Excel Online), Google Spreadsheets also works via modern web browsers like Chrome, Firefox, Edge, etc. Since Google Spreadsheets is also developed to handle spreadsheets and workbooks, it allows the opening of popular file formats created in Excel. Therefore, we can open our desired Excel file in Google Spreadsheets and convert or save it in the CSV format. The use of Google Spreadsheets for the conversion of Excel documents (XLS/XLSX) to CSV includes the following steps:
The only drawback of using Google Spreadsheets to convert Excel to CSV is that we need an internet connection to use this online spreadsheet software/tool. Also, the converted CSV file may not display special characters when opened in Excel software. However, the formatting from the original Excel file may appear for some converted CSV files opened in other spreadsheet programs. Converting Excel to CSV using Online ToolsWith an internet connection, many more online tools or converters can help us convert Excel to CSV with a few clicks. However, they are completely different as compared to Google Spreadsheets. Google Spreadsheets is an online spreadsheet program similar to Excel. But, the online converters are the programs purposely developed to convert between different file formats. When we need to convert Excel to CSV using the online converters, the best options are Zamzar, Cloudconvert, and Convertio. All these online converters allow free conversions for a limited number of files daily. The visual appearance of these three online tools may vary; however, the process of converting Excel to CSV remains the same for each. Let us understand the common steps of one of these online tools (i.e., Zamzar) to learn how to convert Excel to CSV:
The converted CSV file may not contain special characters like the above methods. How to convert Excel to CSV without destroying or damaging special characters?To keep the special or non-ASCII characters in the converted CSV file, we must choose the Unicode character encoding option while saving the individual CSV file. There are mainly two Unicode encoding formats in Excel, namely UTF-8 (8-bit) and UTF-16 (16-bit). The UTF-8 encoding is considered a better option to keep non-ASCII characters undamaged in CSV files. It is a more compact encoding, using 1-4 bytes for each symbol, while UTF-16 uses 2-4 bytes. Suppose we have non-ASCII characters in our Excel file, and we need to convert this file into CSV without damaging the non-ASCII characters. In that case, we only need to select one of the encoding formats (i.e., UTF-8 or UTF-16) in the 'Save As' dialogue box, while all the other steps remain the same as discussed above. However, when using Excel 2013 or earlier versions, we must save the CSV file using the 'Unicode Text (*.txt)' from the 'Save as type' drop-down list in the 'Save As' dialogue box. Afterwards, we must open the corresponding CSV file in Notepad, replace the tabs with commas using the 'Find and Replace (Shortcut- Ctrl + H)' feature and save it again with the CSV extension instead of TXT. Also, the encoding type must be selected as 'UTF-8 with BOM'. Important Points to Remember
Next TopicHow To Create Table in Excel |