You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
If you've worked with Excel long enough, you've probably needed to get data into Excel so that you can analyze it, or maybe create a chart. Where does this data come from? It might come from a database or some other program, or even the web. And it can come in a variety of forms.
Whatever the case, I can guarantee you the last thing you'd ever want to do is retype it, so that option is out unless you really love typing and have tons of extra time on your hands. And copying from another program or from the web and pasting directly into a cell in a workbook might leave you with nightmarish looking results I've seen this, believe me.
Luckily for you, there's a much better way to get that data into Excel. Importing! Yes, importing is not just for purveyors of fine art or exotic foods.
When you use the Text Import Wizard, you don't open your data source in its native program. You point Excel's Text Import Wizard to a file, and the wizard leads you through a few steps where it looks at the rows of data in the source file and makes suggestions based on the layout of the file. Of course, you're allowed to fine tune or override these suggestions so that the data comes into Excel just the way you want.
In this file, the four pieces of information for each employee are separated (also known as "delimited") by a comma. The first row of the file is the column names, which are also separated by commas.
The rows of information look just like this in the text file:
To import the contents of the file, select an empty cell in Excel — this can be in a new workbook, a new worksheet in an existing workbook, or in an existing worksheet. The wizard isn't picky!
Now, click the Data tab, and in the Get External Data group, click From Text.
This starts the Text Import Wizard. Go find your file, and click Open. Notice how the Delimited button is already selected?
My data certainly is delimited by those commas, so I'll click Next. The default delimiting character in Excel is the tab character, so it's pre-selected here in step 2. Notice how, with the tab character as the delimiter, the preview doesn't look right? So I see I need to change that from Tab to Comma.
Now I've selected Comma instead. There, see how the preview changes and looks right now?
I'll click Next to go to the third and last step of the wizard. I can select each column and, if I want, change its data type so that it has a format I prefer or a right or left margin. But all of these columns look fine, so I'll click Finish.
Now, Excel asks me if I want to import the data right here in the current cell or create a new worksheet. That's nice, because what if I had selected a cell that contained data I didn't want to overwrite? Anyway, I'm in blank cell in a new worksheet, so I'll accept Existing Worksheet and cell =$A$1, and click OK.
And here's my imported data! As a plus, the Text Import Wizard (in most cases) optimizes the width of the columns to fit the data so you don't have to.
-- Gary Willoughby
Comments: (7) Collapse
Good article Gary! Years ago I worked at a company where vast amounts of data were managed in Excel between legacy mainframes. Data was imported into Excel, massaged, then put back into delimited files to go on to another machine. Not efficient, but Excel always does the trick in a pinch. Another idea beyond Gary's excellent pointer is that Access supports the same import/export capability, and also lets you save the import profile. That way in case you have to perform the process 3 times a day, every day of the week, you're not repeating the same steps every time.
Thanks, Brandon - glad you liked it. Great tip on the Access import/export profiles -- I remember testing them when I was an editor for Access 2007. As a bit of a coincidence, we have an upcoming guest post from one of our Access writers planned that will cover exporting Access data to Excel.
Question about 2007. I use TODAY() to start the year in a drop-down list for VB macro (select the year form control list box). Everytime when hit undo, run time error pop-up. Any help to resolve this??
@gama: I'd recommend posting your question in the Microsoft Excel 2007 Answers forum: answers.microsoft.com/.../office_2007-excel. Tip: The more details you provide about the problem, the more likely it is you'll get help quickly. Hope that helps you out.
The biggest problem with importing, is that when there's data in the field, that looks like date, Excel converts it into say Sep-2011, instead of showing 2011-09. It shouldn't be doing upon importing.
The other issue, if you're importing a number, very often Excel treats it as text, and you can't sum them up. You need to type 1 anywhere near, and paste special multiply. Only after trying multiplying this way by 1, text becomes a number, and you can use it.
Nice article Gary.
Some time ago I have written up a similar tutorial here:
www.jkp-ads.com/.../importtext.asp
It is great. I often use this method to manage data in excel.
<a href="http://www.dataentryuk.com">Data Entry</a>
Comments: (loading) Collapse