IT Operations, Risk and Audit · #95

Online Asset Inventory CSV Cleaner

Makes inventory tables merged from different sources uniform and duplicate-free.

  • Data is processed in your browser and not sent to a server
  • Export: CSV, XLSX

Tool

First row header. Data does not leave the browser.

Normalised header name; empty means no duplicate check.

What does this tool do?

When a purchase sheet, a discovery tool and an AD export are merged, the same device appears several times with different spellings: upper/lower case serials, MACs with dashes or colons, dates as day.month.year or year/month/day. The tool fixes these differences in the browser and keeps all values as text so Excel does not strip leading zeros.

How to use it

  1. Paste the CSV with its header row (separator is detected).
  2. Enter the column that identifies duplicates (normalised name, e.g. serial_no) and tick the options.
  3. Press Run; download the clean table as CSV or XLSX.

Fields

Inventory CSV
First row header. Data does not leave the browser.
Unique column
Normalised header name; empty means no duplicate check.
Remove duplicate rows (first kept)
Format MACs as AA:BB:CC:DD:EE:FF
Format dates as YYYY-MM-DD
Uppercase serials and tags

Worked example

InputValue
Inventory CSV
Asset Tag,Hostname,Serial No,MAC Address,IP,Purchase Date
00123, srv-01 ,abc123,001a2b3c4d5e,10.0.0.5,05.03.2021
00124,srv-02,ABC124,00-1A-2B-3C-4D-5F,10.0.0.6,2022/1/7
00125,srv-01-old,abc123,001A2B3C4D60,10.0.0.300,31.02.2020
,,,,,
Unique columnserial_no
Remove duplicate rows (first kept)✓
Format MACs as AA:BB:CC:DD:EE:FF✓
Format dates as YYYY-MM-DD✓
Uppercase serials and tags✓

Expected result: Headers become asset_tag, hostname, serial_no, mac_address, ip, purchase_date; 2 clean records remain. The second row with serial abc123 was removed as a duplicate; 10.0.0.300 reported as invalid IP and 31.02.2020 as invalid date; the blank row was deleted.

Asset tag 00123 keeps its leading zeros. Invalid values are not auto-corrected; check them at the source. To choose which record stays, sort the table by the most reliable source first.

Second scenario

InputValue
Inventory CSV
Hostname;IP
PC-01;192.168.1.10
PC-02;192.168.1.11
Remove duplicate rows (first kept)–
Format MACs as AA:BB:CC:DD:EE:FF✓
Format dates as YYYY-MM-DD✓
Uppercase serials and tags✓

Expected result: A two-row semicolon list needs no fixes; headers were lower-cased.

Method and assumptions

Headers are lower-cased, de-accented and underscored. Extra spaces in cells are trimmed. MAC: in columns whose header contains 'mac', 12 hex characters are written with colons. Date: in columns containing date/purchase/warranty, DD.MM.YYYY and YYYY/M/D are converted to ISO; non-existent dates (31.02) are errors. IP columns are validated.

Using the result and troubleshooting

Use the clean table for the Hardware Lifecycle tool or a CMDB import. In XLSX all cells are text, so Excel does not turn serial numbers into scientific notation.

Limits and common mistakes

  • No fuzzy matching (e.g. 'SRV01' vs 'srv-01'); only case-insensitive exact matches in the chosen column.

Frequently asked questions

Why does Excel strip leading zeros?

Opening a CSV by double-click lets Excel convert number-like values. The XLSX output writes values as text, so this does not happen; for CSV use Excel's 'Data > From Text' import.

Related tools and service

Related service: Technical Support and Scheduled Maintenance

Systems online tools · See all online tools