In this article you will find 2 simple ways convert CSV file to Excel. In addition, you will learn how to import several CSV files and how to deal with situations where a piece of data from a CSV file does not display correctly in an Excel sheet.
Recently, we started to study the features of the CSV (Comma-Separated Values) format and various ways to convert Excel file to CSV. Today we are going to do the 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 the same time. 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, 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 csv conversions in Excel: You can open a file with the extension .csv directly in Excel, double click on the file in Windows Explorer either import CSV into excel like external source data. In the following, I will elaborate on these three ways and point out the advantages and disadvantages of each.
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 figure out exactly how to import each column of data.
If the data matches at least one of the following items, then use the Text Import Wizard:
To make Excel launch the Text Import Wizard, you can either change the file extension from .csv on the .txt(before opening the file), or as described below.
How to open a CSV file using Windows Explorer
This is the most fast way open csv in excel. In Windows Explorer, double click on the file .csv and it will open as A new book Excel.
However, this method will only work if Microsoft application Excel is set as the default program to open files .csv. If so, you will see the familiar icon in File Explorer Windows nearby with filename.
If Excel is not the 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 one or new leaf Excel. Unlike the previous two methods, it does not just open CSV in Excel, but it converts the format .csv in .xlsx(if you are using Excel 2007, 2010 or 2013) or .xls(in Excel versions 2003 and earlier).
Before we move on, please take a look at the screenshot below, which shows original file CSV and desired result 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 consecutive comma or other delimiter character in sequence, 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 updating, layout, and formatting for imported data.
Comment: If your CSV file contains numbers or dates, Excel may not convert them correctly. To change the format of the imported data, select the column(s) with errors, click on them right click mice 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 that's in theory. In fact, many so-called CSV files use other characters to separate data, for example:
- Tabs - 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 deal with known issues starting with the most common.
CSV file is displayed incorrectly in Excel
Signs: You are trying to open a CSV file in Excel and all the data goes into 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 separator is a comma. While in European countries the comma is used as the decimal separator, and the list field separator is the semicolon.
Solution: There are some possible solutions this problem. You can quickly look through the recommendations below and choose the most suitable for your particular task.
Comment: All solutions shown change the delimiter only for given file csv. If you want to change the default separator once and for all, then the following solution will suit you.
- We change separators in the settings of regional standards. Click the button start(start) and run control panel(Control Panel), click 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(Decimal separator), and set a comma (,) for the parameter List separator(List element separator).
Translator's note: These settings are given for the English localization of Excel (and a number of other countries). For Russian localization, it will be more customary to use a comma as a separator between integer and fractional parts, and a semicolon to separate list items.
Comment: Installation in Panel Windows controls 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 CSV file in Excel
Signs: Your CSV file contains values with leading zeros and those zeros are lost when opening the CSV file in Excel.
Cause: By default, Microsoft Excel displays the CSV file in the format General(Common), in which leading zeros are truncated.
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 is how you convert your CSV file to Excel, keeping the zeros in place.
Excel converts some values to dates when opening CSV file
Signs: Some of the values in your CSV file look like dates, and Excel will automatically convert such values from text format to date format.
Cause: As mentioned above, Excel opens the 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 usernames, the entry "Apr23" will be converted to a date.
Solution: Convert the CSV file to Excel using the Text Import Wizard. In step 3 of the wizard, select columns with records that look like dates and change the column format to text.
If you need to achieve the opposite result, that is, in a certain column, convert values 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 opens in this way as a separate Excel workbook. In practice, switching back and forth between several open files Excel can be extremely inconvenient and burdensome.
I hope 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 thank you for the patience of everyone who mastered reading this long article to the very end!
Total CSV Converter- excellent CSV file converter DOC formats, PDF, HTML, TXT, XLS, DBF, XML, including in batch mode. Allows you to easily convert to desired 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 Converter - CoolUtils Total CSV Converter 3.1.1.179 RePack by vovava in detail:
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. CSV to CSV conversion is useful for changing delimiters, date and time format. The program can process selected files in batch mode, it can search for files in selected folders by itself. The program interface is user friendly and very easy to use. 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: Passed (Key).
Command line key:
Silent Installation:/S
Screenshots of CoolUtils Total CSV Converter 3.1.1.179 RePack by vova torrent:
The CSV format stores text data that is separated by commas or semicolons. vcard is a file business card and has VCF extension. It is commonly used to transfer contacts between phone users. And the 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 interface application that was created specifically for converting CSV to VCARD.
Method 2: Microsoft Outlook
Is popular email 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 "Import and Export Wizard", in which we select the item "Import from another program or file" and click "Further".
- In field "Select file type to import" indicate the required item "Comma Separated Values" and click "Further".
- Then we press the button "Overview" 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. Only three options are 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. We leave the recommended value "Allow duplicates" and click "Further".
- Choose a folder "Contacts" in Outlook where the imported data should be saved, then click on "Further".
- It is also possible to match the fields by clicking the button of the same name. This will help avoid data inconsistencies during import. Confirm 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 as a vCard. At the same time, you still need to remember that by default the contact that is previously selected is saved. After that go to the menu "File", where we click "Save as".
- The browser is launched, 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 "Explorer" Windows.
Thus, we can conclude that both of the considered programs 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 more functionality for processing and importing CSV files, but at the same time saving to VCARD format is carried out only by one contact.
Import data from text files in Excel, there are two ways: open a file in Excel application 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 in the dropdown 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 values (CSV) text files that typically use a comma (,) as the text field separator.
You can change the separator character used in both TXT and CSV files. This may be required to ensure successful import and export operations.
Note: You can import and export up to 1,048,576 rows and 16,384 columns.
Importing a text file by opening it in Microsoft Excel
A text file created in another program can be opened as a Microsoft Excel workbook using the command Open. When opening a text file in Microsoft Excel, its format does not change - this can be seen in the line Microsoft header Excel, where the name appears with a text file extension (such as .TXT or .CSV).
Select File > Open.
In the dialog box Open select item Text files.
Find desired file and double click it.
Importing a text file by connecting to it
You can import data from a text file into an existing sheet as an external data range.
If Microsoft Excel does not convert a column to required format, data can be converted after import. additional information see articles Converting numbers from text to number format And Convert dates from text format to date format.
Exporting data to a text file by saving it
Excel sheet can be converted to a text file using the menu command Save as.
On the tab File select item Save as.
A dialog box will appear Saving a document.
In field File type select a text file format for the sheet.
-
A dialog box will open, reminding you that only the current sheet will be saved in the text file. If you want to save 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 will open, reminding you that the sheet you are saving may contain features that are not supported. text formats files. If you only want to save sheet data in the new text file, click Yes. If you have any doubts and need more information about which Excel features not supported by text file formats, click the button reference.
For more information about saving files in other formats, see the article Saving a book in a different file format.
Change the separator used in a text file
If you use the Text Import Wizard to import a text file, you can change the text separator from a tab character to another character in the second step of the wizard. At this point, you can also change how consecutive delimiters (such as consecutive quotation marks) are handled.
Article: Text Import Wizard Learn more about delimiters and advanced options.
Change separator in all text CSV files
IN operating system Microsoft Windows press the button Start and select the item Control Panel.
Open the Change Regional and Language Options dialog box.
Enter a new delimiter in the field List separator.
