Contents
This time we will try to learn simple, fast, and most importantly effective methods. transformations information from tables Excel to CSV, while maintaining the invariance of all special inserts and signs of foreign languages. The following methods are compatible with all versions of Excel.
How to convert Excel document to CSV
Let’s imagine a situation. We need to use the data that is stored in the Excel spreadsheet using some other program, for example, Outlook, a program with the functions of an email client, or Access, a database program. Under such circumstances, you will first need to change the Excel to CSV format, then import the .csv file into the required program. The following are step-by-step instructions for using the Save As operation.
1) Go to the tab “File“And click”Save as“. Knowing which keys or key combinations to press will save you a lot of time while working in a particular program. Therefore, to open the same dialog box (Save As), you can simply press the key F12.
2) In line File Type we need to choose CSV (separators are commas).
Along with the file type, the program presents a choice of other options:
Option 1 | CSV (separated by commas). Here the document is saved as text with commas, in the future it is possible to work with it in another Windows program (or another version Windows OS). |
Option 2 | CSV (Macintosh). Similar to the previous version, but intended for use in Mac OS. |
Option 3 | CSV (MS-DOS). Required for use in MS-DOS OS. |
| Option 4 | Text Unicode (* TXT). It is an industry standard for computers and is supported by almost all modern operating systems. It can process the signs of almost all modern languages, as well as ancient ones. Accordingly, if we have letters of foreign languages or some other special characters, for starters, the document is saved in Unicode, then converted to CSV. The execution process is shown above. |
Note: With all of the above formats, only active sheet.
3) Select folder destination, Further – “Save».
After, open two dialogue window. No need to worry, there is nothing wrong, everything is fine.
4) The first message reminds you that only one active sheet in csv. Must click OKif that is what is required.
If you want to save the entire contents of the worksheet, click “cancellation“, then separately save each sheet. Next, you need to save each individual file to CSV.
5) If click OK while opening the first window, a second window will appear informing you that your spreadsheet may contain functions that do not support CSV encoding. Okay, just click yes.
That’s it. Quick и easily, you are unlikely to encounter any obstacles along the way.
Export using CSV encoding UTF-8 or UTF-16
This may seem difficult at first. Some encodings, incomprehensible names – but in fact everything is simple. Let’s sort it out in order. If your document has special inserts, signs of foreign languages or hieroglyphs, hold conversion the above method will not work.
The reason is that the operationSave as CSV» mangles any characters except ASCII (American Standard Code for Information Interchange). If there are “smart quotes” or long dashes in the document (as an option, inherited from the original Word document), they are distorted.
Exist alternative – save the excel file to Unicode (.txt) and further conversion to CSV. Then it will be possible to leave all the original signs unchanged.
Before proceeding, let’s briefly understand the difference between UTF-8 и UTF-16so that you have the opportunity to choose the correct format in a particular case:
- UTF-8 encrypts the most compactly, since 1 to 4 bytes are used for each character. In general, it is recommended if the signs ASCII make up the bulk of the document. Another plus is that a UTF-8 document has the same encoding as an ASCII document.
- UTF-16 2 to 4 bytes are used for each character. However, more memory is not always required. For example, Japanese characters in this variant require 3 to 4 bytes, while UTF-8 requires 2 to 4 bytes. Accordingly, UTF-16 is well suited when using Asian characters, including Japanese, Chinese, or Korean. However, it is not fully compatible with ASCII and needs software compatible with Unicode. Take note!
Convert Excel to CSV UTF-8
Let’s say you have an Excel workbook with several foreign characters. Let’s look at an example Japanese:
That leave all signs in initial state, you need to proceed as follows:
1) First you need to select “File“,”Save as».
2) Next you need to name the file and select the text Unicode (* .txt), then click on “Save».
3) Then you should open file in a text editor Unicode TXT.
Note. Some simple text editors do not support all Unicode characters, so some characters will appear in blocks. Most often this does not affect anything, so you can simply ignore it or use a more complex program, for example Notepad++.
4) Since next we need to change the file in CSV (separators – commas), you need to put down these commas.
Note. If you need commas not required, you can simply skip the next step.
Follow the sequence of actions:
- Выберите tab character, click on the right mouse button and select “Copy“. In this case, there is also a well-known key combination for quickly executing a command. Clamp CTRL + CLOSE.
- Press CTRL+H, the “Replace” window will appear, then paste what was copied earlier (combination CTRL + DRAW), in the “Find” line. Put a comma in the “Replace with” line. At the end click “Replace all».
В Notepad received file should look like this:
5) Next, you need to click “File” and “Save as”, name the file and change the encoding to UTF-8. At the end, click Save.
6) Using the explorer, change the extension from .txt on . Csv.
There is another replacement option, in the window “Save as CSV“. Need to choose “All files” (*. *), see screenshot below:
7) Next, check the result. To do this, open the CSV file in Excel, click “File”, “Open”, “Text files (.prn, .txt, .csv)”to make sure the information is correct.
Note. If you plan to use the file in other programs, to avoid problems with encoding, do not change anything in the tables or save the file directly in Excel. If not everything is displayed, corrections must be made in the program itself, and then again save to UTF-8.
Convert Excel to CSV UTF-16
This method is easier and faster, since when saving a file in Unicode (.txt), it will automatically use UTF-16.
And all you have to do is click “File“,”Save As in Excel”, then select the format Unicode (* .txt) and change the Windows Explorer file extension to CSV. Made!
When you need to CSVcommas were written in the file – using a text editor, you need to replace the spaces with commas (See step 6 above for more information.
Other ways to convert Excel to CSV
The methods just described are universal. They work for all special signs and in any versions.
There are also several other ways, which also need can be used.
Convert via Google Sheets
This is very a simple solution. Let’s assume you have already installed Google Drive, then you need to follow only five simple steps.
1) So, click “Create in Google Drive“, then you need to select tables.
2) Next, select “Import” on the menu “File”.
3) Then you need to click “Download”, then you will need to select on your computer the required download file.
4) After that, select “Replace Spreadsheet“, click”Import».
Recommendation. To save time, you can simply copy the data to Google spreadsheet. But this is only suitable if you have a small document.
5) Next, go to “File”, “Download As”, select values separated by commas, then save the file CSV to your computer.
Finally, the check out the resulting document in some editorto make sure everything is saved properly. Unfortunately, this technique does not display everything correctly in all cases.
Save .xlsx to .xls, then convert to .csv
This methodology hardly needs additional explanation, since the title of the subtitle speaks for itself.
This solution was suggested on the forum. Many users have reported that special characters lost when directly saving .xlsx to .csv can be saved if you save the file first .xlsx в .xls and then save .xls as . Csv. Of course, you can try this method yourself, and if everything goes well, it can save you time.
Save Excel as CSV with OpenOffice
The sequence of actions when using the program OpenOffice presented below. First, using OpenOffice Calc, open an Excel document, select “File”, “Save as”. Then save as CSV (.scv).
You will then have to choose between different character sets and field separators. Of course you should choose Unicode (UTF-8) and a comma, if as a result we need to come to CVS UTF-8 (or other required encoding and punctuation character), click OK. As a rule, quotes (“)” are left by default.
Alternatively, another application of the same type can be used − LibreOffice — for quick and hassle-free conversion from Excel to CSV.