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
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
- Paste the CSV with its header row (separator is detected).
- Enter the column that identifies duplicates (normalised name, e.g. serial_no) and tick the options.
- 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
| Input | Value |
|---|---|
| Inventory CSV | |
| Unique column | serial_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
| Input | Value |
|---|---|
| Inventory CSV | |
| 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
- Hardware Lifecycle Refresh PlannerDerives from the inventory which device needs refreshing when, and the yearly refresh budget.
Related service: Technical Support and Scheduled Maintenance