cis 122l
Week 9
Module 12 Instructions and Corrections
During the next week you will cover some of the material in Module 12 – Collaborating on a Shared Workbook in your textbook. Additional new and review material is in the Assignments sheet. You will also cover Importing a Text File in the notes below.
Please note the following addition to the textbook in Module 12:
Page EX776 – A slightly easier way to save a worksheet as a PDF file is File/Export/Create PDF.
Importing (not opening) a Text File
Text files are recorded files of data that have no special coding like font changes or line spacing in them. They consist purely of readable characters. Excel can import or export two flavors of text files: delimited and fixed width (also sometimes called fixed field length).
Delimited text has each item of data separated from the next by some character, usually a comma as shown below.
|
Delimited Text: |
Mary,Jones,64,Vice-President |
|
|
Sam,San Antonio,41,Engineer |
|
|
Tucker,Fedderton,38,Sales Associate |
|
|
Peter,Miller,56,Clerk |
|
|
Elizabeth,Persat,31,Secretary |
With delimited text, sometimes the character data (as opposed to numerical or date data) is surrounded by quotation marks in case the text contains the delimiter character itself.
Fixed width data contains no separating markers (delimiters). Instead, each item of data in a field is padded to exactly the same number of characters with spaces used to fill in any gaps. Thus, the data lines up in neat columns.
|
Fixed Width: |
Mary Jones 64Vice-President |
|
|
Sam San Antonio41Engineer |
|
|
Tucker Fedderton 38Sales Associate |
|
|
Peter Miller 56Clerk |
|
|
ElizabethPersat 31Secretary |
To import a text file:
If you are using Excel 365, they removed the Get External Data button that you need, so you must follow the instructions at the bottom of this page to re-install it. Otherwise, the incoming data will be put into a table, adding extra column headers and throwing off the homework instructions.
1. On the Data tab, click the Get External Data button followed by From Text. (If your version of Excel does not have a Get External Data button, click the From Text button. If you are using Excel 365, you must follow the bulleted steps in the section below first.)
2. Navigate to the text file, highlight it, and click the Import button.
3. In the Text Import Wizard dialog box, choose either Delimited or Fixed Width. If there are lines of text at the beginning of the file that you do not want to import, set the “Start import at row” option. You can see the actual text in the preview window. Click the Next button.
4. In Step 2 of the Text Import Wizard dialog box, choose the delimiter character if the data is delimited or set the column dividers if the data is fixed width. If the delimiter character is not one of the four listed characters (tab, semicolon, comma, or space), check the Other box and type the delimiter character in the box at the right of “Other.” Click the Next button.
5. Click on each column of data one by one and change the data type, if necessary.
6. Click the Finish button.
7. In the Import Data dialog box, select the cell that will be the upper-left corner of the target range and click OK.
To install the Legacy Wizards command on the Ribbon if you are using Excel 365:
· On the File tab, click the Options button.
· Select the Data category in the left column.
· In the Show legacy data import wizards section, select From Text (Legacy).
· Click OK.
|
· Then, in Excel 365, on the Data tab, in the Get & Transform Data group, click on Get Data. · Select Legacy Wizards and then pick From Text (Legacy). (NOTE: Once you have done these steps to install the legacy text import command, you should not need to do them again.) |
|