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'll do reverse process– CSV import 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 CSV conversions in Excel: You have the opportunity to open a file with the .csv extension specifically in Excel, double-click on the file in Windows Explorer or import 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:
- Click right click mouse over any .csv file in Windows Explorer and in the opened context menu 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, 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 trouble lies in the fact that in your regional and language 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. 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 demonstrated answers change the separator only for this file CSV. 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: Installation in Panel Windows management Selecting decimal separators and list elements 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 several open files Excel 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
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.
Total CSV Converter- excellent CSV to CSV converter DOC formats, PDF, HTML, TXT, XLS, DBF, XML, including in batch mode. Allows you to easily convert to required format even CSV files larger than 1 GB, while excluding unnecessary rows and columns from the file if necessary.
System requirements:
Windows 2000 / 2003 / XP / Vista / 7 / 8 / 10 (32-bit & 64-bit)
Torrent CSV File Converter - CoolUtils Total CSV Converter 3.1.1.179 RePack by vovava details:
Total CSV Converter will help you convert CSV files to more acceptable formats ( Microsoft Excel or OpenOffice). Converts CSV to DOC, PDF, HTML, TXT, XLS, DBF, XML and OpenOffice formats. The program supports the following types of file conversion: CSV to DOC (Word). CSV to PDF. CSV to HTML. CSV to XML. CSV in OpenOffice. CSV to TXT. CSV to XLS. CSV to DBF. CSV to CSV. Converting CSV to CSV is useful for changing delimiters, date and time format. The program can process selected files in batch mode and can search for files in selected folders. The program interface is convenient and very simple for the user. Total CSV Converter can be controlled via command line, the corresponding version (command line) is included in the standard distribution and does not need to be downloaded separately.
Features of RePack"a:
Type: Installation.
Languages: Russian | English.
Cut: Other languages
Activation: Conducted (Key).
Command line switch:
Silent Installation:/S
Screenshots CSV file converter - CoolUtils Total CSV Converter 3.1.1.179 RePack by vovava torrent:
In this article you will find 2 simple ways convert csv file to 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 tackle the reverse process - importing CSV into 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 into Excel, double-click the file in Windows Explorer, or import the 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 as a 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, right-click on them and select from the context menu 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, while 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 your Windows regional and language settings and your CSV file have 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 delimiter for a given CSV file only. 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: Setting decimal characters and list items 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
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 Excel files can be extremely inconvenient and burdensome.
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!
There are two ways to import data from text file in Excel: You can open it in Excel or import it as an external data range. To export data from Excel to a text file, use the command Save as and change the file type from the drop-down menu.
The two most commonly used text file formats are:
delimited text (TXT) files, which typically use a tab character (ASCII character code 009) to separate fields of text;
Comma-separated value (CSV) text files, which typically use a comma (,) as the text field separator.
You can change the separator character used in TXT and CSV files. This may be required to ensure successful implementation import and export.
Note: You can import and export up to 1,048,576 rows and 16,384 columns.
Import a text file by opening it in Excel
A text file created in another program can be opened as an Excel workbook using the command Open. When you open a text file in Excel, its format does not change - this can be seen in the title of the Excel window, where the name is indicated with the extension of the text file (for example, TXT or CSV).
Go to file > Open and navigate to the location containing the text file.
Select text files in the File Type drop-down list in the dialog box Opening .
Find required file and double click it.
Import a text file by connecting to it (Power Query)
On the "tab" data" in Group " Receiving & Converting Data" select from text or CSV.
In the dialog box Import data locate and double-click the text file you want to import and click the button Import.
There are several options in the Preview dialog box:
If Excel doesn't convert a certain column of data to the format you want, you can convert it after importing. additional information see articles and.
Save as.
Import a text file by connecting to it
You can import data from a text file into an existing sheet.
If Excel doesn't convert the column to the required format, it can do so after import. For more information, see Convert numbers from text to numeric format and Convert dates from text to date format.
Export data to a text file by saving it
Excel sheet can be converted to a text file using the command Save as.
-
A dialog box will open Saving a document.
In field File type Select a text file format for the sheet.
-
A dialog box appears reminding you that only the current worksheet will be saved as a text file. If you want to save exactly the current sheet, click the button OK. Other sheets can be saved as separate text files by repeating this procedure for each of them.
A second dialog box appears reminding you that the worksheet you are saving may contain features that are not supported by text file formats. If you want to save only the sheet data in the new text file, click Yes. If you are unsure and want more information about what Excel features are not supported by text file formats, click Reference.
For more information about saving files in other file formats, see Save a workbook in another file format.
Changing the delimiter used in a text file
If you use the Text Import Wizard to import a text file, you can change the delimiter from the tab character to another character in the second step. This step can also change how consecutive delimiters (such as consecutive quotation marks) are handled.
For more information about delimiters and advanced options, see the Text Import Wizard.
Changing the delimiter in all CSV text files
Click the button Start and select Control Panel.
Open the Change Regional and Language Settings dialog box.
Enter a new separator in the field List element separator.
Press the button twice OK.
Note: After changing the list item separator, all programs on your computer will use new symbol. You can return the standard character by following the same steps.
Loading...
