Now that you have learned about the importance of data quality, in this video you will learn how to import data from a text file using the Text Import Wizard, learn how to adjust column widths, and learn how to add and remove columns and rows. As you know, by default Excel works with .xlsx or .xls files and opens them as workbooks. But Excel can also use data that is in other formats, such as plain text, or data that has been comma-separated and tab-separated. Sometimes, these source files will be saved with a .txt extension and referred to as ‘text’ files, but others might be saved with a .CSV file extension, and are typically referred to as CSV files. Here in Notepad, I have opened a text file that contains data about car sales, and it uses comma separated values (or CSVs) to separate each bit of data in a record. Notice that the top line holds headings, such as Manufacturer, Model, Engine_size, and so on, and each one is separated by a comma. We want these to become our headers when we import the file into Excel. The line below these headings is the first line of real data, and again you can see that each piece of data is also separated by a comma. There are 16 headings and there are also 16 pieces of data on each of the lines below the headings. If we scroll to the bottom, we can see that last data record is for the Volvo S80. Now, to open the file in Excel, we choose File, Open, and then either select the file from the recently used list, or click Browse to find the file we want to import. When we open the file, the Text Import Wizard launches automatically, and it will start to try and determine what your file is. Note that it has been detected as being a delimited file; that is, one that has its data fields separated by a character such as a comma or a tab. As we want the headings to become headers in Excel, we need to ensure that we select the option ‘My data has headers’. We can see a mini preview of the data in the preview box below. Then we click Next to proceed in the wizard. In step 2 of the wizard, we need to select our delimiter; that is, which character is separating our pieces of data; so we select Comma, and deselect any others. Note the data preview now starts to show us what the imported data will look like. You can scroll down and across this preview window to ensure that the data is going to look as you want and expect. It all looks OK, so we’ll continue with the wizard. In step 3 of the wizard, we can set the data format for each column. For example, you might want to change a column to Text or Date format. In this case we can just accept the default General format, and finish the import wizard. In Excel we can see that the headings in the text file have been imported as a header row. But also notice that some of the columns are not showing all the data; some of the headings are not showing in full and some of the data is not shown either; all you can see are a number of hashes in the cells. This is because the column widths are too narrow in some cases. If you remember, we can manually adjust a column’s width by dragging the divider across. But to change them all in one go, we select all the columns first, then double-click one of the selected column dividers. We can do a similar thing with rows by dragging to make them bigger or smaller, or double-clicking a row divider to autosize it. There are some columns that we have decided we don’t really need; namely Vehicle_type and Latest_Launch, so let’s remove those. This can either be done using the Delete drop-down menu in the Cells group on the Home tab, and select Delete Sheet Columns, or by selecting and right-clicking a column and deleting it that way. To add another column, you simply select the column to right of where you want your new column to be, then right-click the column and choose Insert. And let’s give the header a name, such as Year. To delete a row you don’t need, select the row, right-click it, and choose Delete. And to add a row, select the row below the place you want to add your new row, right-click the row, and choose Insert. If you want to save the file as an Excel file, you can either choose File, Save As, or you can click Save As in the yellow tooltip that appeared at the top of the worksheet when we imported the file, and then you would choose ‘Excel Workbook (*.xlsx)’ in the ‘Save as type’ box. In this video, we learned how to import data using the Text Import Wizard, we learned how to adjust column widths, and we learned how to add and remove columns and rows. In the next video, we will discuss the importance of data privacy, including sensitive information, and personally identifiable data.