How to Import Image Date Taken EXIF Metadata into Excel Power Query
Question details
The user needs to extract and import the 'Date Taken' property of image files into Excel Power Query for data analysis.
- Product
- Excel Power Query
- Device & OS
- not provided
- Scenario
- Importing a folder of image files into Excel to catalog or analyze pictures based on the exact date and time they were photographed.
- Observed behavior
- Power Query's 'From Folder' connector only retrieves standard file-system attributes such as creation date and modified date, missing the EXIF-based 'Date Taken' property entirely.
Download and install a reliable third-party EXIF extraction tool, such as ExifTool or Exif Pilot, to access the embedded image metadata before attempting to import it into Excel.
Extract EXIF Data to CSV and Merge in Power Query
Since Power Query cannot natively read EXIF metadata, you must first extract the Date Taken values into a CSV file using a third-party tool, and then merge that CSV with your folder listing in Excel.
The 'Date Taken' attribute is embedded deep within an image file's EXIF data rather than the standard Windows folder structure. A two-step process utilizing a metadata extractor is the most reliable workaround.
Use your chosen third-party tool (like Exif Pilot) to scan your target image folder. Select the 'Date Taken' and 'File Path' or 'File Name' fields, and export this metadata to a CSV file on your computer.
Open Excel, navigate to the Data tab, and click 'Get Data' > 'From File' > 'From Text/CSV'. Locate your exported metadata CSV file and click 'Transform Data' to load it into the Power Query Editor.
Go back to the Data tab or use 'New Source' inside the Power Query Editor. Select 'From File' > 'From Folder' and choose your image directory to load the standard file properties.
In the Power Query Editor, select your main folder query. Click 'Merge Queries' on the Home ribbon. Select your CSV query from the dropdown, then highlight the 'File Name' or 'File Path' columns in both tables to serve as the matching key, and click OK.
Click the double-arrow expansion icon at the top of the newly created merged column. Check the box for 'Date Taken' to reveal the EXIF data alongside your standard file info. Finally, click 'Close & Load' to output the results to your worksheet.
Try WPS Office for Seamless Spreadsheet Management
While specific Power Query workflows are unique to Microsoft Excel, WPS Office provides a highly compatible, fast, and free alternative for managing spreadsheets, documents, and presentations without the heavy subscription fees.
Frequently Asked Questions
Why doesn't Excel Power Query natively import the Date Taken property?
Power Query's 'From Folder' connector is designed to read standard Windows file-system attributes, such as Date Created and Date Modified. The 'Date Taken' property is stored specifically within the image file's EXIF metadata, which Power Query does not parse by default.
Can I use VBA instead of a third-party tool to get Date Taken metadata?
Yes, you can write an Excel VBA macro that utilizes the 'Shell.Application' object. This allows Windows to loop through a folder and read extended file properties (usually property ID 12 for Date Taken) and pull them directly into a worksheet without external software.
What third-party tools are recommended for exporting EXIF data?
ExifTool is highly recommended for users comfortable with the command line, as it supports almost all image formats. For users who prefer a graphical interface, Exif Pilot is a popular and easy-to-use option that exports seamlessly to CSV.
Will this extraction and merge method work for video files?
Yes, as long as your metadata extraction tool supports the video format (like MP4) and can read its embedded metadata (such as 'Media Created'), you can export that data to CSV and merge it in Power Query using the exact same steps.
