How to Import Multiple CSV URLs into Excel Power Query
Question details
The user needs to import and combine monthly CSV files hosted across a list of multiple URLs into a single dataset for data analysis.
- Product
- Excel Power Query
- Device & OS
- not provided
- Scenario
- Consolidating monthly data reports stored online in CSV format into one unified workbook query.
- Observed behavior
- The goal is to automatically retrieve each CSV file from a dynamic list of URLs and append them together, rather than importing each file manually.
Ensure you have a stable internet connection and that all CSV URLs in your list are publicly accessible or use the same authentication method.
Combine CSVs using a Custom Power Query Function
Create a URL list and build a custom function in Power Query to automatically retrieve and append all online CSV files.
This method requires turning a standard web import query into a reusable function. It is highly efficient for pulling data from a large list of web links automatically.
Navigate to Data > Get Data > From Web. Enter your first CSV URL and load the data. Repeat this for a second URL, then use 'Append Queries' to ensure their columns align correctly.
In your spreadsheet, create an Excel table containing all the CSV URLs. Select any cell inside the table and click Data > From Table/Range to bring the list into the Power Query Editor.
Right-click your initial single-URL test query and open the Advanced Editor. Replace the hardcoded URL string with a parameter (e.g., `(CSV_URL as text) =>`) to convert the query into a custom function.
Select your URL list query. Go to Add Column > Invoke Custom Function on the top ribbon. Choose your new function and map it to the column containing your URLs.
Click the expand icon (two arrows) on the newly generated column to extract and merge all the CSV tables. Finally, click Close & Load to bring the consolidated data into Excel.
Download Locally and Use the Folder Connector
If remote URL downloads time out or fail frequently, download the files locally and use the From Folder method.
Handle Your CSV Data Analysis seamlessly with WPS Office
If you are struggling with complex Microsoft Excel features or expensive subscriptions, consider switching to WPS Office. It is a highly compatible, free alternative that offers a lightweight installation, familiar user interface, and seamless migration for your data analysis tasks.
Frequently Asked Questions
Why do my CSV URLs fail to load in Power Query?
Connections can fail due to network timeouts, firewall blocks, or missing authentication. Ensure the URLs are publicly accessible, do not require a web-form login, and that your proxy settings allow Excel to access the internet.
Can I automate downloading CSV files to a local folder?
Yes, you can write an Excel VBA (Visual Basic for Applications) macro to loop through a range of URLs, retrieve the files via HTTP requests, and save them to a specified local directory.
How does Power Query handle CSV files with different column headers?
Power Query appends data based on exact column names. If headers differ between your monthly CSV files, Power Query will create new separate columns, resulting in null values where the data doesn't align. You may need to rename columns in an intermediate transformation step.
