Importing Data in Excel

If you want to do anything with data, you must at first get that data into your worksheet. Excel can import data from a text file and can also retrieve data from websites.

Importing from a file

We are discussing here which file types Excel can open directly. To get data from file types use this command: File ➪ Open ➪ Computer ➪ Browse. The following image shows the list of the file types Excel can open.

Importing Data in Excel

Excel can open the above file types.

Spreadsheet file formats

Excel 2013 is backward compatible. Backward compatible means that when a new version of Excel comes in the market, this new version is compatible with the old version Excel files. Excel 2013 supports XLSX, XLSM, XLSB, XLTX, XLTM, and XLAM file types. In addition to supporting these current file formats, Excel 2013 can open workbook files from all previous versions of Excel.

  • XLS: Binary files created by Excel 4, Excel 95, Excel 97, Excel 2000, Excel 2002,
    and Excel 2003
  • XLM: Binary files that contain Excel 4 macros (no data)
  • XLT: Binary files for an Excel template
  • XLA: Binary files for an Excel add-in

Excel also support one file format created by other spreadsheet software: ODS, the OpenDocument spreadsheet format. ODS files are created by a variety of “open” source software, including Google Drive, OpenOffice, LibreOffice, StarOffice, and others.

Note: Excel does not support Lotus 1-2-3, Quattro Pro, or Microsoft Works files.

Database file formats

    Excel 2013 can open the following database-related file formats:

  • Microsoft Access files: These files have various extensions, including MDB and ACCDB.
  • dBase files: Excel 2013 support file produced by dBase III and dBase IV. Excel does not support dBase II files.

You can perform various types of database connections with databases through Excel. After connected to databases, you can perform a query to retrieve data selectively rather than the entire database.

Read More: Importing a text file in Microsoft Excel

Text file formats

In a text file, you will find raw characters, with no formatting. Excel can open almost all types of text files:

  • CSV: Comma separated values. Columns are delimited by a comma, and rows are delimited with a carriage return (Enter).
  • TXT: Columns are delimited with a tab, and rows are delimited with a carriage return (Enter).
  • PRN: Columns are delimited with multiple space characters, and rows are delimited with a carriage return (Enter). Excel imports this type of file into a single column.
  • DIF: The file format originally used by the VisiCalc spreadsheet. It is rarely used.
  • SYLK: The file format originally used by Multiplan. It is rarely used.

Most of these text file types have variations. For example, text files produced in a Mac environment have different end-of-row characters. Excel can easily handle the variants without a problem.

When you try to open a text file in Excel, the Text Import Wizard dialog box might pop-up to help you specify how you want the data to be retrieved.

Importing Data in Excel

Text Import Wizard dialog box.

Tip: To avoid the Text Import Wizard, press Shift while you click the Open button in the Open dialog box

Importing HTML files

You can open most HTML files with Excel. The HTML files can be located on your local drive or in a web server.

Choose File ➪ Open and locate the HTML file from your local drive. If the HTML file is on a web server, just copy the URL and paste it into the ‘File Name’ field in the Open dialog box.

Importing Data in Excel

Put the URL in the File Name field (4).

Sometimes, the HTML file may look exactly as it is in a web browser. Other times, it may be different totally, especially if the HTML file uses Cascading Style Sheets (CSS) for design.

How to import XML files

XML (Extensible Markup Language) is a text file format used to contain structured data. Data is enclosed in tags, which also serve to describe the data.
You can open simple XML files with little or no effort. Complex XML files will require some work, however. See the following image where I have imported Exceldemy.com’s post-sitemap.xml file as tables in my Excel program.

Importing Data in Excel

Importing XML files in my Excel program

You can import XML files in other formats like “As a read-only workbook” or “Use the XML Source task pane”. Try to do it by yourself.

Importing a text file into a specified range

Sometimes you may want that you will place a text file into a specific range of your worksheet. You will at first think that you have to import the text file in a new workbook and then copy the data and paste it into your range where you wanted to place it. But there is a direct way to do it. 🙂

We are going to import this CSV file into our Excel worksheet at Cell B3.

At first, let’s check the inside view of a CSV file. CSV means comma separated value. In this type of files, values are stored with commas.

Importing Data in Excel

Inside of a sample CSV file

Let’s import this CSV file in Cell B3 of our worksheet.

  1. Choose Data (tab) ➪ Get External Data (Group) ➪ From Text (command). “Import Text File” dialog box will appear.
  2. Navigate to the folder where you have saved the CSV file “DSE Index.csv”.
  3. Select the file “DSE Index.csv” from the list, click the Import button. The “Text Import Wizard-Step 1 of 2” dialog box will appear now. If you double-click on “DSE Index.csv” file, direct “Text Import Wizard-Step 1 of 2” dialog box will appear.
  4. In “Text Import Wizard-Step 1 of 2” dialog box, select the ‘Delimited’ Radio button, then click on the ‘Next’ button, deselect ‘Tab’ and select ‘Comma’ from the ‘Delimiters’ list, and Select ‘Finish’ to end our job with “Text Import Wizard-Step 1 of 2” dialog box.
  5. When you clicked ‘Finish’, the ‘Import Data’ dialog box will appear. You can choose also “New worksheet” to select a new worksheet to place your CSV values.
  6. Click OK, and Excel imports the data. Observe the following two images.
Importing Data in Excel

Import Data Dialog Box. Enter cell address where you want to place your CSV values. We have entered E10 value.

Importing Data in Excel

CSV data placed in the E10 position of the worksheet.

Copying and Pasting Data

Sometimes it is better to try with standard copy-and-paste techniques. Say we want to do our previous job with this simple copy-and-paste technique. Open the ‘DSE Index.csv’ file and select all the data inside it using CTRL+A command and copy it with the command CTRL+C. You can also right-click after selection and click Copy from the list.

Importing Data in Excel

Select all data inside the CSV file using CTRL+A, then use CTRL+C to copy it. You can also right-click and select Copy from the list.

Select your preferred cell, where you want to place these data. Then Paste your data using this command CTRL+V. The screen will look like this.

Importing Data in Excel

After pasting the screen will look like this.

Immediate after you have pasted your data, you will find that a “Paste Options:” shortcut control has appeared. Click on it and select “Use Text Import Wizard” to place this data more accurately like we did above.

Download Working File

Download the working file from the link below:

DSE-Index.csv


Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share here. Not only how to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned!

We will be happy to hear your thoughts

      Leave a reply