Cleaning Up a Messy Spreadsheet Before You Import It

Most vendor spreadsheets were never designed. They grew, show by show, one column added for whatever that week needed. Cleaning one up before you import it is a half hour that saves you months of bad data.

If you have been running your business on a spreadsheet for more than a season, it is not really one spreadsheet anymore. It is a stack of decisions: a column someone added for a specific buy, a condition note that means something only you remember, a price column that is sometimes a cost and sometimes a sale price depending on the row. None of that is a problem while you are the only one reading it. It becomes a problem the moment you try to import it somewhere else, because an importer needs the same thing in the same place every row, and your sheet has never had to promise that.

The good news is that cleanup is mechanical, not hard. You are not re-entering your inventory. You are telling your existing data to sit up straight. CSV import is free, so there is no reason to keep running the business out of a file that only makes sense to you.

Map your columns to what the importer expects first

Before you touch a single row, figure out what fields the importer actually wants: card name, set, number, condition, quantity, cost, and maybe a tag or note. Lay your spreadsheet's columns next to that list and mark which column maps to which field. You will usually find three kinds of mismatch:

• Missing fields. Maybe you never tracked cost per card, just a running total. You will need to either back it out or import without it and fill it in later.
• Merged fields. One column holding "Charizard Base Set Holo NM" when the importer wants name, set, printing, and condition as separate values.
• Renamed fields. A column header like "Paid" instead of "Cost," or "Grade" instead of "Condition." Harmless to a human, invisible to an importer unless you rename the header or map it explicitly.

Write the mapping down somewhere, even if it is just a note at the top of the sheet. You will use it again for every batch after this one, and if you ever hand the file to someone else to clean up, the mapping is what keeps their work consistent with yours.

It helps to do this pass with the actual column headers visible, not from memory. A sheet that has been through three vendors or two seasons of "quick fixes" rarely has the headers it started with, and a column named "Notes" by the third owner might be functioning as the condition field by the time you look at it.

Split quantity from price, and price from cost

The two mixups that cause the most damage are quantity hiding inside a price cell and cost getting confused with sale price.

Quantity hiding in price looks like a cell that reads "$12 x 3" because that was faster to type at the time than three separate rows or a quantity column. An importer reads that as text, not a number, and the row either fails or imports as garbage. Split it into a quantity column and a single unit price before you import, even if that means expanding one row into three.

Cost versus sale price is a quieter version of the same problem. If a "Price" column sometimes means what you paid and sometimes means what you sold it for, your margin math is wrong before you have typed a single row into a new system. Decide now: cost is what you paid to acquire the card, nothing else goes in that column. If you track a separate sale price history, that is a different field entirely, not a value that overwrites cost.

Normalize condition and set names

A spreadsheet accumulates its own dialect over time. "NM," "Near Mint," "nm," and "Mint-" might all mean the same thing to you, but they are four different strings to a computer, and four different buckets once imported. Same problem with set names: "Base," "Base Set," and "BS" all pointing at the same set.

Pick one spelling for each condition grade and one name for each set, then use find and replace across the whole file to collapse the variants down to that one form. This is tedious for exactly one spreadsheet and then never again, because once your inventory lives in an app with a fixed condition list and catalog of set names, this kind of drift cannot happen going forward.

Find and kill duplicate rows

Duplicates creep in from re-entering a card after a show because you could not find the original row, from copy-pasting a block to add a similar card and forgetting to change every field, or from two people updating the same sheet on different days. Sort by card name and set, then scan for rows that look nearly identical. A quick way to catch exact duplicates: sort the whole sheet and look for consecutive rows where every column matches except maybe a date.

Decide the rule before you start deleting: if two rows describe the same physical card, they should become one row with the combined quantity, not two separate rows that double-count your stock. Importing duplicates does not just clutter the app, it overstates your inventory value and your true quantity on hand.

Watch for near duplicates too, not just exact ones. A card entered once as "Charizard" and once as "Charizard Holo" will not show up as an exact match in a sort, but they may well be the same physical card described two different ways. This is exactly why normalizing set and condition language first matters: it surfaces near duplicates as exact ones, which makes them easy to find and merge instead of easy to miss.

Test the import on a small sample first

Once the file looks clean, do not run the whole thing at once. Cut out a sample of ten or so rows that represent your weird cases: a card with no cost recorded, a graded card, a bulk lot, whatever your inventory actually contains that is not a plain single. Import that sample first and check every field landed where you expected.

This is where you catch the mapping mistakes that are invisible until you see the result: a condition that imported as blank, a cost that landed in the wrong column, a set name that did not match the catalog and created a duplicate entry instead of linking to the existing one. Fix the file, re-test the sample, and only run the full import once the sample looks right. CardOps' CSV import is free to use as many times as you need to get this right, so there is no reason to go straight for the full file and hope.

From there, the file stops being special. New cards get added by scanning them in, not by editing a spreadsheet, and your condition and set data stay consistent because the app enforces it instead of you remembering it row by row.