Import and Reconcile Vehicle Maintenance Records in a Spreadsheet

To bring an existing car maintenance log from a CSV file into Excel or Google Sheets safely, do five things. Keep the original file untouched. Map each source column to a planned destination column. Import with explicit settings instead of letting the spreadsheet guess. Compare a trial import against the source. Then reconcile, so that every source row is either accepted, held for review or rejected, with nothing silently lost or changed. A CSV (comma-separated values) file is plain text in which each line is one record and a delimiter, usually a comma, separates the fields. It has no built-in data types, so the spreadsheet decides what each value means when it opens the file. That guessing step is where maintenance records most often get damaged.

Why opening a CSV directly can corrupt service records

When you double-click a CSV, the spreadsheet tries to convert values automatically. For vehicle records, this commonly causes four problems:

Leading zeros are dropped, so an invoice or work-order number such as 004512 becomes 4512.
Long numeric strings, such as some part numbers, can be shown in scientific notation or lose their final digits.
Dates are read using the computer's regional setting, so 03/04/2024 may become March 4 or April 3, and dates that don't fit the setting may stay as text.
Text that looks like a number or a date, such as a part code like 5W30-1 or an entry like 10-12, can be converted into something else.

Exact behavior depends on the application and version. Recent Microsoft 365 versions of Excel let you switch off some automatic conversions in the data options. Using an import tool with per-column settings is more dependable, however, and it works the same way in every application.

Step 1: Preserve and inventory the source file

Copy the original CSV to a separate folder and work only from the copy. Then open the copy in a plain text editor rather than a spreadsheet, and record:

Filename and the date you received or exported it
The exact header row, spelled as it appears
The number of data rows, not counting the header
The delimiter actually used: comma, semicolon or tab
Whether text fields are wrapped in quotation marks, especially fields that contain commas, such as "Oil change, filter, rotation"
The date pattern, for example 2024-03-04, 03/04/2024 or 4 Mar 2024
How distance is written, and whether the unit is miles, kilometers or unstated
A few representative rows, including any that look unusual

The illustrative excerpt below, which is not from a real vehicle, shows the kinds of values that need attention:

Vehicle,Date,Odometer,Service,Shop,Invoice,Cost
Civic,03/04/2024,48210,"Oil change, filter",Main St Auto,004512,64.90
Civic,11/09/2024,52,890 mi,Tire rotation,Main St Auto,004871,25.00
Truck,2024-06-15,81200 km,Brake pads front,,A-2231,310.45

In this excerpt, the second data row has an odometer reading written with a thousands separator. That comma is not inside quotes, so a CSV reader will split it into two fields and shift the rest of the row by one column. The third row mixes date formats and uses kilometers. Invoice 004512 will lose its leading zeros unless its column is imported as text. Problems like these are easier to find in a text editor before the import than afterward.

Step 2: Design the destination columns

Decide what your finished maintenance log should contain before you import anything. A practical layout keeps the original values alongside cleaned ones, so you can always check an interpretation against what the source actually said.

Column	Data type	Purpose
Record ID	Text	Unique reference you assign to each source row, e.g. SRC-001
Vehicle	Text	Nickname, or the 17-character VIN (vehicle identification number) for road vehicles built since 1981
Date (original)	Text	The date exactly as written in the source
Service date	Date	Interpreted date used for sorting and due-date tracking
Odometer (original)	Text	Distance exactly as written
Odometer	Number	Cleaned reading with no separators or unit text
Unit	Text	mi or km
Service description	Text	Work performed
Shop	Text	Who performed the work, or DIY
Invoice ref	Text	Invoice or work-order number, stored as text to keep leading zeros
Cost	Number	Total amount in one currency
Status	Text	Accepted, Review or Rejected
Notes	Text	Reason for any change, hold or rejection

Step 3: Build a mapping worksheet

Add a separate sheet that describes how each source column becomes a destination column. This is a short written plan, and it keeps decisions consistent when you import later batches.

Source column	Destination column	Type	Transformation	Open question
Vehicle	Vehicle	Text	Replace nickname with VIN where known	Which VIN is 'Truck'?
Date	Date (original), Service date	Text, Date	Copy as text; interpret as month/day/year unless the value is in year-month-day form	Is 11/09/2024 November 9 or September 11?
Odometer	Odometer (original), Odometer, Unit	Text, Number, Text	Strip separators and unit text; record the unit	What unit applies to rows with no unit?
Service	Service description	Text	None	None
Shop	Shop	Text	Leave blank cells blank; do not fill them in	Who serviced the truck in June?
Invoice	Invoice ref	Text	None; keep leading zeros	None
Cost	Cost	Number	Remove currency symbols	Is tax included?

