Python in Excel can handle small-to-medium data directly input or imported into Excel, as well as large data imported via Power Query (or small-to-medium data).
Input or Import Data Directly into Excel
You can input data directly into an Excel worksheet or copy it from other worksheets/workbooks. For file data, open the File → Open → Browse menu in Excel to launch the "Open" dialog box (Figure 4-1). Navigate to the file’s directory, then select the file type from the dropdown list above the "Cancel" button and choose the file to import. As shown in Figure 4-1, Excel supports opening many types of data files.
Import Data via Power Query
For large data (e.g., millions or tens of millions of rows), use Power Query for import. To import via Power Query, click the Get Data button in the Data tab to expand the menu (Figure 4-2). Combine different menu options to import different data types.
Figure 4-2
Below is an example of importing an existing Excel file:
Copy the Samples directory from the book’s examples to your computer.
In Excel, go to Data → Get Data → From File → From Excel Workbook to open the "Import Data" dialog box. Locate the target file (e.g., ID Card Numbers.xlsx in the ch04 folder) and click Import.
After clicking Import, the "Navigator" dialog box appears (Figure 4-3), listing all worksheets in the file and previewing data for the selected worksheet. Choose the data to import, then click Transform Data (to modify data in Power Query Editor) or Load (to load directly).
If you click Transform Data, the selected data opens in Power Query Editor (Figure 4-4), which provides tools for merging, splitting, column/row operations, value cleaning, etc. If you are familiar with Power Query, use these tools; if not but proficient in Python’s pandas, use built-in Python (which offers an alternative to Power Query for large data processing).
Power Query and pandas are functionally aligned—both are designed for large data, offer fast computation, and encapsulate rich data processing tools.
Click Close & Load in Power Query Editor to load data. As shown in Figure 4-5, Excel adds a new worksheet (named after the selected table in Figure 4-3) before the current worksheet. Note: Imported data is displayed as a Super Table, which has special considerations for reference. A "Queries & Connections" panel on the right shows related information.
Example: Import OData Source Data
To import OData data:
Go to Data → Get Data → From Other Sources → From OData Feed (Figure 4-6).
Enter the OData URL (e.g., https://services.odata.org/northwind/northwind.svc) and click OK.
In the "Navigator" dialog box (Figure 4-7), select the Categories table and click Load.
Excel adds a new worksheet named Categories (Figure 4-8) with the imported data.