How to clean up asset register data before you import it

To clean up asset register data before you import it, work on a copy and fix five things: one header row and one row per item, tag numbers stored as text so they keep their leading zeros, dates written one unambiguous way, results and equipment classes spelled consistently, and duplicates checked by hand rather than deleted in bulk. You don't need to rename columns or make the file pretty. A little tidying now saves fixing items one by one after the import.

This guide is written for test and tag operators moving a register out of Excel, or out of a tester's export file, into new software. Most of it applies to any equipment register.

General information only. Your test records may be needed to show compliance, so keep an untouched copy of the original file and check the record-keeping rules in your state or territory before you delete anything.

Before you start: work on a copy

Save the original file somewhere safe, give the copy a new name, and only ever edit the copy. Two reasons:

• Some of the fixes below can't be undone once the file is saved. Excel's Remove Duplicates, for example, permanently deletes rows, and Microsoft suggests copying the original data first (Microsoft Support).
• The original is your archive. If a client ever asks about a test from three years ago, you want the file exactly as it was.

If the data comes from tester software, export it fresh rather than reusing an old copy. Our guides cover exporting your records from WinPATS and getting a Metrel ES Manager export into Excel.

Step 1: One header row, one row per item

Import tools read a file as a table: the first row names the columns and every row below it is one record. Anything that breaks that shape causes trouble.

Remove or fix:

• Title rows above the headings ("Register for Smith Builders, updated March").
• Merged cells, especially in the header. Unmerge them and give each column its own heading.
• Totals, subtotals and notes typed under or beside the table.
• Blank rows used as spacers between sites or rooms. Put the site or room in its own column instead.
• More than one table on a sheet, or items split across several tabs. Bring them into one table.
• Hidden rows, columns or sheets. Unhide everything so you can see what will be imported.

Then decide what a "row" means. Many spreadsheets add a new row every time an item is retested, so one kettle can appear five times. For an import, you want one row per item, carrying its latest test date and result. Sort by tag number and then by test date, keep the newest row for each item, and leave the older rows in your archive copy.

Step 2: One client per file

Save each client's items as a separate file, with the site and location (building, floor or room) in their own columns. It keeps each import small enough to check properly, and a mistake in one client's file can't spill into another's.

If your tester software exported every client into one file, filter by client and copy each one into its own workbook. Keep the same headings in every file.

Step 3: Protect your tag numbers

The tag number is how you find the right item when you scan or read a sticker on site, so it has to survive the clean-up exactly as printed.

Excel is the usual culprit. It automatically removes leading zeros from numbers, so 00412 becomes 412, and it converts long numbers to scientific notation such as 1.23E+15. It also keeps only 15 significant digits, so in a number of 16 digits or more, everything after the 15th digit becomes zero (Microsoft Support). Serial numbers are at risk too.

To keep them intact:

• Open CSV files through the import tool, not by double-clicking. In Excel, choose Data > From Text/CSV, then set the tag number and serial number columns to Text before loading.
• Turn off automatic conversions if your version has the setting. Excel for Microsoft 365 and Excel 2024 let you stop it removing leading zeros, shortening long numbers and turning letter-number strings into dates (Microsoft Support).
• Don't rely on a custom number format to bring zeros back. Microsoft notes that formatting won't restore leading zeros that were already removed. Go back to the original export instead.

Check a handful of tag numbers against real stickers or the original file before you move on.

Step 4: Make every date unambiguous

Dates cause more import problems than anything else in a test register. Watch for:

• Day-month order. 03/04/2026 is 3 April in Australia and 4 March in the US. A file that has been through a US-set computer or program can mix both.
• Dates stored as text. If some dates sit on the left of the cell and others on the right, the left-aligned ones are probably text, not real dates.
• Words in date columns, such as "N/A", "new", "see notes" or "Mar 26".
• Impossible dates: a last test in the future, or a next due date before the last test.

The safest fix is to write every date the same way. Our free register template uses year-month-day (2026-10-01), which can't be misread either way. Move anything that isn't a date into a notes column.

If you have both a last test date and a next due date, check a few rows to see that the gap matches the retest interval you meant to use. A due date typed by hand years ago is often wrong.

Step 5: Spell results and classes the same way

