In this article you will find 2 simple ways convert CSV file in Excel. You'll also learn how to import multiple CSV files into Excel and how to deal with situations where some of the data in a CSV file doesn't appear correctly in an Excel worksheet.
Recently we began to study the features of the CSV (Comma-Separated Values) format and various ways to convert excel file to csv. Today we'll do reverse process – CSV import in Excel.
This article will show you how to open CSV in Excel and how to import multiple CSV files at once. We will also identify possible pitfalls and offer the most effective solutions.
How to Convert CSV to Excel
If you need to pull some information into an Excel sheet from your company's database, then the first idea that comes to mind is to export the database to a CSV file and then import the CSV file into Excel.
There are 3 ways to convert CSV to Excel: You can open the file with the extension .csv directly in Excel, double click on the file in Windows Explorer or import CSV into Excel as an external data source. Next I will talk in detail about these three ways and I will indicate the advantages and disadvantages of each of them.
How to open a CSV file in Excel
Even if the CSV file was created in another program, you can always open it as an Excel workbook using the command Open(Open).
Comment: Opening a CSV file in Excel does not change the file format. In other words, the CSV file will not be converted to an Excel file (.xls or .xlsx format), it will retain its original type (.csv or .txt).
Comment: When Microsoft Excel opens a CSV file, it uses the default formatting settings to understand exactly how to import each column of data.
If the data corresponds to at least one of the following points, then use the Text Import Wizard:
To force Excel to run the Text Import Wizard, you can either change the file extension from .csv on .txt(before opening the file), or as described below.
How to open a CSV file using Windows Explorer
This is the most quick way open csv in excel. In Windows Explorer, double-click on the file .csv, and it will open like new Excel workbook.
However, this method will only work if Microsoft Excel is set as the default program for opening files .csv. If so, you will see a familiar icon in Windows Explorer next to the file name.
If Excel is not your default program, here's how you can fix it:
How to Import CSV to Excel
In this way you can import data from a file .csv to an existing or new Excel sheet. Unlike the previous two methods, it does not just open CSV in Excel, but converts the format .csv V .xlsx(if you are using Excel 2007, 2010 or 2013) or .xls(V Excel versions 2003 and earlier).
Before we move further, please take a look at the screenshot below, it shows the original CSV file and the desired output in Excel. I hope this helps you better understand why we choose certain settings in the following example.
Advice: If your CSV file uses more than one comma or other delimiter character in sequence, then check the box. Treat consecutive delimiters as one(Count consecutive delimiters as one) to avoid empty cells.
Advice: You can press the button Properties(Properties) to configure advanced settings such as refresh, layout, and formatting for imported data.
Comment: If your CSV file contains numeric data or dates, Excel may not convert it correctly. To change the format of the imported data, select the column(s) in which errors occurred, click on them right click mouse and context menu select Format cells(Cell Format).
Converting CSV to Excel: Problems and Solutions
The CSV format has been in use for over 30 years, but despite its long history, it has never been officially documented. The name CSV (Comma-Separated Values) comes from the use of commas to separate data fields. But this is in theory. In fact, many so-called CSV files use other characters to separate data, for example:
- Tabulation – TSV files (tab-separated values)
- Semicolon – SCSV files (semicolon separated values)
Some variations of CSV files separate data fields with single or double quotes, others require a Unicode byte sequence marker (BOM), such as UTF-8, to correctly interpret Unicode.
This lack of standards creates a variety of problems that you may encounter when trying to convert excel file to csv, and especially when importing a CSV file into Excel. Let's look at the known problems, starting with the most common.
CSV file does not display correctly in Excel
Signs: You are trying to open a CSV file in Excel and all the data ends up in the first column.
Cause: The root of the problem lies in the fact that in your regional and linguistic Windows settings and your CSV file has different delimiters set. In North America and some other countries, the default list field separator is a comma. While in European countries, a comma is used as a decimal separator, and the list field separator is a semicolon.
Solution: There are a few possible solutions this problem. You can quickly review the recommendations below and choose the most suitable ones for your specific task.
Comment: All solutions shown change the separator only for this file CSV. If you want to change the default separator once and for all, then the following solution will suit you.
- Changing separators in the regional settings. Click the button Start(Start) and run Control Panel(Control Panel), click the item Region and Language(Regional Standards) > Additional Settings(Extra options). A dialog box will open Customize Format(Format Setting) in which you need to select a dot (.) for the parameter Decimal symbol(Integer and fractional separator), and set a comma (,) for the parameter List separator(List element separator).
Translator's note: These settings are provided for the English localization of Excel (and a number of other countries). For Russian localization, it will be more common to use a comma as a separator between integer and fractional parts and a semicolon to separate list elements.
Comment: Installation in Panel Windows management Setting decimal separators and list items will change the default character settings for all programs on your computer, not just Microsoft Excel.
Leading zeros are lost when opening a CSV file in Excel
Signs: Your CSV file contains values with leading zeros, and those zeros are lost when you open the CSV file in Excel.
Cause: By default, Microsoft Excel displays the CSV file in the format General(General), in which the leading zeros are cut off.
Solution: Instead of opening the .csv file in Excel, run the Text Import Wizard as we did earlier to convert the CSV file to Excel.
In step 3 of the wizard, select the columns that contain values with leading zeros and change the format of these columns to text. This way you convert your CSV file to Excel, keeping the zeros in their place.
Excel converts some values to dates when opening a CSV file
Signs: Some values in your CSV file are similar to dates, and Excel automatically converts such values from text format to date format.
Cause: As mentioned above, Excel opens a CSV file in the format General(General), which converts date-like values from text format to date format. For example, if you open a CSV file containing user logins, the entry “Apr23” will be converted to a date.
Solution: Convert your CSV file to Excel using the Text Import Wizard. In step 3 of the wizard, select columns with date-like entries and change the column format to text.
If you need to achieve the opposite result, that is, convert values in a certain column to dates, then set the format Date(Date) and select the appropriate date format from the drop-down list.
How to Import Multiple CSV Files into Excel
I think you know that Microsoft Excel allows you to open multiple CSV files using the command Open(Open).
This method is simple and fast, and we could call it excellent, if not for one circumstance - each CSV file is opened in this way as a separate Excel workbook. In practice, switching back and forth between several open files Excel can be extremely inconvenient and cumbersome.
I hope you can now easily convert any CSV file to Excel. If you have any questions or difficulties, feel free to write to me in the comments. And thank you for your patience to everyone who managed to read this long article to the very end!
Text format documents CSV used by many computer programs to exchange data between each other. It would seem that in Excel you can launch such a file with a standard double-click on it with the left mouse button, but in this case the data is not always displayed correctly. However, there is another way to view the information contained in the file CSV. Let's find out how this can be done.
Format name CSV is an abbreviation of the name "Comma-Separated Values", which translates into Russian as “values separated by commas.” Indeed, in these files commas are used as delimiters, although in the Russian-language versions, unlike the English-language ones, it is still customary to use a semicolon.
When importing files CSV In Excel, the problem of reproducing the encoding is relevant. Often, documents containing Cyrillic alphabet are launched with text replete with “crazy words”, that is, unreadable characters. Moreover, quite common problem is a matter of delimiter mismatch. First of all, this applies to those situations when we try to open a document made in some English-language program using Excel, localized for a Russian-speaking user. After all, in the source code the separator is a comma, and Russian-language Excel perceives a semicolon as this. Therefore, the result is again incorrect. We will tell you how to solve these problems when opening files.
Method 1: Normally opening a file
But first we will focus on the option when the document CSV created in a Russian-language program and is ready to open in Excel without additional manipulation of the content.
If Excel program is already installed to open documents CSV on your computer by default, then in this case just click on the file double click left mouse button and it will open in Excel. If the connection has not yet been established, then in this case you need to perform a number of additional manipulations.
After this, the contents of the document CSV will be opened in Excel. But this the method will work only if there are no problems with localization or display of the Cyrillic alphabet. In addition, as we can see, you will have to do some editing of the document: since the information does not in all cases fit into the current size of the cells, they need to be expanded.
Method 2: Using the Text Wizard
You can import data from a CSV document using a built-in Excel tool called Text Master.
Method 3: Open through the "File" tab
There is also a way to open a document CSV via tab "File" Excel programs.
As you can see, despite some problems with opening documents of the format CSV in Excel, you can still solve them. To do this, you need to use a built-in Excel tool called Text Master. Although, for many cases it is quite enough to use standard method Open a file by double-clicking the left mouse button on its name.
CSV format stores text data that is separated by commas or semicolons. VCARD is a business card file and has the extension VCF. It is commonly used to forward contacts between phone users. A CSV file is obtained when exporting information from memory mobile device. In light of the above, converting CSV to VCARD is an urgent task.
Method 1: CSV to VCARD
CSV to VCARD is a one-window application that was created specifically for CSV conversion in VCARD.
Method 2: Microsoft Outlook
Microsoft Outlook is popular mail client, which supports CSV and VCARD formats.
- Open Outlook and go to the menu "File". Here we click on "Open and Export", and then on "Import and Export".
- As a result, a window opens "Master of Import and Export", in which we select the item "Import from another program or file" and click "Further".
- In field "Select a file type to import" indicate the required item "Comma Separated Values" and click "Further".
- Then click on the button "Review" to open the original CSV file.
- As a result, it opens "Conductor", in which we move to the desired directory, select the object and click "OK".
- The file is added to the import window, where the path to it is displayed in a certain line. Here you still need to define the rules for working with duplicate contacts. There are only three options available when a similar contact is detected. In the first it will be replaced, in the second a copy will be created, and in the third it will be ignored. Leave the recommended value "Allow creation of duplicates" and click "Further".
- Select a folder "Contacts" in Outlook, where the imported data should be saved, then click on "Further".
- It is also possible to set the fields to match by clicking the button of the same name. This will help avoid data inconsistencies when importing. Confirm the import by checking the box "Import…" and press "Ready".
- The source file is imported into the application. In order to see all contacts, you need to click on the people icon at the bottom of the interface.
- Unfortunately, Outlook only allows you to save one contact at a time in vCard format. At the same time, you also need to remember that by default the contact that is previously selected is saved. After that we go to the menu "File", where we click "Save as".
- The browser starts, in which we move to the desired directory, if necessary, enter a new name for the business card and click "Save".
- This completes the conversion process. The converted file can be accessed using "Conductor" Windows.
Thus, we can conclude that both programs considered cope with the task of converting CSV to VCARD. At the same time, the most convenient procedure is implemented in CSV to VCARD, the interface of which is simple and intuitive, despite English language. Microsoft Outlook provides broader functionality for processing and importing CSV files, but saving to the VCARD format is carried out only for one contact.
Convert csv and txt format files to xls format can be done in two ways:
Method 1
This method is suitable for both users Microsoft programs Excel and for those who use Openoffice.
You can open a text or csv file as a Microsoft Excel workbook using the Open command. When you open a text file, its format does not change. If you open a csv file, Excel application will open it and display the data in a new workbook.
In Excel, click the button Open- a dialog box will appear Open.
For operating system Windows Vista select Text files. Next, find in address bar necessary text file and double click on it.
For Microsoft Windows XP select from the list File type - Text files. On the list Folder Find the text file you want and double click on it.
An example of what it looks like Openoffice
A window opens Importing text
After setting all the values, click Ok, and a table appears that you need to save. Come in File - Save As- and in the dialog box, select Microsoft format Excel 97/2000/XP (.xls) and the file is saved in xls format.
Method 2.
Suitable for Microsoft users Excel
You can import data from a text file into an existing worksheet by opening external data. Select a cell in the table to import external data. Next on the tab Data in Group External data click the button From the text.
For operating room Windows systems Vista, in the address bar, find the file you need to import and double-click on it.
For Microsoft Windows XP listed Folder Find the file you need to import and double-click on it. A dialog window will open Importing text, in which you can change the encoding (usually set to Unicode (UTF-8)), you can also select the delimiter that separates the values in the csv file.
1)
2)
In this article, you will find 2 simple methods to convert a CSV file to Excel. In addition, you will determine how to import a couple of CSV files into Excel and how to deal with situations when some of the data from the CSV file does not display correctly on the Excel page.
Relatively not so long ago, we began to study the features of the CSV format (Comma-Separated Values - values divided by a comma) and different methods for converting an Excel file to CSV. Now we will do the reverse process - importing CSV into Excel.
This article will show you how to open CSV in Excel and how to import a couple of CSV files in one go. We will also identify likely pitfalls and offer the most effective answers.
How to Convert CSV to Excel
In case you need to extract some data to Excel page from your company's database, the first thing that comes to mind is to export the database to a CSV file, and then import the CSV file into Excel.
There are 3 methods for converting CSV to Excel: You have the option of opening the .csv file specifically in Excel, double-clicking on the file in Windows Explorer, or importing the CSV into Excel as an external data source. Then I will talk in detail about these three methods and indicate the advantages and disadvantages of each of them.
How to open a CSV file in Excel
In addition, if the CSV file was created in a second program, you can always open it as an Excel workbook using the Open command.
Note: Opening a CSV file in Excel does not change the file format. In other words, the CSV file will not be converted to an Excel file (.xls or .xlsx format), it will retain its original type (.csv or .txt).
- Launch Microsoft Excel, on the Home tab, click Open.
- The Open dialog box will appear, select Text Files from the drop-down list in the lower right corner.
If you open a CSV file, Excel will open it immediately, placing the data in a new Excel workbook. If you open a text file (.txt), Excel will launch the Text Import Wizard. For more information about this, see the section Importing CSV into Excel.
Note: When Microsoft Excel opens a CSV file, it applies default formatting settings to understand how to import any column of data.
If these correspond to at least one of the following points, then use the Text Import Wizard:
- The CSV file uses different delimiters;
- The CSV file uses different date formats;
- You are converting these, among which there are numbers with a leading zero, and you want to preserve this zero;
- You want to preview how these will be imported from a CSV file into Excel;
- You want more flexibility at work.
To force Excel to run the Text Import Wizard, you have the option of either changing the file extension from .csv to .txt (before opening the file), or importing the CSV into Excel as will be outlined later.
How to open a CSV file using Windows Explorer
This is the fastest method to open CSV in Excel. In Windows Explorer, double-click the .csv file. and it will open as a new Excel workbook.
But, this method will only work if Microsoft Excel is set as the default program for opening .csv files. If this is the case, then you will see the familiar icon in Windows Explorer next to the file name.
In case Excel is not the default program, here's how you can fix it:
- Right-click on any .csv file in Windows Explorer and in the context menu that opens, click Open with Choose default program.
- Select Excel from the list of recommended programs, make sure that Always use the selected program to open this kind of file is checked, and click OK.
How to Import CSV to Excel
With this method you have the opportunity to import these from a .csv file into an existing one or into new page Excel. Unlike the previous two methods, it does not just open CSV in Excel, in particular it converts the .csv format into .xlsx (if you are using Excel 2007, 2010 or 2013) or .xls (assuming Excel 2003 and earlier).
- Open the required Excel sheet and click on the cell where you need to import these from the .csv or .txt file.
- On the Data tab, in the Get External Data section, click From Text.
Before we move further, please take a look at the screenshot below, it shows the original CSV file and the desired output in Excel. I hope this will help you better understand why we choose certain settings in the following example.
- Step 1. Select the data format and line number from which you want to start importing. It is much more common to select Delimited and from line 1. The preview area at the bottom of the wizard dialog box shows the first couple of records of the imported CSV file.
Text qualifier is a character that encloses individual values. The entire text between such characters, for example, text1, text2, will be imported as one value, even if this text contains the character you specified as a delimiter.
In this example, we specified a comma as a delimiter, and quotes as a line delimiter. As a result, all numbers with a decimal separator (which is also helped in our case by a comma!) will be imported into one cell, as can be seen in the preview area in the figure below. If we do not specify quotes as a line delimiter, then all numbers will be imported into different cells.
Tip: If your CSV file uses more than one comma or other delimiter character in succession, then check the Treat consecutive delimiters as one option to avoid the appearance of unoccupied cells.
- Choose where to insert the imported data, on an existing or on a new page, and click OK. to finish importing the CSV file into Excel.
Tip: You can click the Properties button to configure additional settings such as updating, formatting, and layout for the imported data.
Note: If your CSV file contains
These are numerical dates, Excel may convert them with inaccuracies. To change the format of the imported data, select the column(s) in which inaccuracies appeared, right-click on them and select Format cells from the context menu.
Converting CSV to Excel: Solutions and Problems
The CSV format has been in use for over 30 years, but despite its long history, it has never been officially documented. The name CSV (Comma-Separated Values) comes from the use of commas to separate data fields. But this is in theory. In fact, many so-called CSV files use other characters to separate data, for example:
- Tabulation – TSV files (tab-separated values)
- Semicolon – SCSV files (semicolon separated values)
Some variations of CSV files separate data fields with single or double quotes, while others require a Unicode byte sequence marker (BOM), such as UTF-8, to correctly interpret Unicode.
This lack of standards gives rise to a variety of problems that you may encounter when trying to convert an Excel file to CSV. and especially when importing a CSV file into Excel. Let's look at the known problems, starting with the most common.
CSV file does not display correctly in Excel
Metrics: You try to open a CSV file in Excel and all the information ends up in the original column.
Circumstance: The root of the problem lies in the fact that different delimiters are set in your Windows regional and language settings and in your CSV file. In North America and some other countries, the default list field separator is a comma. Whereas in European countries, a comma is used as a decimal separator, and a semicolon is used as a separator for list fields.
Answer: There are a couple of possible answers to this problem. You can quickly review the tips below and choose the ones most suitable for your specific task.
- Specify the correct delimiter specifically in the CSV file. Open the CSV file in any word processor(a simple notepad will also work) and put the following text in the first line. Note that this must be a separate line before any second allowed ones:
- To set the comma delimiter: sep=,
- To set the semicolon delimiter: sep=;
As you may have already guessed, it is possible to set each character as a separator, easily specifying it immediately after the equal symbol.
When the Column Text Distribution Wizard starts, in the first step, select the Delimited data format and click Next. In the second step, select the desired separator and click Finish.
Note: All the answers shown only change the delimiter for a given CSV file. If you want to change the default separator once and for all, then the following answer will suit you.
- Changing separators in the regional settings. Click the Start button and launch Control Panel, click Region and Language Additional Settings. The Customize Format dialog box will open, in which you need to select a period (.) for the Decimal symbol parameter, and set a comma (,) for the List separator parameter.
Translator's note: These settings are for the UK localization of Excel (and sequences of other countries). For Russian localization, it will be more common to use a comma as a separator between integer and fractional parts and a semicolon to separate list elements.
Click OK twice. to close dialog boxes- all is ready! From now on, Microsoft Excel will open and display all CSV (comma delimited) files correctly.
Note: Setting the decimal and enumeration separators in the Windows Control Panel will change the default character settings for all programs on your computer, not just Microsoft Excel.
Leading zeros are lost when opening a CSV file in Excel
Indicators: Your CSV file contains values with leading zeros, and those zeros are lost when you open the CSV file in Excel.
Circumstance: By default, Microsoft Excel displays a CSV file in General format, in which leading zeros are trimmed.
Answer: Instead of opening the .csv file in Excel, run the Text Import Wizard as we did earlier to convert the CSV file to Excel.
In step 3 of the wizard, select the columns that contain values with leading zeros and change the format of these columns to text. This way you convert your CSV file to Excel, keeping the zeros in their own places.
Excel converts some values to dates when opening a CSV file
Measures: Some values in your CSV file look like dates, and Excel automatically converts such values from text format to date format.
Circumstance: As mentioned above, Excel opens the CSV file in General format and also converts date-like values from text format to date format. For example, if you open a CSV file containing user logins, the entry Apr23 will be converted to a date.
Answer: Convert the CSV file to Excel using the Text Import Wizard. In step 3 of the wizard, select columns with date-like entries and change the column format to text.
If you need to achieve the opposite result, in other words, convert values in a certain column to dates, then set the Date format and select the appropriate date format from the drop-down list.
How to import a couple of CSV files into Excel
I think you understand that Microsoft Excel allows you to open a couple of CSV files using the Open command.
- On the File tab, click Open and select Text Files from the drop-down list at the bottom right of the dialog box.
- To select a pair of files in a row, click on the first file, then press and hold the Shift key. click on the last file. Both of these files, and everything in between, will be selected.
To select non-consecutive files, hold down the Ctrl key and click on each .csv file. whatever you want to open.
This method is simple and fast, and we could call it good if not for one event - any CSV file is opened as a separate Excel workbook. In practice, switching back and forth between multiple open Excel files can be very inconvenient and cumbersome.
Hopefully now you can easily convert any CSV file to Excel. If you have any questions or difficulties, feel free to write to me in the comments. And I thank everyone for their patience who managed to read this long article to the very finish.)
The lesson was prepared for you by the team of the site office-guru.ru
Loading...
