Transcripts for the above video clip:

Import Data

In Excel it is possible to import data from a number of sources. One of these sources is a text file. You can do it in a number of ways. Probably the easiest is just to open a file. I have selected the Files as type “All files” – this will enable you to see all the files. In this example I am going to pull through this file – a text file – called “TEXT EXAMPLE”. Click it and open it. You’ll see Excel automatically goes to a Text Import Wizard screen. This screen allows you to control how Excel is going to import the data. The first thing it needs to know is how this data is split up. You’ll see the options are “Delimited” where a character splits the information or else “Fixed Width” , where for example the first column is 10 characters, the next column is 10 characters, etc. If you had to click “Delimited” and say, “Next”, what you’ll see is that Excel has put a Tab and the data hasn’t really changed. We can see that commas have actually been used so I’m going to switch Tab off and click on Commas, you’ll see Excel immediately puts lines where all the commas appear. You can now scroll down to see if it makes sense. If you scroll down you’ll see the data seem to be correctly split in columns.  You can use other features on the  Text Import Wizard screen (semi-colon, space, other) to split the data.  Click “Back” and you’ll see that if we had chosen  “Fixed Width” and then click “Next”, you’ll see Excel tries to guess where the fixed widths are, you can manually move the columns as shown, and you can add a few more in if you want to. So in this case you have to tell Excel exactly where the split is. Click “back” because you know in this case it is “Delimited”, click “Next”, and click on “Comma”, click “Next”. Excel now gives you the opportunity to set the Format of each column that you have imported. For example, in the first column and look at the data – it looks like dates, so we are going to instruct Excel not to bring the date in as text, because you run the risk of Excel thinking the date is a text and you will have to do work to get it into a date format, you can highlight the column and then click “Date”  in the Column Data Format – you can be more specific and specify what the format of the date should be. You can see that it is DMY. However if you have data that is MDY, you can select that instead. In the next column you can select “General” or “Text”. If you do not want to import a particular column, you can highlight it and then click on “Do not import column” and Excel will skip that column. There is also an Advanced Function, click on it and in this you can specify how decimals are separated if its not the standard way and thousands as well. At  this point we can click “Finish” and you’ll see that what Excel has done is it has brought all the data through and if you scroll down you’ll see that where the key data is, has been nicely split and now that the data is in Excel we can now work on it to tidy it up a bit.