You often need to import data into an Excel worksheet from a text file. Microsoft Excel provides a Text Import Wizard to import data from various text file formats:
■ Comma-separated files (.csv) or tab-delimited files (.txt), as shown in Figure 1.1.2-a.
■ Files with fixed-width columns, where there are no delimiters between columns and data starts at fixed positions on each line (Figure 1.1.2-b).
You will often receive data in a Microsoft Word document or in a plain text file (.txt) that you need to import into Excel for analysis. To import a Word document into Excel, you must first save it as a text file.
When importing delimited data from a file, Excel evaluates the file and displays a preview of how the data will be imported. If the data exceeds certain size limits, only a portion is previewed, and a message appears indicating that the data has been truncated due to size limits. However, this does not affect the amount of data actually imported.
For example, the file importationdedonnees.docx contains the amount of time each player played for Dallas in multiple games during a season. It also includes the performance rating for each lineup.
For example, the first two lines indicate that against Sacramento, Bell, Finley, LaFrentz, Nash, and Nowitzki were on the court together for 9.05 minutes and played at a level of 19.79 points (per 48 minutes), which is worse than the average NBA lineup.
Figure 1.1.2-c: Sample data
We want to import this lineup information into Excel so that, for each lineup, the following information is listed in separate columns:
■ The name of each player
■ Minutes played by the team
■ Rating range
A problem arises with the player Van Exel (full name: Nick Van Exel). If you choose the Delimited option and use a space to split the data into columns, “Van Exel” will occupy two columns. As a result, the numeric data for lineups that include Van Exel will appear in a different column compared to lineups that don’t include him.
To fix this, the Replace command was used in Word to change all instances of « Van Exel » to just « Exel ». Now, when Excel splits data at spaces, “Van Exel” only takes up one column.
Figure 1.1.2-d: Updated data with “Van Exel” replaced by “Exel”
The key to importing data from a Word or text file into Excel is to use Excel’s Text Import Wizard. As mentioned earlier, the Word file (in this example importationdedonnees.docx) must first be saved as a text file.
To save a Word document as a .txt file (Plain Text):
- Click the File tab.
- Click Save As.
- Click Browse, then choose where to save your file.
- In the Save as type list, select Plain Text (.txt).
- Enter a name for your file, then click Save.
In the File Conversion dialog box, select Windows (Default) for the text encoding, then click OK.
Your file is now saved as importationdedonnees.txt.
Close the Word document. In Excel, to open importationdedonnees.txt, click File, then Open, then Browse. Navigate to the .txt file folder, select All Files (*.*) in the file type list, select the file, and click Open.
You will see Step 1 of the Text Import Wizard, illustrated in the figure below:
Figure 1.1.2-e: Step 1 of the Text Import Wizard
Step 1 – Key elements to consider:
- Original data type: Select Delimited when items in the text file are separated by tabs, semicolons, spaces, or other characters. Select Fixed width when all items in each column are of equal length.
- Start import at row: Enter the row number to specify where to begin the import.
- File origin: Choose the character set used in the text file. Usually, you can keep the default setting. If the text file was created using a different character set than your system, update this option to match.
- Data preview: Shows how the text appears when split into columns in the worksheet.
In our case, we choose Delimited. However, suppose you mistakenly choose Fixed width. Then Step 2 of the wizard will appear as shown in Figure 1.1.2-f, allowing you to create, move, or delete break lines. Adjusting column breaks in fixed-width mode can be imprecise and cumbersome.
Figure 1.1.2-f: Step 2 of the wizard after selecting Fixed width
If you select Delimited in Step 1, you’ll see another version of Step 2, shown in Figure 1.1.2-g.
Figure 1.1.2-g: Step 2 of the wizard after selecting Delimited
Step 2 – Key elements to consider:
- Delimiter: Choose the character that separates values in your file. If it’s not listed, check Other and type the character manually. This option is unavailable for fixed-width files.
- Treat consecutive delimiters as one: Check this if your data contains multiple consecutive delimiters or complex delimiters.
- Text qualifier: Choose the character that encloses text. When Excel sees this character, all the text between this and the next occurrence is treated as one value—even if it contains a delimiter.
For example, if the delimiter is a comma (,) and the qualifier is a double quote (« ),"Dallas, Texas"is imported as a single cell value. Without a qualifier or if you choose'as the qualifier, it may be split into two cells: « Dallas » and « Texas ».
If the delimiter character occurs inside the text qualifier, Excel ignores it; if it occurs outside, it is treated normally. - Data preview: Review the split data to ensure columns appear as desired.
In this example, we select space as the delimiter. Checking Treat consecutive delimiters as one ensures that multiple spaces don’t create unnecessary columns. It is recommended to keep Tab selected as well since some Excel add-ins rely on it.
Figure 1.1.2-h: Step 2 of the Text Import Wizard with Delimited selected
When you click Next, you reach Step 3, shown in Figure 1.1.2-i.
Figure 1.1.2-i: Step 3 of the wizard, where you can define formats for the imported data
Step 3 – Key elements to consider:
- Advanced button:
- Specify decimal and thousand separators to match your region settings.
- Indicate if negative numbers might have a trailing minus sign.
- Column data format: Select a format for each column shown in the data preview.
If you do not want to import a column, choose Do not import column (skip).
Once a format is selected, the column header in the preview updates. If you select Date, choose the appropriate date format (e.g., DMY) from the list.
Choose the format that best matches the data to ensure Excel converts it correctly. For example:
■ For monetary values: choose General.
■ For pure numbers: choose Text.
■ For date values: choose Date, then select the date format like DMY.
In this example, choosing General lets Excel treat numbers as numeric values and other items as text.
When you click Finish, the wizard imports the data into Excel as shown below.
Figure 1.1.2-j: The Excel file with lineup information
Each player is listed in a separate column (columns A to E);
- Column F contains the rating
- Column G the game number
- Column H the minutes played
- Columns I and J list the two teams in the match
Of course, if you wish, you can globally replace « Exel » with « Van Exel » to restore the player’s full name in the data.
After saving the file as an Excel workbook (.xlsx), you can use all of Excel’s analytical capabilities to analyze Dallas’s lineup performance.