Power Query Problems

Automatically Import Semicolon-Delimited Web Data into Excel

Steve KSteve K Nov 22, 2026 868 views

Question details

Import a semicolon-delimited text file directly from a web URL into Excel and refresh it without repeating the manual conversion.

Automatically Import Semicolon-Delimited Web Data into Excel
Product
Microsoft Excel Power Query
Device & OS
not provided
Scenario
A user needs an Excel table that can be updated from an online text file.
Observed behavior
The desired result is a refreshable query that separates the web data into columns using semicolons.
Before you start

Find the direct URL of the text file and check that it opens in a browser. Make sure your version of Excel includes Power Query; menu names can vary by version.

Solution 1Recommended

Create a refreshable web query in Power Query

Use a blank query to fetch the text file directly and parse it with a semicolon delimiter.

1
Create a blank query

In Excel, select Data > Get Data > From Other Sources > Blank Query to open Power Query Editor.

2
Enter the web import formula

In Power Query Editor, select Home > Advanced Editor. Replace the existing code with: let Source = Csv.Document(Web.Contents("https://www.amfiindia.com/spages/NAVOpen.txt"), [Delimiter=";", Encoding=65001, QuoteStyle=QuoteStyle.Csv]) in Source. Replace the example URL with your file URL if needed, then select Done.

3
Connect and check the preview

If prompted for credentials, choose Anonymous for a publicly accessible file and select Connect. Check that the preview separates the data into columns. Use the Home or Transform commands to remove unwanted rows or adjust column types as needed.

4
Load the query

Select Home > Close & Load to add the query results to the workbook.

5
Set refresh options

In Excel, open Data > Queries & Connections, right-click the query, and choose Properties. Under Usage, enable Refresh data when opening the file or Refresh every and set an interval. You can also use Data > Refresh All to update it manually.

Create a refreshable web query in Power Query
Refresh and file structure: Interval refreshes require the workbook to be open. If the source contains section headings or rows with different numbers of fields, review and adjust the query transformations before relying on the loaded table.
Free Microsoft Office alternative

Work with Imported Text Data in WPS Spreadsheet

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Excel format compatibility for moving existing workbooks. It does not fix Excel Power Query or guarantee an equivalent refreshable web connection.

Open and work with semicolon-delimited text data in a spreadsheetCompatible with Microsoft Excel XLS and XLSX filesFamiliar spreadsheet tools make migration easierFree and lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

How do I refresh the imported data?

Select Data > Refresh All in Excel, or configure refresh options by right-clicking the query in Data > Queries & Connections and choosing Properties.

Will the query refresh when the workbook is closed?

The query's interval refresh requires the workbook to be open. To update it when you next open the file, enable Refresh data when opening the file in the query properties.

What authentication should I use for a public text file?

Choose Anonymous when Power Query prompts for credentials. If the source requires a sign-in, use the authentication method provided by the source instead.

Why are some rows not arranged like the rest of the data?

Some text files include section headings or rows with different numbers of fields. Open the query in Power Query Editor and inspect or remove those rows using the Home and Transform commands before loading.