Chapter 47: Getting Data from Pictures, Other Files, and Text Files in Excel

Excel provides several ways to import and extract data from various sources, including pictures, other files, and text files. This chapter covers how to use these features to get data into your Excel worksheets.

1. Getting Data from Pictures

Excel allows you to extract data from pictures, such as scanned documents or photos of printed tables.

• Steps to Import Data from a Picture:
  1. Open Excel: Start by opening the Excel workbook where you want to import the data.
  2. Go to the Data Tab: Click on the Data tab in the Ribbon.
  3. Click on “From Picture”: In the Get & Transform Data group, click on From Picture. You’ll have the option to choose From File or From Clipboard.
     • From File: Choose this option if you have a picture file saved on your computer.
     • From Clipboard: Use this if you’ve copied the picture to your clipboard (e.g., using the Print Screen key or a screenshot tool).
  4. Select the Picture: Navigate to the picture file on your computer or paste the picture from the clipboard.
  5. Review and Insert Data: Excel will use Optical Character Recognition (OCR) to extract the data. Review the data in the Picture Tools pane and make any necessary adjustments. Click Insert Data to place the data into your worksheet.

Pro Tip: Ensure the picture is clear and well-lit to improve the accuracy of the data extraction.

2. Getting Data from Other Files

Excel can import data from a variety of file formats including CSV, XML, and Excel files.

• Importing Data from Another Excel Workbook:
  1. Open Excel: Open the workbook where you want to import data.
  2. Go to the Data Tab: Click on the Data tab.
  3. Click on “Get Data”: In the Get & Transform Data group, click on Get Data, then select From File and choose From Workbook.
  4. Select the File: Navigate to the Excel file from which you want to import data and click Import.
  5. Choose Data to Import: In the Navigator pane, select the sheets or ranges you want to import and click Load.
• Importing Data from a CSV File:
  1. Open Excel: Start by opening a new or existing workbook.
  2. Go to the Data Tab: Click on the Data tab.
  3. Click on “Get Data”: Click on Get Data, then choose From File and select From Text/CSV.
  4. Select the CSV File: Browse to the CSV file and click Import.
  5. Preview and Load: Excel will display a preview of the data. Adjust the delimiter settings if necessary and click Load.
• Importing Data from an XML File:
  1. Open Excel: Open the workbook where you want to import the XML data.
  2. Go to the Data Tab: Click on the Data tab.
  3. Click on “Get Data”: Click on Get Data, then select From File and choose From XML.
  4. Select the XML File: Browse to the XML file and click Import.
  5. Choose Data to Import: Follow the prompts to select and import the data.

Pro Tip: Use the Power Query Editor to clean and transform the data before loading it into your worksheet. This can be accessed from the Get & Transform Data group.

3. Getting Data from Text Files

Importing data from text files (TXT) is useful for handling data exported from other applications.

• Importing Data from a Text File:
  1. Open Excel: Start by opening a new or existing workbook.
  2. Go to the Data Tab: Click on the Data tab.
  3. Click on “Get Data”: Click on Get Data, then choose From File and select From Text/CSV.
  4. Select the Text File: Browse to the text file and click Import.
  5. Adjust Import Settings: Excel will display a preview of the data. You can adjust the delimiter settings (such as comma, tab, or space) to properly format the data. Click Load to import the data into your worksheet.

Pro Tip: For large text files, use the Text Import Wizard for more control over how the data is parsed and formatted.

By utilizing these methods, you can easily incorporate data from various sources into your Excel worksheets, enhancing your ability to analyze and work with diverse datasets.