Convert CSV to Excel as a table

The contents of databases are usually extracted as a .csv file. However, it’s just a text file, not very readable. To work with the contents of the database, it is necessary to present it in a different format – often Excel sheets are most convenient. About what methods exist to do this, which one is better, and what errors there are when transferring data, will be discussed in this article.

How to Convert CSV to Excel Format

There are various ways in which a CSV document downloaded from a database can be transferred to Excel. We will discuss three of them:

1. Direct opening in Excel.
2. Opening via Windows Explorer.
3. Import document with format change.

Opening a CSV document in Excel

Excel can open .csv documents directly without conversion. The format does not change after opening in this way, the .csv extension is saved – although the extension can be changed after editing.

1. Launch Excel, click on “File“, Then”Open«.
2. In the window that appears, select the option “Text filesfrom the expanded list.

3. Find the document you need and double-click on it.

.csv documents open in Excel immediately, without any additional manipulations. But .txt files will require conversion – a window will appear “Text Import Wizards«.

There are cases when it is better not to open the document directly, but to call Master. This approach is justified in the following cases:

• the separating character used in the document is non-standard, or there are several varieties of them;
• the document contains dates in different formats;
• you are converting numbers that start with zeros and want to keep them that way;
• before you transfer data, you want to see how the end result will look like;
• you generally want more portability.

Master will start if you change the document extension to .txt. You can also start importing a file differently, which will be discussed later, after describing the second method of opening.

If you make changes to the document after you open it, Excel will prompt you to save it as .xls (or .xlsx) because otherwise some of the formatting will be lost. Then there will always be the opportunity to change the format back, however, another part of the content may be lost – zeros may disappear at the beginning of the numbers, some of the records may change their appearance.

Opening a CSV document through Windows Explorer

This path is not fundamentally different from the previous one. To open a document, just double-click on it in Windows Explorer.

Before opening, make sure that next to the document name there is an icon for the Excel program, and not some other one – this means that Excel is selected as the program that should open such files. Otherwise, some other program will open. You can change it to Excel like this:

1. Call the context menu by right-clicking on any .csv file and apply Open With… > Select Program.
2. Выберите Excel (desktop) of Recommended Programs, designate it as a program that should always be used for such files (check the box below), and close the window by pressing OK.

Import CSV to Excel

It is also possible to turn the opened document into an Excel workbook. The format will change to .xls for earlier versions of Excel (2000, 2003) and .xlsx for all others. All content will be displayed on one sheet.

1. Click on the cell in the sheet where the import should start. This is usually the very first cell in the table, A1. Starting from it, as many rows as there are in the opened file, and as many columns as there are values ​​in each column will be filled.
2. In the tab “Data” in a group “Getting External Data” select “From text”.

3. In the window that appears, find the required document and double-click on it (you can also use the button Import at the bottom of the window).

4. Next, you need to follow the step-by-step instructions of the opened Text Import Wizards.

The image below shows the original document and the expected result. In order for everything to look exactly like this after import, you need to apply various settings, which will be discussed later.

Step 1. The wizard will prompt you to select a file type – as you might guess, it will be “with separators” (in English – delimited), and the line from which the content transfer will begin – most likely, you need to start with terms 1if you don’t want to transfer only part of the content. The window below will show the first lines from the selected document.

Step 2. Now you need to understand which one is used separator (there may be more than one) in the file, and specify this character in Masters. It has options to select from standard delimiters, but if an atypical character is used in the document, you can choose Other and enter the desired character. In the file used for the demonstration, the delimiters are − Comma и Tab. Commas separate cells with product characteristics, such as serial number and number of copies sold, and tabs separate one product from another – information about each must begin on a new line.

It is also necessary to define and specify text delimiter. This is a character placed before and after each piece of text that must be located in one cell. Thanks to the delimiter, each such segment is perceived as a separate value, even if inside it there are characters chosen to separate the values. In our document, each value is framed with quotes – therefore, even if it contains a comma (for example, “beginning, then continue”), all its text will be placed in one cell, and not in two consecutive ones.

Step 3. Here it is enough to look at the preview and, if it does not show any unacceptable flaws, click Finish. It may happen that some of the values ​​will be separated not by one separator, but by several, as a result, cells without values ​​will appear between them. To prevent this from happening, select the checkbox Treat consecutive delimiters as one.

5. Select the destination path (it can be a new sheet or an existing sheet) and click OKto complete the import process.

You can also click Materials — there are other possibilities. For example, you can format content, customize markup, and how information is updated.

Sometimes the result of the conversion is far from expected. How this result can be changed will be discussed in the next section of the article.

Problems during conversion and their solutions

For all the time that the CSV format has existed, no one has bothered to officially document it. Therefore, although it is assumed that commas should be used to separate values, in fact, different databases use different separators – semicolons, tabs, and others.

Text delimiters can also vary – most often they are either quotation marks or a byte order mark. There may not be any delimiters at all – then the character used as a separator is always perceived as such (then it is usually not a comma – it is used too often in the text – but a different, less common character).

Non-standard files may not open correctly – in order for them to be displayed as they should, you have to change the settings of the computer or the opening program. Let’s see what difficulties there are and what solutions exist for them.

The file does not open correctly

Evidence. The entire content of the document is placed in the first column.

Cause. The document uses a character as a delimiter that is not designated as such in the computer settings, or is reserved for a different function. For example, a comma may be reserved to separate decimal parts of a number, and therefore cannot separate values ​​in a file.

Solutions. There are different ways to solve this problem:

1. Change the separating character in the document itself. Open it in Notepad or a similar editor, and in the initial line (empty, all data should be in the lines below), enter the following text:

• to change the separator to a comma: sep
• to change to a semicolon: sep=;

Another character written after sep = in the initial line, will also become a delimiter.

2. The separator character used in the file can also be specified in Excel itself. In versions 2016, 2013 or 2010, for this you need to open the tab Data and choose “Text by columns” in a group “Working with data”.

This will open the window “Wizards for distributing text into columns”. There, from the proposed data formats, you need to choose those with separators. Then you need to press Next and, after choosing a delimiter, Finish.

3. To automatically open a document with Import Wizard, and not just in an Excel sheet, the extension can be changed from .csv to .txt. AT Masters it is possible to specify any character as a separator – how to do this, the article explained earlier.
4. Use VBA. You can use, for example, – it is suitable for Excel 2000 or 2003. The code can be changed so that it is suitable for other versions.

The solutions presented above are designed for individual documents, the settings of which differ from the usual ones. These sequences of actions will need to be applied for each file that does not open correctly. If most of the documents do not open correctly, perhaps the best solution is to change the settings of the computer – this is discussed in the fifth solution.

5. Change separator and decimal point in computer settings

В Control Panel, called by the button Start, select “Extra options” from the list “Regional Standards”. After clicking, a window will appearFormat setting” – in it you can select “List Separator” and the separator of the integer and fractional parts of the number. If the files require a comma as the delimiter, first set the period as the decimal point. It may turn out to be the other way around – you need a semicolon as a separator character. Then for fractions, you can leave any sign, this will not cause a conflict.

When all settings are completed, click OK on both of the two open windows – they will close and the changes will be saved. Keep in mind that they now work for all programs on the computer.

Eliminate leading zeros

Sign. Some of the values ​​in the source document are numbers that start with zeros that are not separated by a fraction sign (for example, ciphers and codes with a fixed number of digits, logins and passwords, meter and instrument readings). In Excel, the zeros at the beginning of such numbers disappear. If you edit the file and then save it as an Excel workbook, it will no longer be possible to find in this workbook where those numbers with zeros were.

Cause. Excel has separate formats for text and numbers. In text files, there is no such separation, and therefore Excel assigns the General format to all values. It means that text is displayed as text, and numbers without alphabetic characters are displayed as a number that cannot start with zeros.

Solution. Change the extension to .txt to enable the Import Wizard when you open the document. When you get to Step 3, change the format of columns with numbers that start with zeros to text.

Some of the values ​​look like dates

Sign. Dates display values ​​that are originally plain text or numbers.

Cause. The general format involves turning values ​​into dates that resemble those for Excel. If the CSV document contains a single value like may12, then in the Excel sheet it will be displayed as a date.

Solution. Similar to that in the previous case. Change the extension to .txt, in Masters change the format of values ​​converted to dates to text.

If, on the contrary, you want to display the contents of a certain column as dates, set the format for it date. There are several types of date format, so select the one you need from the list.

How to import multiple CSV files into Excel

Excel can open multiple CSV files at once.

1. Press File > Open and select the option Text files from the drop down list below.
2. To select multiple files side by side, first select the first one, then click Shift and click on the last one. In addition to the selected files, all files in between will be selected.
3. Click Open.

The disadvantage of this method is that each selected file will be opened separately in Excel. Moving from one document to another can increase time costs. However, it is then possible to copy the contents of all of them into sheets in the same workbook.

The explanation was long, however, now you will be able to open any CSV file in Excel without much difficulty. If something seems incomprehensible at first glance, just follow the steps indicated and everything will become clear.