How to Import a Coin Inventory Spreadsheet

To import a coin inventory spreadsheet, first clean it so there is one coin (or one lot of identical coins) per row and one fact per column: title, year, mint mark, grading service, grade, cert number, quantity, value per coin, cost per coin, and location. Then save it as CSV, match each column to a field in your new software, read the preview for errors and duplicates, and import a small test batch before the whole file.

Most of the work is the cleanup. An old spreadsheet has three spellings of PCGS, grades in the title, costs with "ea" after them, and cert numbers that Excel has rounded. This guide fixes them one column at a time.

Step 1: Make a copy and export to CSV

Never clean your only copy. Save the workbook under a new name, and do the cleanup in the copy.

If your stock is in other coin software, export the inventory to CSV from that program. If it is in Excel or Google Sheets, save it as CSV at the end. CSV keeps only the values. Formulas become their results, and colors and comments are lost.

Step 2: One coin per row, one fact per column

The most common problem in a dealer's spreadsheet is a single cell that holds a whole coin:

Item
1881-S Morgan $1 PCGS MS65 #12345678

Software cannot sort, search, or check that cell. Split it into columns:

Title	Year	Mint mark	Denomination	Service	Grade	Cert #
1881-S Morgan Dollar PCGS MS65	1881	S	$1	PCGS	MS65	12345678

Keep a full title column as well. The title is what buyers read, and the separate columns are what you sort and filter by. The cert numbers in this guide are made up.

Lots and rolls

A row can hold more than one coin when the coins are the same, such as a roll of 1964 Kennedy halves. Give it a quantity and give the value and cost per coin:

Title	Quantity	Value per coin	Cost per coin
1964 Kennedy Half 90% Silver	20	15.00	12.50

A common mistake is to type the lot total into the value column. Twenty halves at a value of $300 each is $6,000 of stock on paper, when the lot is worth $300. Divide first.

Step 3: Clean the grading service column

Look at every distinct value in the column. In Excel, filter the column and read the list. A typical list looks like this:

• PCGS, pcgs, P.C.G.S., PCGS (CAC)
• NGC, N.G.C., NGC
• raw, Raw, ungraded, none, N/A, blank

Pick one spelling for each service and use find-and-replace. Put a CAC sticker in its own column, not in the service name, since the coin is graded by PCGS or NGC and CAC is a second opinion on top. Decide what a raw coin is called, such as "Raw", and use that for every ungraded coin.

Step 4: Clean the grade column

Grades come in many formats: MS65, MS-65, 65, Gem BU, Unc. Choose one format and use it on every row. Most dealers write the prefix and the number with no space or dash, the way it appears on most labels: MS65, PR69, AU58.

Move designations to their own column. "MS64 DMPL", "MS65RD", and "PR69 DCAM" each hold two facts, the grade and the designation. Split them:

Grade before	Grade	Designation
MS64 DMPL	MS64	DMPL
MS65RD	MS65	RD
PR69 DCAM	PR69	DCAM

Details grades stay as written, with the problem: "XF Details, cleaned". Do not shorten them to "XF". The problem is part of what the coin is.

Step 5: Protect the cert numbers

Cert numbers are the most valuable column in the file. They prove the grade, they let a buyer check the coin, and they find duplicate rows. Excel damages them in three ways:

1. Leading zeros are dropped. A cert that starts with 0 loses the 0.
2. Long numbers turn into scientific notation, such as 1.23457E+11, and the digits after the first fifteen are lost.
3. Text with a dash can turn into a date. The same happens to box codes such as 3-12, which can become 12-Mar.

Format the column as Text before you paste or type into it. If the damage is already done, go back to the source, such as the old export or the holders. Check five or ten certs against the coins before you import.

Clean the format too. Take out "#", "Cert:", and stray spaces. NGC numbers often have a dash and a suffix, such as 1234567-001. Keep it, since it is part of the number.

Step 6: Clean costs, values, and dates

Money columns should hold a plain number per coin:

Before	After	Why
$1,250.00	1250.00	Plain number. Many importers read the $ and the comma, but not all do.
45 ea	45.00	Remove the text.
300 (lot of 20)	15.00	Value per coin, with 20 in the quantity column.
?	(blank)	Unknown cost is blank, not zero.
see notes	(blank)	Move the note to a notes column.

If you bought a collection for one price and never split the cost across the coins, do that now, before the import. The guide to allocating cost on a collection shows how.

Dates should use one format on every row. 2024-03-15 is the safest, because it cannot be read two ways. 03/04/2024 is March 4 in the US and April 3 in much of the world.

