Dragging-loading-importing data from TXT format files to Excel can be done in two ways. Using Open (select TXT file type) and using the PowerQuery functionality built into versions 2019-2021-365. In previous versions 2010, 2013, and 2016, this functionality was optional and was additionally installed on top of an existing Excel installation.
In versions : 2003, 2007, 2010, 2013, 2016, 2019, 2021, 365, for Mac and for the web
Drag using OPEN
The size of the TXT file is determined by the operating system and can be 2GB for FAT16B, 4GB for FAT32, 2GB for HFS, etc. Which allows you to record a table of 10 columns (each column of 10 bytes) and almost 20 million rows, of course if you have a program that can generate or Notepad that can print such a file.
We certainly didn’t forget the delimiter, which can be: Tab (TAB character – ASCII character code 009), semicolon, comma, space, or user-defined character. Also, let’s not forget that the maximum allowed table in Excel is 1,048,576 rows and 16,384 columns.
This is also a limitation for this type of data upload for further processing.
It means that we do all the processing of more than a million rows using PowerQuery and PowerPivot functionalities.
Drag data wizard
You can open a text file that you created in another program as an Excel workbook using the Open command. Opening a text file in Excel does not change the file format — you can see this in the Excel title bar, where the file name retains the text file extension.
- Go to File > Open and browse to the location that contains the text file.
Select a TXT file from the File Type drop-down list in the Open dialog .
- After clicking the Open button, a three-step help wizard will be launched.
The first step
- It expects you to determine whether you will use a special column delimiter to separate the values by column, or whether you will use the number of characters to determine the width for all rows.
- The wizard recommends the code layout of the text (UTF-8) as well as the option to check if the table header is in the first row (My data has headers.)
- When you have finished selecting options, click Next> .
Second step
- It asks you to specify a separator character such as Tab ( Tab ), Semicolon ( Semicolon ), Comma ( Comma ), Space ( Space ) or some other character you specify ( Other ).
- In case you have consecutive delimiters, for example, duplicated, tripled, check the independent box to treat consecutive delimiters as one ( Treat consecutive delimiters as one ).
- And finally set the quotation marks for texts ( Text qualifier ).
- When you have finished selecting options, click Next> .
The third step
- You are now expected to specify a data type for each column from the TXT file.
- The default value is General format ( General ), it can also be text , date ( Date ) in a certain orthographic form ( DMY , day, month and year), or simply do not drag and import a column ( Do not import column (skip) ).
- Here you can simply leave everything in the existing general format.
- When you have finished selecting the options, click Finish .
Drag using PowerQuery
In case we work with more than a million rows in the table (saved in TXT format), the only way to do it in Excel is to use the PowerQuery functionality. PowerQuery is an add-on in versions 2010 to 2016 and must be installed afterwards, and in versions 2019, 2021 and 365 it is an indispensable function on the Data tab.
When you open the Data tab , the first tool on the ribbon is Get Data . In the first From File option , select From Text/CSV , as in the following image.
In the file selection dialog (Files), select the folder and the TXT file you want to upload. After the information window for connecting to the file, the content opens as in the following image:
When we load data from TXT format and we do not know which character separates the data by column, it is smarter to load each row into one column, and perform the processing itself, i.e. the transformation, in PowerQuery. Simply by choosing for the Delimiter (middle column) a character that we know does not exist in the TXT file. In the specific case it is Colon .
PowerQuery Editor
The necessary transformation of the data from the TXT file will be done by clicking on Transform Data , which calls the PowerQuery Editor .
In order to separate that one column into several columns, we will use the tools on the Add Column tab . The first tool Column From Examples-From Selection as in the following picture allows us a simple way of forming a new column by specifying an example.
An example of separating the payer’s account number can be seen in the following picture. Most often, one example is not enough, so it is necessary for the user to correct the content in the line where he offers a different solution, as in this example in the 9th line.
We can also extract the date from the text date format and later transform it into the correct date format, which we will see in the next steps.
Likewise, if we want to extract text into a separate column by specifying parts of the text between two delimiters, select the option Text Between Delimiters in the Extract menu of the same tab.
The Extract option is on the same toolbar.
The size of the delimiter itself is not limited, and it can be any string that we define in the dialog in the following image.
We can repeat such an intuitive procedure as often as we need, based on the content in one or more selected columns.
Separation of numbers and characters by columns
In case we want to separate all numbers and texts into separate columns, we can use the option Split Column – By Digit to Non-Digit as shown in the following image.
We can see what the result of that separation looks like immediately after clicking on the action.
Of course, now we have the problem of merging whole numbers from one column with decimals in another column using the Merge Column tool in the Text Column tool section .
Pay attention to the beginning of each column, there is a statistical overview of the data in the column, whether all the data is validated, whether there are errors and empty cells. In addition to these data, there is a histogram of unique values and the number of repetitions in the entire column.
When we want to transform dates written from text format to date format, we first use the already known transformation based on our sample.
Then at the beginning of the column header we have a data type symbol with a dropdown.
Simply select Date to define a date field ie. columns.
Closing PowerQuery Editor and dragging data
When we have finally performed all the necessary transformations and removed unnecessary columns, we can close the PowerQuery Editor in the Home tab . By clicking on Close & Load where we have two options.
The first option, Close & Load, closes the editor and drags the data into the Excel workspace table, which is the most common case.
Another possibility Close & Load This , in addition to closing the editor, offers us to drag the data into the Excel workspace table, directly into the Pivot table, Pivot diagram and only to establish a connection with the data with eventual addition to the Data model for further processing in PowerPivot additional functionality.
By choosing the default option Table , we get the final following form in Excel.
