In the old Excel when we wanted to import data from a text file, the interface looked like this. After power
query was integrated as a part of Excel with get and transform
when we want to import data, we automatically get to power query.
This way we have way more options than we had before.
Let's import a text file together.
First off let's take a look at the data we're importing.
The file is sitting on my desktop.
It has extension .txt
It doesn't matter if the file you're going to be importing has .DAT or .CSV
You need to follow the same steps.
Let's just open this with notepad to see what's inside.
So you can double click it or right mouse click and open with notepad.
Here we have our column headers. The information we have in this file is sales information, quantity information,
for different articles by sales documents and company name.
The information itself is split with a comma.
The first one here is company name.
Then this must be the sales document.
Then we have the date, article code, reject information, quantity, and sales in USD.
Notice that we also have empty lines in the middle of the data set and also some lines where we have
no information, just information on the company name.
Let's close this and import this data into Excel.
Open the start file for this section and go to the import tab. To import data into your workbook,
go to the Data tab, in the Get and Transform data section
notice you have "From Text/CSV". If you have another version of Excel, this might be called something different
and you might find it under another dropdown.
If you have Office 365, you're going to see "From Text/CSV".
Just click on it. Then select your file, find the right drive, click on it and click on import.
The moment you click on import the power query editor is opened up. And what it tries to do is one, it
tries to figure out the right delimiter so that it knows how the Column should be split.
And in this case it's found correctly, it's a comma.
If it didn't find it correctly, you can click on this dropdown, select the right one or click on custom
and add your delimiter to here.
But in our case it's a comma.
So we're just going to go with that.
The second thing is that it tried to guess the data types based on the first two hundred rows of data.
We can change this and base it on the entire dataset.
But in our case, first two hundred rows is the same as the next 200 rows.
So it doesn't really matter. Now if you're happy with what you see here and you don't need to make any
other adjustments, you can directly click on load.
What this does is it creates an Excel table on a new worksheet with this transformed result.
You can also click on this dropdown and select "load to" and then select where you want to load it.
If you want to load it on the existing sheet, you can go to "load to" and select a cell on the existing sheet
Or, you can click on transform data and perform more operations on this.
So I'm just going to show you what happens if we just click load directly. Notice a new sheet was created
here.
We have a new table.
This table has a name which looks like our query name which is right here. And here under queries and
connections, we get the list of our query.
Now if for some reason you close this, you can always open it by going back to the Data tab and clicking
on queries and connections.
This shows you the full list of connections you have in place.
The moment we imported this text file we've created a connection to it. Wich means that any time the
information in that text file is updated, we can right mouse click and refresh this and the data here
will be updated.
So source is the text file.
This is the destination.
Anything changes in source.
It's going to get reflected in destination after you press refresh. Now if at any point in time you want to
go and update the cleaning process.
Because right now we haven't done any cleaning.
We just selected the file.
We clicked on next and we just imported the data.
But let's say now I decide, okay I want to remove these empty rows here.
I also want to remove anything that just has the company name and no sales information. To adjust this
further,
I can go and edit my query by double clicking this query and that's going to open up power query again.
But it's going to take me directly to the power query editor where I can do more cleanups.
So I'll take you back here in a second.
Let's just close this again and go back to our file.
Now let's say this was just a one time thing.
You just needed to import this data once.
You don't want to have a connection to that text file.
You want to remove the connection.
Just keep the data. What you can do is to delete the query. Just right mouse click and delete this
query.
It asks you: Do you really want to delete it?
We're going to delete.
Now this is not connected to anything.
If we right mouse click, we can't refresh this anymore.
So now let's go and remove this sheet.
Let's go back and import our data set but transform it before we load it into our worksheet.
So under data, go back to from text/CSV, select a file, click on import.
And now don't click on load but go to transform.
This is something I also recommend that whenever you're importing data files, transform it before you
load it.
This gives you a better feeling
If Excel has picked up the correct data types or not.
So click on transform
and this is going to take us to the power query editor.
Here are some of the steps that you need to do right at the beginning.
Number one, give it a name that you'll like because that name is going to end up to be the name of your
final table.
I'll call this "ImportedSalesData", press enter.
Now notice that there are some steps that have automatically been applied by power query for you.
So right here was the source.
You can click on each of the steps to see how the data looked at that stage.
When I click on source notice that the headers are in separate cells. In the next step,
I can see that it promoted the headers
and after that it changed the data type. Because if you go back here notice that article code is ABC
which means it's not a number.
It's a text data type. Date was also ABC.
It's seen as text and not a date. In the next step,
Excel did all of this automatically for you.
It tried to guess the right data type.
So ABC represents text. In this case it's correct.
The 123 here means it's a whole number which is also correct. Calendar box means it's a date
and that's also fine.
So once you get familiar with what these represent it's really easy just to cross-check if they are
correct.
Now especially for dates, make sure that power query recognizes these as real dates. Because if it doesn't
you're going to run into problems when you analyse your data with pivot tables.
So make sure they all have the correct data types. Now changing the data type here doesn't change your
source data or actually any of the steps here doesn't change your source data. Your source data will
always remain untouched but it changes the final information.
Sales USD was seen as a decimal number.
I'm just going to change it to currency. It asked me if I'm sure, I'm going to say replace it.
And now we get the currency format up here.
If for some reason your source data changes, it's not sitting in the same place or the name of your
file changes you can go and edit that step by clicking on this gear icon right here. So just click
on it and it brings up the features for that step.
This was the file path.
You can browse and change it.
You can also change the delimiter and then click on ok.
Any of these steps that has the gear icon beside it can be adjusted by just clicking on the icon.
Now to improve our final result further I want to remove any rows that are empty.
That's where we come to use the features that we see right here.
A lot of the useful ones are in the Home tab but you'll find other great features in the transform and
add column tabs.
In this case to remove empty rows I just need to go to reduce rows, remove rows, and remove blank rows.
So that checks if the entire row is blank and it removes it.
And notice what it did here.
It added another step.
The other thing I wanted to do is to remove any rows from the final result
if it doesn't have any cells.
If this column is empty and I can filter this out. So I'm just going to click on the filter icon here
and select remove empty.
That basically filters out any rows that didn't have any sales.
And I can see that step in here. If I add any steps by mistake
I can just remove them by clicking on the X icon here.
Let's say by mistake I remove this column right here.
I'm just gonna select it, right mouse click, and click on remove. It adds a step here.
If this was a mistake I'm going to click on the X and remove that step.
I get my column back. Now here's the thing.
Every time my query refreshes it connects to the data set and then it goes through all the steps and
it gives me back the final result.
That's the beauty of power query.
So now that we've done the changes to our data set. Let's go and load it to our Excel sheet.
I'm gonna go to close and load. This time close and load to. I get the option to load this into a table
directly into a pivot table, pivot chart, or just create a connection to it without actually loading it
into my Excel workbook. But I'm gonna load it in this case as a table. I'm going to select the existing
worksheet and go with cell A3 and then click on OK. My data is loaded and it looks super clean.
It doesn't have those empty rows and it doesn't have any sales values that were empty.
Let's check one last thing.
What happens when we change data or add data to our text file? Let's open the text file, scroll all the
way to the bottom and add in some information. I'm just going to copy and paste this.
Actually I'm going to add an empty row and I'm going to change this to new and new and 100 and
200.
Okay so we have an empty row and we have two new ones in there, Control+s and close.
Now notice it says 136 rows are loaded.
I haven't refreshed this dataset.
Let's go and refresh it.
Right mouse click, refresh.
138 rows are loaded and I get the new information on the bottom and I didn't
get any problem with that additional empty row.
Why?
Because power query went through all the steps I told it to do before it gave me the final result.
So that's how you can import text files into Excel.