To open an AIS JSON file in Excel, import it with Power Query: in Excel for Microsoft 365 on Windows, select Data > Get Data > From File > From JSON, choose the file, then expand its records or lists and load the resulting table. This turns structured AIS data—often vessel positions, timestamps, and identifiers—into rows and columns you can sort, filter, and analyze. You will need Excel and a valid JSON file; a copy of the original is a sensible precaution.
What You’ll Need
- Excel for Microsoft 365 on Windows. The instructions below use its Power Query JSON connector. Excel versions and platforms can have different import options.
- The AIS JSON file. Save it on your computer, and make sure you know where it is. AIS commonly means Automatic Identification System, but confirm that the file actually contains the vessel data you expect.
- Enough worksheet capacity for the data. An Excel worksheet can hold up to 1,048,576 rows and 16,384 columns. Large AIS archives may need filtering or a database rather than a single worksheet.
- Permission to use the data. If the file came from a tracking service, check its terms before sharing or publishing imported vessel information.
Step-by-Step
- Check and preserve the source file. Find the file in File Explorer and confirm its extension is .json. Make a copy before editing, moving, or converting it. If you are unsure whether the data is AIS information, open the file in a text editor such as Notepad and look for recognizable field names, for example vessel identifiers, latitude, longitude, speed, course, or timestamps. JSON is text, so seeing braces and quoted field names is normal. Do not treat a text editor preview as a spreadsheet.
- Start a blank workbook. Open Excel and choose Blank workbook, or open the workbook where you want the imported data. If you use an existing workbook, select a blank worksheet or leave room for the result. Power Query can create a new worksheet for the imported table, which is usually safer than placing a large import beside existing calculations.
- Open the JSON import command. On the ribbon, select Data > Get Data > From File > From JSON. Browse to the AIS file, select it, and choose Import. If the file is large, Excel may take a short time to read it. Avoid opening the same file by double-clicking it in File Explorer; that may display raw JSON text rather than create a usable table.
- Inspect the Power Query preview. Excel opens Power Query Editor. The first view may show a single item such as a Record or List, rather than rows of vessel data. This is not necessarily a problem: JSON can contain nested objects and arrays. Click the word List or Record in the preview to drill into that level. Use the breadcrumb near the top of the preview to return to a previous level if you go too far.
- Turn the relevant records into a table. If the preview is a list of vessel messages or position reports, select To Table when that option appears. Review the delimiter and handling options in the dialog, then select OK. If the list contains records, you will generally see a column of Record values. Select the expand icon at the right of that column heading to expose the record fields as separate columns. Select only the fields you need, or keep all fields if you are still exploring the data.
- Expand nested fields one level at a time. Some AIS exports place the useful information inside a nested object, or store several reports in a list. Expand the column that contains the nested value using its expand icon. If a field contains another List or Record, repeat the process at the appropriate level. For example, an outer list might contain messages, each message might have a vessel object, and that object might contain a position. A flat table is easiest to work with when each row represents one message or position report and each column represents one field.
- Review column names and data types. Rename unclear headings by double-clicking a column header. Check that identifiers have not been converted into numbers that lose leading zeros, and that coordinates and speed values have suitable numeric types. Check timestamps carefully: a date-time value may be interpreted using a time zone or regional format you did not expect. In Power Query, use the type icon at the left of a column heading to choose a suitable type, or leave a field as text when preserving its original representation matters.
- Load the result into Excel. When the preview has the fields and rows you want, select Home > Close & Load to load the query as a table in a new worksheet. For more control, select Home > Close & Load To, then choose a table and a new or existing worksheet location. Avoid choosing a cell that would overwrite existing data. After loading, check the first and last few rows, the row count, and several key fields against the source or a known record.
- Save the workbook and keep the query. Save the result as an Excel workbook, such as .xlsx. The workbook stores the loaded table and the Power Query steps, so you can usually refresh the query later if the source file is still available at the same location. Keep the original JSON separately. An .xlsx file is the working spreadsheet; it does not replace the original source file.
For an AIS JSON file hosted at a web address rather than stored locally, the available import route can depend on the address and Excel build. In Power Query, use the web connector only when you are authorized to access the URL and it does not require an unsupported sign-in flow. For a first import, downloading the authorized JSON file and using the From JSON command is often simpler.
Common Mistakes
- Opening the file as if it were already a workbook. JSON is a data format, not an Excel worksheet. Double-clicking it can show a long text string or open it in another application. Use the JSON connector so Power Query can interpret objects and arrays.
- Stopping at the first preview. A List or Record view is a container, not necessarily the final table. Drill into the data, convert lists to tables when appropriate, and expand record columns before loading.
- Expanding every field without checking the structure. Nested data can produce many columns, duplicated information, or more rows than expected. Decide what one row should represent—such as one AIS message—then expand the fields that support that layout.
- Trusting automatic data types without review. Automatic conversion can alter identifiers, interpret dates unexpectedly, or produce errors for mixed values. Inspect key columns before analysis, especially MMSI-like identifiers, coordinates, and timestamps.
- Assuming all AIS JSON files use the same schema. Providers choose different field names and nesting. One file may use a list of reports while another wraps them in a top-level object. Follow the structure shown in the preview rather than expecting a specific column name.
- Trying to load an oversized archive into one sheet. A worksheet has a row limit. If the file exceeds it, filter the query to a date range or relevant vessels before loading, or use a data platform designed for larger datasets.
Tips
- Choose a useful row definition first. For mapping or movement analysis, one row per position report is often practical. For a vessel summary, you may instead want one row per vessel. Those are different transformations, so keep the source query intact and create a separate summary later if needed.
- Retain a source identifier and timestamp. Keeping fields that identify a report and when it was recorded makes it easier to trace a questionable value back to its origin. Preserve the original timestamp text if you need to compare it with the provider’s documentation.
- Import only the fields you need. Removing irrelevant nested fields in Power Query can make the table easier to inspect and reduce workbook size. Keep a copy of the unmodified JSON so you can rebuild the import if your needs change.
- Use Refresh for repeat imports. If the source file is replaced with an updated file at the same path and has a compatible structure, select Data > Refresh All to update the query. If the file moves or its structure changes, edit the query’s source or transformation steps rather than assuming refresh will fix it.
- Check units and coordinate conventions. AIS providers may present speed, course, coordinates, or time in formats that need interpretation. Confirm units and conventions with the data provider before comparing reports or calculating distances.
- Keep Excel responsive. Preview a smaller date range or remove unnecessary fields before loading a very large archive. You can also use Power Query transformations to filter rows before they reach the worksheet, rather than loading everything and deleting rows afterward.
Troubleshooting
- There is no From JSON command. Confirm that you are using desktop Excel for Microsoft 365 on Windows and look under Data > Get Data > From File. Ribbon layouts and connector availability can differ by platform, update channel, or organization policy. Excel for the web does not provide the same desktop import workflow. On Mac, check the Get Data options in your installed Excel build; if JSON is not listed, use Excel for Windows or convert the file with an approved tool, then import the resulting CSV or workbook.
- Excel reports invalid JSON or cannot parse the file. The file may be incomplete, contain an error, or include text before or after the JSON value. Open a copy in a text editor and check that the opening and closing braces or brackets match. If the file was downloaded, download it again from the source. Do not simply rename a malformed file or remove characters at random; that can silently change the data.
- The preview shows one column, List, or Record. That can be the correct starting point for nested JSON. Click the List or Record value, use To Table for a list when available, and expand Record columns. If you reach a list of values rather than records, inspect what each value represents before converting it.
- Some columns show Error or null. A field may be missing from some messages, contain mixed formats, or fail an automatic type conversion. In Power Query, inspect the error details and the original value. Set the column to text temporarily to preserve mixed data, or correct the type only after confirming the intended format. Null generally means a value is absent; it does not automatically mean zero.
- Dates or numbers look wrong. Regional settings affect how text dates and decimal separators are interpreted. In Power Query, use the column’s data type menu and, where offered, change the locale used for conversion. Compare a known timestamp and coordinate with the original JSON before relying on calculations. Account for time zone information rather than assuming all times are local.
- The import is too slow or will not load. Narrow the file to a smaller period or subset before loading, remove unused fields, and filter rows in Power Query. If the final table still exceeds worksheet limits or the workbook becomes impractical, use a database or analytics tool built for larger datasets.
Frequently Asked Questions
Can I open an AIS JSON file directly in Excel?
Yes. In desktop Excel for Microsoft 365 on Windows, use Data > Get Data > From File > From JSON. This imports the structured data through Power Query rather than treating the file as a ready-made worksheet.
Will Excel automatically make one row per vessel?
Not always. The imported row layout depends on how the JSON is structured and which level you convert to a table. Many files contain multiple position messages per vessel, so the natural result may be one row per message, not one row per vessel.
Can I import a JSON file that is larger than an Excel worksheet?
You can sometimes use Power Query to filter data before loading, but the final worksheet cannot exceed 1,048,576 rows. For larger datasets, filter to a smaller range or use a database or another tool designed for large data volumes.
Can I convert the imported table back to JSON?
Excel’s JSON import creates a query and table; it is not a one-click round-trip editor for the original file. Keep the original JSON. If you need to produce JSON again, use a suitable export method or tool and verify that its output matches the required schema.
What does AIS mean in an AIS JSON file?
It commonly means Automatic Identification System, the maritime system used to broadcast vessel information. However, file names are not proof of their contents. Check the provider’s documentation and inspect the fields to confirm what the data represents.
Will the query update if the JSON changes?
Usually, if the source remains accessible at the same location and its structure remains compatible, use Data > Refresh All. If the file is moved, renamed, or its fields change, update the query source or transformation steps and check the refreshed table.
Related
- Where Is the Ungroup Button in Excel?
- Unmerge Cells in Excel: Find the Command and Use It
- Where to Find Define Name in Excel and Manage Names
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Double Space in Microsoft Word on Any DeviceTo double-space text in Microsoft Word, select the text—or press Ctrl+A on Windows or Command+A on Mac to…
- Can You Merge Cells in Google Sheets? How It WorksYes, you can merge cells in Google Sheets in a web browser. Select the cells, then choose Format…
- Excel Shortcut Cheat Sheet for Windows and MacAn Excel shortcut cheat sheet is a quick reference to keyboard commands for editing cells, moving around worksheets,…
- How to Add Page Numbers in Word: Step-by-Step GuideTo add page numbers in a Word document, open the file, select Insert > Page Number, choose a…
- Delete a Page Break in Word Without Losing Your TextTo delete a manual page break in Microsoft Word, show formatting marks, locate the line labeled Page Break,…
- How to Hide Columns in Excel on iPad Without Deleting DataTo hide a column in Excel on iPad, tap its letter at the top of the worksheet to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.