Step 7: Clean locations

If your spreadsheet records where each coin is, make the spelling match exactly. "Safe 1", "safe1", "SAFE #1", and "Safe One" are four places to an import. Split a combined location such as "S1-Sh3-B12" into a safe, shelf, and box column. The guide to storage locations covers how to set up the codes.

Step 8: Find duplicates before the software does

Sort by cert number and look for the same number twice. Usually it is a coin you sold and never deleted, or one you entered twice. Keep the correct row. Do the same with your stock numbers (SKUs).

A worked example: five messy rows

Here are five rows from a typical dealer spreadsheet:

Item	Grade	Cost	Value	Where
1881-S Morgan PCGS #12345678	ms 65	$48	$95	safe1
64 Kennedy halves roll	BU	250 lot	300	Safe 1
1916-D Merc dime, cleaned	NGC G Details	?	650	SAFE #2
1893-O Morgan NGC 1234567-001	XF Details Cleaned	140	260	safe 2
1923 Peace raw	AU55?	22	38	Desk

And the same rows after cleanup:

Title	Year	Mint mark	Service	Grade	Cert #	Qty	Cost	Value	Safe
1881-S Morgan Dollar PCGS MS65	1881	S	PCGS	MS65	12345678	1	48.00	95.00	1
1964 Kennedy Half 90% Silver	1964		Raw			20	12.50	15.00	1
1916-D Mercury Dime NGC G Details Cleaned	1916	D	NGC	G Details, cleaned		1		650.00	2
1893-O Morgan Dollar NGC XF Details Cleaned	1893	O	NGC	XF Details, cleaned	1234567-001	1	140.00	260.00	2
1923 Peace Dollar	1923		Raw	AU55		1	22.00	38.00	Desk

What changed:

• Row 1: the cert moved out of the title, the grade got one format, and "safe1" became 1.
• Row 2: "64" became 1964, "BU" came out because the coins are raw, and the lot cost and value were divided by 20.
• Row 3: the service and grade moved out of the title. Read the cert number off the holder and add it before you import. The cost is unknown, so it is blank.
• Row 5: "AU55?" became AU55 on a raw coin. Give the desk a location code, or put the coin away before the import.

Step 9: Import a test batch first

Import 20 rows that cover the hard cases: a lot row, a details coin, a raw coin, and a row with no cost. Open each new coin and check the title, grade, cert, cost, and location. If a field is wrong on all 20, the column match is wrong. Undo the test, fix the file, then import the whole thing.

How CoinManagerPro imports a coin spreadsheet

CoinManagerPro coin import reads CSV and TSV files with a header row, up to 100 MB. Commas, tabs, semicolons, and pipes all work as separators. Save an Excel file as CSV first. Imports cost no credits.

Common column names match coin fields for you, such as Stock #, Cert #, Grader, TPG, Our Cost, and Vendor. You can change any match or leave a column out, and save the mapping as a template for the next file from the same software. If your workspace has custom fields for coins, a column with the same name as a custom field matches it. A number or choice value that does not fit the field shows a warning, and the coin imports without that value.

The preview checks each row the way this guide does:

• Grading service values are matched without case, spaces, or punctuation, so "n.g.c." reads as NGC, and "ungraded", "none", and "N/A" read as Raw. A service that is not in the list is an error on that row.
• Money columns read "$1,250.00". Text that is not an amount, such as "45 ea", is an error on that row.
• Quantity must be a whole number.
• Year reads 1881 from "1881-S" and 1880 from "1880/79". A year it cannot read is a warning, and the coin imports without a year. The mint mark needs its own column.
• A value that looks like a lot total for a row with a quantity gets a warning that shows the value per coin if it is a lot total.
• A row with no title is an error.

The grade imports as written, so set one format before you upload.

Rows with errors stay out of the import, and you can download an error report, fix the rows, and import them in a new file. The preview finds rows whose cert number or SKU is on a coin you have or on an earlier row of the file. Cert numbers compare without case or spaces. You skip the duplicates, or import them with new SKUs. A row with no SKU gets one from the import. If you upload a file you imported before, the preview tells you.

Rows can carry a warehouse, zone, safe, shelf, and box. For a location name you do not have, you create it, use the default location, or leave those rows out. Cost columns import only for members with the View cost permission.

The import runs in the background, and if it stops you can retry from where it stopped. Undo removes the coins an import added. It keeps any coin that was sold, invoiced, put on a memo or listing, or changed after the import, and tells you why.