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.
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 supports 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.
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.
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.
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 file, values are stored with commas.
Let’s import this CSV file in Cell B3 of our worksheet.
- Choose Data (tab) ➪ Get External Data (Group) ➪ From Text (command). The “Import Text File” dialog box will appear.
- Navigate to the folder where you have saved the CSV file “DSE Index.csv”.
- 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 the “DSE Index.csv” file, the direct “Text Import Wizard-Step 1 of 2” dialog box will appear.
- In the “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.
- 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.
- Click OK, and Excel imports the data. Observe the following two images.
Copying and Pasting Data
Sometimes it is better to try 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 the CTRL + A command and copy it with the command CTRL + C. You can also right-click after selection and click 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.
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 as we did above.
Download Working File
Download the working file from the link below: