Excel 2007 Import Text File in CSV format

1. Make sure you have open the Excel 2007 worksheet that you would like to import the text data file into. The example we are using is a Google Webmaster download file in CSV (Comma-Separated Values) format. This data will be imported into a worksheet taking care to get the date field in the correct format. Ensure that you have the Data tab selected then click From Text as shown below.
   
   
2. This will open the file select browser window, navigate to where you have stored your text file and select it then click Import. The file we are using in this example is shown below, you can see it has a CSV file extension and you could open this directly in Excel, however, importing the data allows you to get the correct format for dates and aggregate the data for further processing. The CSV file format cannot store any style information or formulas and should be treated only as a data transfer mechanism.
   
   
   
   
   
   The first four lines of the CSV are shown below. The actual file contains thousands of lines. The names of the fields are in the first row and the next three rows are some of the data. You can see the fields are separated by commas and the data field named "Last found" is in US format M/D/Y (ie Month/Day/Year).
   
   
   
   
   
   
   
   
3. The default file type is correct in this case "Delimited", click Next.
   
   
   
   
4. Here we need to change the Delimiter from Tab to Comma as highighted above, then click Next.
   
   
   
   The third field is then selected by scrolling horizontally in the Data Preview as shown. The column data format for this column is then changed using the radio button to Date and type of data is selected in the drop down list in this case MDY. WARNING:  If you do not select the correct date format the data in Excel for that column will not be a true date and formatting will not work correctly and neither will sorting. Once the selections have been made click Finish.
   
   
5. The next dialog ask where you would like to drop the data.
   
   
   
   We will place it in the current worksheet at cell A1. so click OK.
   
   
6. This shows a sample of the data just imported, you can see the correctly formatted date field in column C. Here the data is showing as D/M/Y which is a European style of date.
   
   
   
   
   If you wish to sort the data that has just been imported you can select the entire set of data by moving the selected cell to A1 if it is not already there (Press Control-Home) and then Press Control-Shift-End (3 keys) to select the entire data area. Once you have selected the data area use click the Data Sort icon to get the sort dialog.
   
   
   
   
7. Once the dialog appears use to enter the sort fields as shown below.
   
   
   
   In this example, the sort is being done using two columes the Page (Column A) and the Link (Column B). When you click OK the rows are sorted according to the selected sort specification. Notice that the checkbox for My data has headers is on, this so that the headers remain at the top of the list and do not get sorted with the other rows.
8. The results of the sort now show different rows at the top of the list:
   
   
   
   
9. You will need to save your work if you wish to keep it for future use. With imported text data in CSV format it is possible to keep adding data to the same sheet if it is transactional type data that is time ordered. In this case it is a snapshot at a particular time so each file could be added to a separate worksheet and provide a history of the snapshots.
   
   We have a range of Excel 2007 training courses from beginners to experts and we can customize the training to your exact requirements.