Microsoft have just announced the IMPORTTEXT and IMPORTCSV functions for Excel.
In this article, I'll take a look at what these functions are and how I think we will be able to use them once they are released into the current channel
The IMPORTTEXT function lets you import data from.txt, .csv, and .tsv files directly into a dynamic array in Excel.
The syntax is: =IMPORTTEXT(path,[delimiter],[skip rows],[take rows],[encoding],[locale])
Video from Microsoft Insider Blog
IMPORTCSV is a version of IMPORTTEXT, designed specifically for importing data from .csv files.
The syntax is: =IMPORTCSV(path,[skip rows],[take rows],[encoding],[locale])
Video from Microsoft Insider Blog
These two import functions will provide a new way to bring data into Excel. Being dynamic array functions, they will bring the data directly into the grid and spill the results.
We will then be able to use the skip rows and take rows parameters, along with other Excel functions like TAKE, DROP, CHOOSECOLS, CHOOSEROWS etc to perform some simple transformations.
PowerQuery is a pretty comprehensive ETL (Extract, Transform & Load) tool built into Excel. As such, it can work with a wide range of data sources and can perform powerful transformations. The final dataset can be loaded into an Excel Table, into a Pivot Table or can be added into the Data Model.
IMPORTTEXT and IMPORTCSV are much simpler tools, and being dynamic array functions, they will not be able to be imported into an Excel Table.
I am a strong advocate of modellers using both Excel Tables and PowerQuery to source, transform and store their data. I don't see these two functions changing that any time soon.
IMPORTTEXT and IMPORTCSV are currently in the Beta Channel (Build 18604.20002) or later. Microsoft haven't yet announced when they will come to the current channel.
This series of articles focuses on new features soon to be released on Excel. This is based on announcements by the Microsoft Office Insider team.
See other articles in our Future of Excel series here.