Software

Import and convert Web text to columns in an Excel worksheet

Armed with a Web query and some conversion options, you can bring Web table data into a worksheet in a format you can use. This example walks through the process.

Excel makes it easy to extract table data from a Web page -- but to be of use in a worksheet, you will need to convert the text tables into columns. For example, say you need to chart U.S. GDP data from 2002 through 2006. First, you would use a Web query to import the data from the Web. Then, you'd extract the data into two separate columns. Follow these steps:

  1. Open a blank worksheet.
  2. Go to Data | Import External Data and then click New Web Query. In Excel 2007, click the Data tab, click Get External Data, and then click From Web.
  3. Enter the Web page URL http://forecasts.org/data/index.htm (Figure A).

Figure A

gdp

  1. Scroll to Gross Domestic Product.
  2. Click the arrow next to the Gross National Product Table. (If there are no arrows next to tables on the page, click Show Icons twice at the top of the dialog box to display them.)
  3. Click the Import button (Figure B).

Figure B

import

  1. Click Existing Worksheet, if necessary.
  2. Click the cell on the worksheet where you want the upper-left corner of the Web data to appear and click OK.
  3. Select the data cells containing the text you want to convert.
  4. Go to Data | Text To Columns. (In Excel 2007, click Text To Column in the Data Tools Group on the Data tab.)
  5. Select Delimited (Figure C).

Figure C

delimited

  1. Click Next.
  2. Click to select the Space check box under Delimiters (Figure D).

Figure D

finish conversion

  1. Click Next.
  2. Click Finish.

The data from the Web is now ready to chart (Figure E).

Figure E

chartable data


Miss an Excel tip?

Check out the Microsoft Excel archive and catch up on other Excel tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

9 comments
cisauditor
cisauditor

A word of caution. Check the last row of imported data against the website data to ensure all data was imported. I recently imported a large amount of data using the web query only to later find about 40% had been truncated for reasons unknown.

rmodesi
rmodesi

Works very well. The comment about too many steps needs more Excel experience. Ron

malcolm
malcolm

Very useful - I've done this many times. However, I need to import the current PGA Money Leaders table from pgatour.com but it doesn't work - looks as though it's blocked. Anyone know a way around this? Currently I do the copy and paste route.

kwilliams30
kwilliams30

I find it much easier to simply highlight the table data I want, then copy and paste into Excel using the "Paste Special - Text" function. Excel will automatically put into columns and it is immediately ready for use. Much simpler

mattohare
mattohare

It stores the details of your webquery in a named range on the work sheet. If you want more than one query on a sheet, you need to delete the named ranges as you go along. (That or use a different sheet for each set.) I tend to have one sheet for the basic import. I scrub the data, then move it to another sheet. (Sometimes this is because I'm taking data from several similar pages.)

john
john

Just imported so looks like you need to do the whole page using the arrow at the very top left of the page as the individual tables are not available by themselves. Probably created as one table when posted so that may be the issue

mattohare
mattohare

If you're importing a table, it will go into columns. The author?s example didn't show this very well. This tool brings everything in with a lot fewer steps. I've never had to use the step of splitting columns. (Unless the table was a badly done table, then I'd have to split the columns even with copy/paste.) Copy/Paste can be good a lot of the time, but it brings along some odd side effects. Also, you run the risk of having to remake the selection if the table is a lot longer than the browser. If you want to see a scenario where this really flies, try: https://www.cia.gov/library/publications/the-world-factbook/fields/2054.html With very little work, you have a great table of national birth rates, with out the hassle of dragging the mouse down to the exact point to select the table and only the table. (Do make sure tick the yellow/green arrow at the upper left of the table of data itself. I can tell you that I've used this process a lot to get data in from various places on the web. I find it valuable to the extreme.

mattohare
mattohare

I think some pages do the layout with something other than tables. Especially with all the anti-table craze, some use positioning and layout for something that tables WERE designed for. Oh well, there's no such thing as a perfect solution.

jordan1
jordan1

Hello, I was hoping this would work for an MRP program we us at work called CINCOM which shows a material detail screen. I successfully select the sheet using the method recommended but when it imports the sheet there's a lot of data that doesn't move over. It shows all of the Column headings successfully but no data other than just one row of data.

Editor's Picks