Select the 'Order Date' column in this instance and choose 'Split Column', then 'By Delimiter' from the ribbon menu. We’ll use 'First Row as Headers' as that’s how the data’s formatted.Īs my Excel’s region settings are UK and I use UK date format (Day, Month, Year) you’ll notice that the date format below is United States (Month,Day,Year), let’s do an operation on the 'Order Date' column to transform it to (Day, Month, Year).

This zoomed in view of 'Power Query Editor', demonstrates that 'Use First Row as Headers' was selected by default, if that’s not the case you may need to change that setting to 'Use Headers As First Row' in which case the columns will have the generic names Column1, Column2 etc. Create an unfiltered query, then using the 'Advanced Editor' edit the M code and use it in an Excel VBA macro to manipulate and dynamically pull in data from the file.Throw away all the data you don’t want prior to import.Use Power Query to get all of the data to Excel in chunks.There are a number of ways to get your data into Excel from here such as… You’ll most likely see a 'connecting' dialog box appear and disappear, then you’ll see a view of the data like so…Ĭlick on the 'Transform Data' button to launch thePower Query Editor.įrom Excel’s perspective it’s not a misnomer, it truly is a 'power query' editor because it can handle data sets way in excess of Excel’s million and a half row limit.

Go to the folder containing your txt file and click 'Import'.

We’re going to use power query to look at this file so after downloading the data file…Ĭlick on the Data tab, then choose 'Get Data' then 'From File' then 'From Text/CSV' Here we have a large csv file with roughly 1.5 million rows of data, this definitely blows Excel’s data limit. As of Excel 2016 going forward Excel can hold 1,048,576 rows of data, but sometimes even that’s not enough.

How to open a large text file in excel