Leave the open-question column filled in until each question is answered from a receipt, a shop record or the person who kept the original log. Don't guess an answer in order to finish faster.

Step 4: Import with explicit settings

Use the application's import tool instead of opening the file directly. In Excel, the From Text/CSV option on the Data tab opens a preview where you choose the file origin (encoding), the delimiter and how data types are detected. Choose Transform Data, then set the type of each column yourself. In Google Sheets, File > Import lets you choose the separator and whether to convert text to numbers, dates and formulas. Turning that conversion off keeps values as written. Whichever tool you use:

Set the delimiter to match what you found in Step 1.
Import invoice numbers, part numbers, VINs and all 'original' columns as text.
Import the date column as text first, then create interpreted dates in a separate column. Use a formula or manual entry based on the pattern you identified.
If accented characters or symbols appear garbled in the preview, change the encoding, for example to UTF-8, before importing.

Step 5: Trial-import and compare

Import the file into a new, separate sheet first, not into your working log. Then check a sample against the source text file, including every row you flagged as unusual in Step 1. Confirm the following:

The number of imported rows equals the source row count.
Every row has the same number of fields, with no values shifted into a neighboring column.
Invoice references still show their leading zeros.
Quoted text containing commas stayed in a single cell.
Blank cells stayed blank and were not replaced with zero.
Each interpreted date matches its original text under the pattern you chose.
Odometer readings carry the correct unit.

Write down how the application actually behaved, for example "Excel kept leading zeros only after I set the column to Text." Then repeat the same import settings for the full file. If the trial reveals a problem you can't fix in the import settings, correct it in a copy of the source file and note the correction. Never edit the original.

Step 6: Normalize dates and distances

Due-date tracking only works when every service date is a true date value and every odometer reading uses the same unit. Keep the original text columns and fill the cleaned columns next to them.

Dates: if a value is ambiguous, such as 03/04/2024, decide its meaning from context, such as neighboring entries or the receipt. Mark it Review in the Status column until it is confirmed.
Distance: choose one unit for the log. To convert kilometers to miles, divide by 1.609344. Keep the original reading and unit so the conversion can be checked.
Odometer sequence: sort each vehicle's records by date and check that the readings rise over time. A drop usually means a typo, a swapped date, a unit mix-up or an instrument cluster replacement, so investigate it rather than correcting it automatically.
Cost: store plain numbers in a single currency, and note whether tax is included if your sources differ.

Step 7: Reconcile every record

Reconciliation means proving that every source record has a known outcome. Give each source row a Record ID before or during import, then classify each one as Accepted, Review or Rejected, with a reason in Notes for anything other than Accepted. The totals must add back up to the source count.

Illustrative example: a source file contains 120 records. After import, 115 are accepted, 3 are held as suspected duplicates and 2 are rejected (one empty row and one row for a vehicle you no longer own that you have chosen to archive separately). The check is 115 + 3 + 2 = 120. If the total comes to 119, one record is unaccounted for, and you should find it before going further.

Counts alone are not enough, because a row can survive the import with a damaged value. For a sample of accepted rows, and for every row that needed a transformation, compare the mapped fields with the source text: date, odometer and unit, description, invoice reference and cost.

Handling suspected duplicates

Duplicates often appear when logs from several sources are combined, such as a shop's customer portal export, a handwritten logbook and an app export. Flag a pair as a duplicate candidate when it matches on vehicle, date, odometer reading with unit, description and invoice reference. A close match on only some of these fields is not enough. Two oil changes a few days apart on one invoice may be separate line items, and two shops can use the same invoice number. Resolve each candidate against the original receipt or record before merging or deleting anything. When you do merge, record which Record ID was kept and which was absorbed.

Keep the import auditable

Store the original CSV, the mapping worksheet, the reconciliation counts and the Notes column with the finished log. When you import the next batch, such as next year's shop export, reuse the same mapping and import settings. Give the new records Record IDs that continue the sequence. Check new rows for duplicates against existing records, not just within the new file.

Next step: use the log for due-date tracking

Once dates and odometer readings are clean, you can add columns that calculate when each service is next due by time or distance. For example, add the interval to the last service date or reading for that item. Take the intervals from the owner's manual or maintenance schedule for your specific model year, engine and driving conditions, because they vary between vehicles and can differ for severe-service use such as towing or frequent short trips.