Sometimes you have data that is in an electronic file, but it is not in Excel
format. If the data is in raw text format (also called ASCII), you can import
it into Excel.
Before you start to try importing the data, you should investigate your data
file a bit. First, make sure that it is in fact a raw text file. If it is a
raw text file, it should open in a basic editor on your computer, such as Notepad.
You can also open it in MS Word, but you will need to confirm its file type
(Word can open many different types of files). Go to File > Properties...
and click on the General tab. Make sure that the file type is Simple
text file.
Next, you will need to see how Excel will figure out how to divide the data
up into different cells (i.e., how the fields are delimited). Typically,
each row of data is separated by a line return. Less standard is how the individual
cells within a row (the column divisions) are separated. Ideally, there will
be a standard character dividing the cells. For example, it might be a comma:
0,0.000112,43.469381,-4.974215,-1.745804e-005,695.510095
1,0.000241,50.561808,-4.974215,-2.109513e-005,808.988924
2,0.000368,55.053678,-4.974215,-8.729019e-006,880.858848
3,0.000491,57.304753,-4.974215,-5.819346e-006,916.876042
4,0.000614,61.313516,-4.974215,2.909673e-006,981.016249
Another common division is a hidden character, such as a space or a tab:
0 0.000112 43.469381 -4.974215 -1.745804e-005 695.510095
1 0.000241 50.561808 -4.974215 -2.109513e-005 808.988924
2 0.000368 55.053678 -4.974215 -8.729019e-006 880.858848
3 0.000491 57.304753 -4.974215 -5.819346e-006 916.876042
4 0.000614 61.313516 -4.974215 2.909673e-006 981.016249
Do not be concerned if all of the numbers that belong in the column do not
line up vertically. Excel will be looking for the identified character to separate
the cells.
Now that you know the characteristics of your text file, you can import it
into Excel. Go to Excel and choose File>Open... and pick your text
file. Excel will automatically recognize it as a text file and start the Import
Wizard:
- The option Delimited indicates a character, such as a comma or a
tab, is used to separate the data fields. If there is no common delimiter,
you can try and use the fixed width option.
- If you do not want to start importing with the first row of the text file,
you can indicate this under Start import at row: Note that it is all
right to have your column headers as the first row you import.
- Finally, you would typically want to indicate that the File origin
is Windows (ANSI). This will help it decide what type of character
is used to divide each row of data fields.
- The Preview window will show you how your data is looking with these
settings. Further windows in the Wizard will show the data in more finalized
form.
Now click Next to go to the next window in the Wizard:
- If you chose Delimited as an option on the previous window, you now
get to choose what that delimiter is.
- Here, a few standard Delimiters can be chosen from. If your file
uses a different character, you can specify it under Other:.
- If you have double commas, triple spaces, etc. delimiting your data fields,
you can click on Treat consecutive delimiters as one.
- Now the Preview window will show vertical lines replacing your delimiter
and separating the data into columns. Scroll up and down your file to make
sure it is properly separating the fields throughout the file.
Click Next when you are ready to go to the next window:
Here, you can choose how to format each column of data. If it is numeric data,
you leave it in the General format. Later in Excel, you can fine tune
this format, adjusting things such as the number of decimals to display or whether
to show it in scientific notation. The format for each column is given at the
top of the preview window. Notice the default it General for all columns.
Now choose Finish:
You'll now see the data placed in cells in an Excel spreadsheet. If you are
happy with the result, save the file now in an Excel workbook with File>Save
as....
If you are not happy with the result, close the file and start the process
again. Things to remember to check for:
- Is your file a raw text file and not a MS Word file?
- Is the same character being used to separate all fields?
- Have you successfully determined whether a blank space is a 'space' or a
'tab'? You can display the hidden characters by going to Tools>Preferences
and choosing the View tab and clicking on All under Non-printing
Characters. Tabs show in gray as right-pointing arrows.
