Sometimes you have data in a text file, and you wish to import it into Excel.
To confirm if your file is a text file, 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.
No comments:
Post a Comment