How to transfer Excel file to CSV (step by step)

CSV (Comma Separated Values) – is a type of file that contains information (such as text and numbers) in a continuous sheet of text. Documents in this extension are supported by most modern programs and applications, so CSV is very popular as one of the ways to import and export data. Files in this format allow you to quickly identify the problem (if any), because when errors occur, data separation and citation rules change. CSV is easy to understand for beginners, ordinary and more advanced users, since this type contains simple text, and it does not require a high level of knowledge or understanding of how complex encodings work.

In this guide, we will look at the fastest and most convenient ways to convert data, and also try to explain how to preserve various foreign characters when transferring data, and how to carry out this process with UTF-8 and UTF-16 encoding.

Converting files to CSV

Do you need to use Excel files in another program? To do this, you need to first turn them into CSV files and only then transfer them to other applications.. Next, we will present a step-by-step guide to converting and transferring a file using the “Save As” function:

1. In the document, go to the “File” tab, and select “Save As”. Or you can press F12 and choose Save As from the menu.
2. In “File type” select CSV (separator – commas) (.csv)

In addition to this file type, there are several additional extensions:

• The separator is commas. This extension will save the file as comma-separated text and make it available for use in any other application. In addition, it will be compatible with other versions of Windows.
• Macintosh. This extension makes it possible to use the file on Mac OS.
• MS DOS. Allows you to use the file on MS-DOS.
• Unicode (.txt). This is a standard extension among most programs that is read by almost all operating systems. It can display characters from almost all modern languages. And it has support for several ancient languages. So, if you have non-standard characters in your document, it is better to save them in this extension.

The note. All of the above formats will save information only on the current worksheet.

1. Select the folder where you want to save the file and click Save. After that, several dialog boxes will pop up. Don’t worry – everything works as it should, these are not errors.
2. One window will tell you that only the active worksheet will be saved. If you are satisfied with this, click OK. If you need to save information on all worksheets, click “Cancel”. There is no separate function to save all the data at once, so you will need to save the contents of each sheet separately as a regular Excel file, and then convert them to CSV.
3. The second window tells you that your file may contain information that is not supported by the CSV code. This is also normal, so click “Yes”.

That’s it, your files have been converted. This is a fairly simple, straightforward and quick process that is unlikely to cause any problems or complications.

How to convert files to CSV with UTF-8 and UTF-16 encoding

Let’s say you have an Excel document containing foreign characters, for example, various hieroglyphs, then the above conversion method will not work. This is because when saved this way, all non-ASCII (American Standard Code for Information Interchange) characters are displayed incorrectly.. And if your document contains quotes or long spaces (after transferring or copying the file to Excel from Word), these characters will also not be displayed as expected.

The easiest way is to save the Unicode Excel file and then convert it to CSV. В In this case, all characters that do not belong to the standard information exchange code will not go anywhere and will be displayed as they should.

Before we continue with using encodings while saving files, we need to find out main differences between UTF-8 and UTF-16 encoding so that the user can make the right choice for himself.

UTF-8 uses 1-4 bits per character, so overall files are quite small.. Typically, this encoding is used for files that mostly use standard code characters for information exchange. Another advantage of this file type is that the character encoding of the standard information exchange code is exactly the same as the code files.

UTF-16 uses 2-4 bits. It would seem that such files should take up more space, but no. They rarely require more storage space. For example, UTF-8 needs 3-4 bits to store Japanese characters, while UTF-16 needs only 2-4.

So this encoding is good to use if your documents have a lot of Asian characters from Japanese, Korean or Chinese. The disadvantages include that such an encoding is not fully compatible with interchange codes and requires a program capable of recognizing Unicode in order to display such characters. So, this should be remembered when the user is going to import Excel files somewhere.

How to convert Excel files to CSV with UTF-8 encoding

Suppose you have a table with foreign characters – in our case, these will be Japanese characters:

In this case, you will need to make sure that these characters remain in place when transferred:

1. Select File, Save As.
2. Give the document a name and select the file type and save.
3. Open this document with any text program such as Notepad.

The note. Some simple text programs do not support all characters, so they may appear as squares. This usually does not affect the final result in any way and can be ignored. Or you can install a more advanced version of a text editor, such as Notepad++.

1. Since our Unicode file is tab-delimited, and we want a comma-separated file, we need to replace all spaces with commas.

The note. If you don’t have specific requirements regarding the division of text in a file, you can skip the next step, as Excel handles tabs without any problems.

If you still need comma-separated documents, then continue with Notepad.

• Select a space, right-click the manipulator and select Copy, or just press Ctrl+C.
• Press Ctrl+H to open the “Replacements” window and copy the space in the “Find” field. Then put a comma in the “Replace” field and click “Replace All”.

In Notepad, the result should look like this:

1. Click “File”, “Save As”, give the file a name, select UTF-8 encoding. Click Save.
2. Find the file using Explorer and change its extension from .txt to .csv.

Alternatively, this can be done after finishing work in Notepad. When you save the file, in the file type, select “All files (*. *)”

1. Open a file from Excel from the File menu → “Open” → your file.

The note. If your file will be used outside of Excel with UTF-8 encoding, do not make any changes to the document. And you don’t need to save it as CSV, otherwise it will create encoding problems. If some information is not reflected in Excel, you need to correct everything through Notepad.

How to transfer Excel files to CSV with UTF-16 encoding

Transferring files with this encoding is much faster and easier than with UTF-8. This is because Excel automatically uses this encoding when saving Unicode (.txt) files.

Everything is simple here: save your file as usual, but choose Unicode as the file type. Everything is so simple and without extra steps. And then, just change the extension to .csv, and you’re done.

If you need comma separation in the text, then follow the same procedure as in the previous example with UTF-8.

Other conversion methods

The above methods are universal and work with any special characters in all versions of Excel.

However, there are several other ways to convert Excel files to CSV. But unlike the previous options, these methods will not give a ready-made UTF format. But all the necessary data and characters will be preserved, so that later it can be converted to UTF.

Google Sheets

Using these tables, you can easily transfer Excel files to CSV in five simple steps:

1. In Google Drive, click “Create Button” and “Table”.
2. From the File menu, choose Import.
3. Click “Download”.
4. Select “Replace Table”.

Tip: If you have a small Excel spreadsheet, you can simply copy and paste the spreadsheet.

1. “File” → “Download As” → choose (.csv). Save to your computer.

You can use any text editor to check all the information.. But, unfortunately, when saving documents in this way, the data may not be displayed correctly in Excel.

Save as “.xksx”, “.xls” and convert to “.csv”

Save the file as one of these extensions and then convert. However, this method does not work for all users, so you can try it. If it doesn’t work out, then it’s not a problem, but if it does work out, it will save a lot of time.

Save file with OpenOffice

With this program, you can export the files you need, because it provides all the necessary tools for this, in addition, the program is in the public domain.

Just open your document with this program, then go to “File” → “Save As” and specify CSV (.csv) as the save type.

Next, a window will appear with a choice of Character Sets and Field Separators. Select UTF-8 with comma separated characters and click OK.

You can also use any other application of this kind to perform a painless and easy file conversion to CSV.

Perhaps these are all the most common ways to convert Excel files to CSV.