The tutorial explains how to quickly convert CSV files to Excel in any version, from 365 to 2007, avoiding typical issues. Show
Generally, there are two ways to transfer a CSV file to Excel: by opening it or importing as external data. This article provides the detailed guidance on both methods and points out the strengths and limitations of each. We will also red-flag possible pitfalls and suggest the most effective solutions. Convert CSV file to Excel by opening itTo bring data from a CSV file to Excel, you can open it directly from an Excel workbook or via Windows Explorer. Whichever method you choose, please keep in mind that:
How to open CSV file in ExcelA comma separated values file created in another program can still be opened in Excel by using the standard Open command.
A comma separated values file (.csv) will be opened in a new workbook straight away. For a text file (.txt), Excel will start the Import Text Wizard. See Importing CSV to Excel for full details. How to open CSV file from Windows ExplorerThe fastest way to open a .csv file in Excel is to double click it in Windows Explorer. This will immediately open your file in a new workbook. However, this method only works only if Microsoft Excel is set as the default app for .csv files. In this case, a familiar green Excel's icon appears next to .csv documents in Windows Explorer.If your CSV files are set to open with another default app, then right-click the file, and choose Open with… > Excel. To set Excel as the default program for CVS files, here are the steps to perform:
Convert CSV to Excel by importing itUsing this method, you can import data from a .csv file into an existing or a new Excel worksheet. Unlike the previous technique, it not just opens the file in Excel but changes the .csv format to .xlsx (Excel 2007 and higher) or .xls (Excel 2003 and lower). Importing can be done in two ways:
How to import CSV into Excel with Text Import WizardFirst off, it should be noted that the Text Import Wizard is a legacy feature, and beginning with Excel 2016 it is moved from the ribbon to Excel Options. If the Text Import Wizard is not available in your Excel version, you have these two options:
To import a CSV file to Excel, this is what you need to do:
How to enable Text Import Wizard in Excel 2016 - Excel 365To activate the Text Import Wizard in modern versions of Excel, this is what you need to do:
Once enabled, the wizard will appear on the Data tab, in the Get & Transform Data group, under Get Data > Legacy Wizards. How to transfer CSV to Excel by connecting to itIn Excel 365, Excel 2021, Excel 2019 and Excel 2016, you can import data from a text file by connecting to it with the help of Power Query. Here's how:
Converting CSV to Excel: opening vs. importingWhen Microsoft Excel opens a .csv file, it uses your default data format settings to understand how exactly to display each column of text data. This works fine in most situations. If your text file has specific values and you want to control how to display them in Excel, then do importing rather than opening. Here are a few typical use cases:
How to save CSV file in ExcelWhichever conversion method you used, you can save the resulting file like you normally would.
How to open multiple CSV files in Excel at onceAs you probably know, Microsoft Excel allows opening several workbooks at a time using the standard Open command. This also works for CSV files. To open multiple CSV files in Excel, here are the steps for you to follow:
In Windows Explorer, you can right-click the selected files and pick Open from the context menu. This method is straightforward and quick, and we could call it perfect but for one small thing - it opens each CSV file as a separate workbook. In practice, switching back and forth between several Excel files may be quite inconvenient and burdensome. Instead, you can have all files imported into the same workbook - the detailed instructions are here: How to merge multiple CSV files into one Excel workbook. Hopefully, now you are able to convert any CSV files to Excel with ease. And thank you for your patience everyone who has read this tutorial to the end :) You may also be interested in |