Over years of use, a result column collects every variation: Pass, PASS, P, OK, a tick, Passed visual. Equipment class gets the same treatment: Class 1, I, Cl I, C1.

Filter each of these columns to see every distinct value, then replace them with one word each:

• Result: Pass or Fail. If an item failed, put what happened next (repaired, replaced, disposed of) in the notes, not the result column.
• Equipment class: one spelling per type, such as Class I, Class II, Cord set and RCD.

Do the same for anything you'll filter by later, like site and room names. "Kitchen", "kitchen" and "Kitchen " (with a space) can end up as three different rooms.

Step 6: Trim stray spaces and tidy descriptions

Invisible spaces at the start or end of a cell stop values from matching. Excel's TRIM function removes them, leaving single spaces between words (Microsoft Support). Put =TRIM(A2) in a spare column, fill it down, then copy and paste the results back as values.

Then check that every row has a description. A row with only a tag number gives you nothing to search for and nothing for the next technician to recognise on site. Short and plain is fine: "Kettle", "Extension lead 10 m", "Laptop charger". Make, model and serial number can sit in their own columns if you have them.

Step 7: Find duplicates without deleting history

Highlight duplicate tag numbers before you remove anything. A repeated number usually means one of three things:

Avoid running Excel's Remove Duplicates across the whole table. It keeps the first copy of each value it finds and deletes the others (Microsoft Support), which may not be the row you wanted to keep.

Step 8: Check the file against what's on site

A clean file can still be wrong. Before you import, look for items that are no longer there: equipment marked disposed or removed in the notes, sites the client has closed, and whole rooms that haven't been tested in years. Decide with the client whether they belong in the new register. Keep their history in the archive either way.

If you can, spot-check ten items against their tags on your next visit. It's the quickest way to find out whether the register still matches reality.

What you don't need to fix

Some clean-up isn't worth the time:

• Column names. In Koalaris, Lyra AI reads your headings, even odd ones like "Tag #" or "Equip descr", and matches them to the register's fields. You check every match before anything is imported.
• Column order and extra columns. You can skip columns you don't need during the import.
• Formatting. Colours, fonts and column widths are ignored.

A pre-import checklist

• Original saved; working on a copy
• One header row, no merged cells, totals, notes or blank rows
• One row per item, with its latest test
• One client per file, with site and location columns
• Tag and serial numbers stored as text and checked against the original
• Every date in one format, nothing but dates in date columns
• Results and classes spelled one way
• Spaces trimmed; every row has a description
• Duplicate tag numbers checked by hand
• Removed or disposed items agreed with the client

Importing the clean file into Koalaris

Open the Register, choose Import, pick the client and drop in your .xlsx, .xls or .csv file. Check Lyra AI's column matches, change any that are wrong, and import. Items keep their old numbers, so the stickers already on your equipment still find the right item. The two-minute import tutorial shows it end to end, and our guide to moving a register off spreadsheets covers the switch from start to finish.

After the import, open three or four items and compare them with your file. If they match, stop editing the old spreadsheet. Two registers is worse than one.

Common questions

Do I need to rename my columns to match the new software?

Not in Koalaris. Lyra AI matches your column names to the register's fields and you review each match before importing. Other software may expect a fixed template, so check its import instructions.

Should I delete old test results before importing?

Not from your records. Keep the full history in your untouched original file. For the import file itself, keep one row per item with its latest test date and result.

Why have my tag numbers lost their leading zeros?

Excel treats digit-only values as numbers and drops the leading zeros when it opens the file. Open the file through Data > From Text/CSV with the column set to Text, or export it again from your tester software. Formatting the cells afterwards won't bring the zeros back.

Can I import my tester software's export without cleaning it?

Often, yes. Exports from tester software are usually tidier than hand-built spreadsheets. Still check the tag numbers, dates and duplicates, and split the file by client if everything came out in one.

Should I save as CSV or Excel?

Either works for a Koalaris import. CSV is simpler, but opening a CSV by double-clicking it in Excel is where tag numbers and dates get changed. If you edit in Excel, saving the clean copy as .xlsx keeps your text columns as text.

---

Got a clean file? Import it on the Free plan: no card, no end date and no limit on items, with a starter Lyra AI allowance for matching your columns.