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:

• CSV (comma delimited): This format helps save data in a comma-separated text file that is usually compatible with other supported Windows software and Windows operating systems.
• CSV (Macintosh): This format helps save data in a comma-separated text file that is usually compatible with the Mac operating systems.
• CSV (MS-DOS): This format helps save data in a comma-separated text file that is compatible with the MS-DOS versions.
• CSV UTF-8 (comma delimited): This CSV format includes the Unicode Transformation Format 8-bit encoding that helps us save special characters, such as hieroglyphs, accented and many other characters. Also, this specific format has backward compatibility with ASCII (American Standard Code for Information Interchange).

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 Feature

Whenever 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:

• First, we need to open a specific Excel document and select the worksheet we need to convert as CSV.
  
• Next, we must navigate the File tab and select the 'Save As' option in the list. This will launch the 'Save As' window.
  
  Alternatively, we can press the keyboard shortcut key 'F12' to open the 'Save As' window/ dialogue box.
• In the 'Save As' window, we must navigate to the folder we want to save our CSV file after the conversion. After that, we must specify the desired name for the CSV file in the 'File name' box and choose the 'CSV (Comma delimited)' in the 'Save as type' drop-down list.
  
• In the next step, we must click the 'Save' button. As soon as we click the 'Save' button, Excel may display another window with two additional buttons. This window notifies us that only the selected sheet will be converted into a CSV file. We must click the 'OK' button to continue the process.
  
• In the next window, Excel notifies us that some features of our Excel sheet may not be saved in the CSV file as the CSV file does not support them. Lastly, we must instruct Excel to save the CSV file without including the worksheet's feature. For that, we must click the 'Yes' button.
  
  This will create or save the converted CSV file in our selected location.
  
  It is essential to note that the above steps only convert the selected worksheet of Excel into a CSV document. So, we must convert each specific worksheet into a CSV file separately. In other words, we need to perform the above steps multiple times for each worksheet.
  Also, the CSV file will not contain any formatting, formulas, charts, shapes, or other objects from the given source worksheet. It occurs due to the selected CSV format. As we said earlier, the basic CSV (comma delimited) format only keeps the ASCII characters.

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 OpenOffice

Like 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:

• First, we need to open OpenOffice Calc and navigate to File > Open to select the Excel document to open it with the OpenOffice Calc.
  
• After opening the Excel file in OpenOffice Calc, we must navigate to the File > Save As and select the 'Text CSV (.csv)' format next to the 'Save as type' box. Also, we can choose the desired name and the location for the CSV file.
  
• Next, we must specify the desired encoding and delimiters by clicking on the 'Keep Current Format' button.
  
  For example, we can use the following arrangements to save our CSV file with the special characters:
  • Character set: Unicode (UTF- 8)
  • Field delimiter: Comma, Semicolon, and whatever delimiter we desire.
  • Text delimiter: Quotation marks
  
  
• Lastly, we must click the OK button, and a newly created (or converted) CSV file appears in the selected location.
  

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 Spreadsheets

Google 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:

• First, we need to open any web browser (for example- Google Chrome) and navigate to the official website of Google Spreadsheets using the following link:
  https://docs.google.com/spreadsheets/
  If it asks us to log in, we must use any Google Account. If any Google Account is already connected or logged in, the main page of Google Spreadsheets will be displayed automatically. It will not ask to log-in in such a case.
• After opening the main page (or Home page) of Google spreadsheets, we need to click on the 'Plus' sign to create a new blank sheet.
  
• In the newly created Google Spreadsheet, we need to go to the 'File' tab and click on the 'Import' option from the list.
  
• Next, we must choose to upload or drag the desired Excel file into the 'Import File' window. We can also copy-paste the data from our Excel sheet into Google Spreadsheet that we created. It is an easy option when we have small data sets in our Excel file.
  
• After the data has been imported to Google Spreadsheets, we need to go to the 'File' tab again and click on the 'Download' button. Afterwards, we must click the "Comma-separated values (.csv, current sheet)" option, as shown below:
  
• Lastly, we must choose the location to download or save our converted CSV file and open it using any text editor program like Notepad to view its data. Matching the data in Excel and CSV files is recommended to ensure all data is exported correctly.

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 Tools

With 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:

• First, we need to visit the official website of the respective online converter. To use Zamzar, we need to go to the following link:
  https://www.zamzar.com/
  
  The Zamzar tool allows a maximum of 50 MB for a single file conversion at a time.
• Next, we need to click the 'Choose Files' button and select the desired Excel to upload to the respective server from our computer/device.
  
• Once the desired Excel file is uploaded, we must select the desired output format (i.e., CSV) using the drop-down list, as shown below:
  
• In the next step, we must click the 'Convert' button and wait for the conversion process to complete.
  The 'Download' button appears on the screen after the conversion process. We can click the 'Download' button and save the converted CSV file to the desired location in our device's storage.
  

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

• We can't convert multiple worksheets of Excel into CSV formats at once.
• Although there are many online convertor tools to convert Excel to CSV, it is recommended not to use such tools when converting a file that contains sensitive information. We cannot completely trust third-party online tools regarding data security.