Learn how to import a TXT file in an Excel worksheet using tried and tested methods!

If you work with system logs and legacy database systems, you often encounter TXT files containing datasets instead of XLSX or XLS files. But that’s not all! During medium to large-scale data transfers, you’ll often find it useful to use the TXT file format to transfer databases instead of an XLSX file. So far it’s okay! How about opening these TXT files in Excel to transfer the dataset to an Excel workbook?

Don’t worry if you’re new to this! Follow along with the methods mentioned below to master your Excel skill of importing TXT files in an Excel workbook.

Open Directly in Excel Desktop App

In most cases, you can open the TXT file in an Excel workbook using the Open command.

Open a blank Excel workbook.

Click on the File menu and choose Open from the left side navigation panel.

You should see the Open menu on the right.

Click on the Browse button and then use the Windows File Explorer dialog box to locate and import the TXT file.

Sometimes, the TXT file might not show readily when you access the Windows File Explorer. To fix this, click on the All Files drop-down menu and select the Text Files option from the list of supported file types.

Complete the importing process by clicking Open at the bottom of the dialog box.

The Text Import Wizard dialog box will open. There, make sure that the Delimited option is selected by default and hit the Next button.

On the next screen, checkmark the checkbox for Tab in the Delimiters section. Click Next.

Select anything from options, like General, Text, Date, etc., and hit the Finish button.

Excel should efficiently organize and import the content of the TXT file into the destination worksheet.

Ensure you click the Save button in the top left corner and choose a destination for the new Excel workbook.

Drag and Drop TXT File

Sometimes, you can save and export an Excel file as a TXT file. If the sender has done this to create the source TXT file, you can simply use the drag-and-drop move to import a TXT file into an Excel worksheet.

Open a blank Excel workbook and drag and drop the source TXT file on a blank worksheet.

If the TXT file is properly structured, Excel should be able to convert it into an XLSX file. The converted file will also open in a new window.

Using the Copy and Paste Method

Most TXT files created as an output for data exported by software and databases are formatted appropriately so you can easily import the content into an Excel worksheet.

Open the source TXT file using the Notepad or any other text processing apps.

Click on any blank space on the TXT file and press Ctrl + A to copy the entire dataset.

Go to the destination worksheet and press Ctrl + V to paste the data in the first cell, A1.

Excel should import the TXT file efficiently.

If the entire data set shows up in one column or the organization of the imported data looks a bit off, you can use the Text to Columns tool to reorganize the copied content.

Using the Get Data Command

The Get Data command allows you to import data into an Excel worksheet from various sources including a TXT file.

Firstly, go to the destination worksheet and click on the Get Data command in the Data tab.

Hover the cursor over the From File menu. An overflow menu of data connections and sources will open on the right side.

Click on the From Text/CSV option.

The Import Data dialog box will open.

Use this Windows File Explorer dialog box to locate the TXT file on your PC or in any network location.

You might not see the TXT file as an option if the default selection for file preference is All Files.

Click on the All Files drop-down menu and select Text Files.

Now, click on the source TXT file and hit Import.

You should see a data import wizard showing a temporary structure of the data you’re importing from the TXT file.

You can use this wizard to further organize the content of the TXT file.

For example, use the Delimiter drop-down menu to choose from options like Tab, Colon, Comma, and more.

Once you’re done reorganizing your data, click on the Load drop-down arrow in the bottom right corner of the Get Data wizard.

Select the Load To option.

You should now see the Import Data dialog box on the active worksheet.

Click on the Existing worksheet option and select a destination cell or cell range for the dataset from the TXT file.

Click OK to complete importing a TXT file into an Excel workbook.

Excel will create a data connection between the source TXT file and the destination XLSX file.

The next time you copy and paste a new data in the existing TXT file hit F9 on the keyboard to recalculate the worksheet.

Excel should be able to update the old dataset and serve the new data, organization, etc.

Using Power Query

Sometimes, the TXT file you want to import may not fully fit one worksheet because Excel can handle only so many rows, which is 1,048,576 rows, and much fewer columns, which is 16,384 columns.

Here, you can use Power Query to transform your data, minimize unwanted content, and export data that you need for further analytics and visualization purposes.

To use Power Query Editor when importing TXT into Excel, follow the steps explained earlier to bring up the data preview wizard. You can use the From Text/CSV command from the Get & Transform block of the Data tab.

Once you see the data preview wizard, click on the Transform Data button in the bottom left corner to load the TXT file into the Power Query Editor interface.

Now that the data has been loaded in Power Query, you can use various tools from tabs, like Transform, Add Column, View, and Home.

Once you’re done working with your raw data, click on the File tab and select Close & Load To to bring up the Import Data wizard.

Click on the Existing worksheet option and select a cell on the active worksheet as a destination for the data to be imported from the TXT file.

Excel will create a table for the imported data. It’ll also create a data connection with the TXT file saving all the data transformations you’ve done in Power Query.

The next time you need to import a similar but new TXT file into the same worksheet, simply double-click the query below the Queries & Connections navigation panel on the right.

The Power Query Editor interface will open. Double-click on the Source step in the Query Settings panel.

The Comma-Separated Values dialog will open. Use the Browse button to select the new TXT file.

This way, you can avoid the data transformation tasks again if the dataset you’re importing is similar to the existing TXT file.

Conclusions

If you’re here, congratulations!

You’ve learned the proven and effortless methods to import data from a TXT file in an Excel worksheet.

Here, you’ve seen five different methods for importing a TXT file into Excel. These include simple drag-and-drop moves, the Excel Open command, and copying and pasting the intended file. Additionally, you’ve explored the advanced Get Data command and an automated approach using Power Query.

If this Microsoft Excel tutorial helped you to learn an important skill today, share your acknowledgment in the comment section. If you’ve got any feedback, don’t forget to mention that too!