See all How-To Articles

How to Open a Text (.txt) File in Excel & Google Sheets

This tutorial demonstrates how to open a text file in Excel and Google Sheets.

 

converttxt intro

 

Open Text File in Excel

  1. In the Ribbon, select File > Open > Browse, and then select the text or CSV file to be opened.
    The Text Import Wizard opens to the first of three steps.

 

Convert Step1

 

  1. Depending on the data contained in the file selected, you can either choose Delimited or Fixed Width as the file type. If the data is separated with a character like a comma, semicolon, space, or tab, select Delimited. If the first row of data contains column headings, check My data has headers.
  2. Click Next.

 

Convert Step2

 

  1. In Step 2, select the type of delimiters contained in the text file – in this case the tab delimiter. Then select the text qualifier. In some cases, the data may be enclosed in quotes, but for this example, (none) is selected as the text qualifier.
  2. Click Next.

 

Convert Step3

 

  1. The final step of the text import wizard allows you to specify the data type contained in each column. Most of the time, Excel is able to determine the data type automatically, and you can leave the selected Column data format on General.
  2. Click Finish to import the data into Excel.

 

Convert Text File

 

Import Text File Using Get Data

An alternative way of importing data into Excel from a text file, is to use the Get and Transform Data feature.

  1. In the Ribbon, select Data > Get and Transform Data > From Text/CSV.

 

Convert GetData Ribbon

 

  1. Select the file to be imported, and then click Import.
  2. The file appears in a dialog box with the delimiter already automatically selected, and the text divided by Excel according to the data stored in the text file. Click Load to load the data into Excel.

 

Convert LoadData

 

The data now appears in a new sheet in Excel. There are now two new tabs on the RibbonTable Design and Query – as well as a Queries & Connections pane appearing on the right-hand side of the screen. This shows that the data is linked to the TXT file and if any data in the TXT file was to change (external to Excel), the data displayed in Excel would change too.

 

Convert GetData LinkedData

 

  1. To unlink the imported data from the external TXT file, in the Ribbon, select Table Design > External Table Data > Unlink.

 

Convert GetData Unlink

 

  1. The data is imported into Excel as a table. To convert it to a standard Excel range, in the Ribbon, select Table Design > Tools > Convert to Range.

 

Convert GetData ConvertToRange

 

Both the Table Design and Query tabs on the Ribbon disappear.

Open Text File in Google Sheets

  1. From the File menu in Google Sheets, select Import.

 

Convert GS ImportMenu

 

  1. Click Upload, and then click on the blue Select a file from your device button to select the required file.

 

Convert GS Upload

 

The name of the file to be imported is displayed under File with the default options available for importing automatically selected.

 

converttxt importfilename

 

  1. To change the Import Location, click the drop-down list under Import Location and select the option required.

 

converttxt importlocation

 

To change the Separator type, click the drop-down list on the right and select a character to delineate by.

 

converttxt importdelimiter

 

  1. Keep Convert text to numbers, dates, and formulas checked and then click Import Data to import the file into a new Google sheet.

 

converttxt opennow

 

  1. Click Open now.

 

converttxt gs final