You may have data from other sources such as text files, CSV files databases.
Whether that’s an Access database or some other database in the back end maybe it’s a web page that you want to build a pull data from.
But you’ve got external data that you now want to import and work with inside of Microsoft Excel.
One of the most common sources to import data from, we’re going to take a look at importing from a text file or a CSV file, really the imports the same for both.
In this instance we’re going to be importing data from a tab delimited text file.
I’m going to show you what the text file looks like. I going to open it up on my computer and the need of application in this case Notepad.
Microsoft changed the way that imports happen from various sources in the new editions of Microsoft Excel.
Now inside the newer releases and like I said I’m actually open up the text file in front of you, I’ve got one called sales data text import and this is what it looks like.
I’ve got column headers along the top rows the records down below and each column is separated by a tab on keyboard. Ultimately that tab delimiter is going to help Excel identify the data and how it should be structured inside and document where the column breaks should take place and so on. this is tab delimited but another very common one is a comma separated document where each column or each value will be separated by a comma.
There’s actually several different types of delimiter and we’ll actually see that as we start to import data.
I’m going to go to Data tab and I’m going to find the Get External Data (Get & transform data) section which has all of your import options here.
Ultimately once we start importing this is going to take us into Excel Power query where we can ultimately do some additional transforming of the data or you can just start to load it and dump it directly into Excel.
Go to Data tab-> From Text-> Select text file -> then select Import
Select My data has headers if your data contain header, click Next
At Delimiters, check on your separator data, click Next
Select column data format, then click Finish
Then Click OK
Now I can start calculating it sorting or filtering it creating pivot tables whatever I need to do all based off of that text file that we saw earlier.