Automatically Import Semicolon-Delimited Web Data into Excel
Question details
Import a semicolon-delimited text file directly from a web URL into Excel and refresh it without repeating the manual conversion.

- 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.
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.
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.
In Excel, select Data > Get Data > From Other Sources > Blank Query to open Power Query Editor.
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.
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.
Select Home > Close & Load to add the query results to the workbook.
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.

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.

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.




