Excel's New IMPORTCSV and IMPORTTEXT Functions: Simplify File Imports

Introducing IMPORTCSV and IMPORTTEXT

• 💡 Excel has released two new functions, IMPORTCSV and IMPORTTEXT, making data imports from CSV and text files significantly easier and more transparent.
• 🚀 These functions are currently available for Microsoft 365 users on the beta channel.
• ✅ Unlike Power Query, these new functions embed the import logic directly into the formula, making the structure explicit and easier to troubleshoot.

Using IMPORTCSV

• 🎯 The IMPORTCSV function is straightforward for comma-separated value files.
• 🔑 The primary argument is the file path, which can be a local path or a URL pointing directly to the file.
• 📄 Upon entering the formula, data is directly imported into the Excel grid without preview windows or hidden Power Query steps.
• 🔄 Imports made with these functions can be refreshed via the Data tab's Refresh All feature, similar to Power Query.

Utilizing IMPORTTEXT

• 🧩 The IMPORTTEXT function offers more flexibility for various text file formats.
• ⚙️ Optional arguments include specifying the delimiter (e.g., comma, space), defining fixed-width columns, skipping rows, and setting the take rows count.
• 🌐 Advanced options also allow for specifying file encoding (e.g., UTF-16) and regional formatting to ensure correct data interpretation.

Advanced Data Manipulation

• 📊 Since IMPORTCSV and IMPORTTEXT return arrays, their output can be directly used with other Excel functions like GROUPBY for sorting, summarizing, or extracting data.
• 🔗 The LET function can be used to organize complex formulas, making them more readable and efficient by defining variables for imported data and intermediate steps.
• 📈 This formula-driven approach allows for dynamic data summarization directly from external text files, with updates available via 'Refresh All'.

When to Use Which Tool

• ➡️ Use IMPORTCSV/IMPORTTEXT for structured, straightforward imports where data is already clean and transparency is key.
• ➡️ Use Power Query for complex data transformation, merging multiple files, connecting to diverse data sources (databases, APIs), or when dealing with messy data.