Sometimes when you working with data, you need to get data from multiple sources. And text files are one of the most popular sources for data. Sometime , when you donwload data from a web portal, or a export it from inventory software, it comes in a .txt file.
To use that data further in the Excel, you need to open that file in the Excel. And the good news is Excel is pretty much amazing in files like .txt and .csv. And, in this tutorial, we will look at the three different ways to open a text file in Excel.
Steps to Open a Text File in Excel
To open a text file in Excel, you can simply open it using the open dialog box. Once you open it, Excel will show you the text-to-columns option to import data into the worksheet. If data is in tabular form, you need a few clicks to add it to Excel.
- First, you need to go to the File tab; in the File tab, you have the Open option. In the Open option, click on Browse to open the dialog box to save the file.
- After that, in the bottom-right corner of the dialog box, there is a drop-down. From that drop-down, make sure to have “All Files” selected. This allows Excel to open all formats of files in Excel.
- Next, you need to locate the folder where you have the file which you want to open. Then, click on the file that you want to open the file.
- You have the text-to-columns dialog box. Click on the next button to go to the next step, and checkmark “My data has headers.” If your data already has headers.
- In the next step, checkmark the “Tab”, and click Next. This tells Excel to separate data using the tab into columns. Even when you check-mark the tab, it shows you the snapshot of the data in the data preview.
- In the end, click on the Finish button to get the data into the worksheet. Once you click, it will open the text file in Excel and get the data in the worksheet.
Below is the data that you get in the end when you open it in Excel. Once you open a text file in Excel, you’ll get the data, but it won’t save that text file as an Excel file. Save it as an Excel workbook, go to the File ⇢ Save As.
Benefits of this Method – When you open a text file (txt) in Excel, you open it within the Excel. And when you make changes, all those changes go directly into the text file. So when you want to make chages to the file after opening it, it is the best way for this.
Options in the Import Wizard to Know When you are Opening a Text File
There are a few options in the import wizard which you can use to get better control over when you opening a text file in Excel.
- My Data has Header – When you tick-mark this option when you have headers in the data. And you want to treat those header as there are in the Excel file.
- Start Import at the Row – When you open a .txt file, Excel taks it as tabular form, and you can choose the row from which you want to import the data.
- Delimiters – If your data is not in the tabualr form and have a delimiter in between the values, in that case, you can specify the delimiter to split data into columns.
- Column data format – You can specify the format of the each column before opening the data from the text file. Select the each column in the “Data Preview” and then specify the data format.
- Settings for Numeric Data – There are specific setting which you can use for numeric data. For example, indetifing the decimals, thousand seperator, etc.
Open a Text File in Excel with Power Query
Using Power Query is a game changer when you want to open the text file but actually don’t want to make changes to the file itself. Power Query connects the text file as a data source and then you can get all the data from it. Let’s do this with the steps below.
- To open power query, you need to go to the “Data” tab, and from there, click on the “Get Data” drop down. From there, in “From File”, go to the “From Text/CSV”. This option helps you to connect a text file with the Power Query as a source.
- Now, once you get the “Import Data” dialog box, you need to get locate the text file which you want to open (connect). Click on it and then click on the “Import” button.
- After that, it will show you a preview of the dat which you have in the .txt file which you have loaded to the power query. Here you have two options to use one is to load the data and the second is to open the power query editor and transform the data. From now, click on the “Load”.
- In the end, you have the data in the worksheet from the text file. This data is only linked with the text file and you need to save this Excel file seperatly.
The best part of using the power query for importing data from the text file is it’s dynamic. When you update data in the text file, you can update it in Excel by clicking on the refresh button.
Power Query also allow you to open and get data from multiple files, you checkout this tutorial to learn mroe on this.
Copy-Paste Data from Text File to Excel Worksheet
You can also use the simple copy and paste method. To get the data from a text file. Usually, text file doesn’t have much formatting other than Font Size, Font Style, etc. And when you copy and paste data from it that formatting won’t get pasted there in the Excel file.
Once you open the text file and use the keyboard shortcut Ctrl + A to select all the data from the text file.
Go to the worksheet, and then select the cell on which you want to paste the data. After that, use the keyboard shortcut Ctrl + V to paste the data.
