Return to main CellarTracker site...Front Page | Recent Changes | Title Index | Help
Difference from prior author
revision.
major diff minor diff hide diffThis document describes the CellarTracker bulk import facility and how it can
be used to migrate existing wine inventory information stored in spreadsheet,
database or other cellar management applications. The document is broken down
into four major sections:
<TableOfContents>
Customers who have used the bulk import facility have found that it generally
speeds up the process by a factor of 10-15 as compared to manually entering a
cellar on the site. That said collectors with large cellars should still expect
to spend at least a few hours. I sincerely believe you will find the effort to
be well worth your time!
= Readiness Checklist =
Here are a few basic steps to follow to ensure that your import goes
smoothly:<br>
1. Please familiarize yourself with the [BulkImport#h2 high-level overview]
of the import process.
1. Please make sure to browse to [http://www.cellartracker.com
www.CellarTracker.com] and press the
[http://www.cellartracker.com/terms.asp?F=TRUE link to register].
1. Prepare your spreadsheet or text file so that it is clean and consistent.
Please try to avoid [BulkImport#h6 common errors] or attempt to use one of the
standard Excel import templates. (There are separate templates for
[http://www.cellartracker.com/bulkimport.xls inventory] and for
[http://www.cellartracker.com/bulknote.xls consumed bottles and tasting
notes]).
1. Mail your spreadsheet(s) to eric@cellartracker.com. If you are mailing
from a different address than you used to register on the site, please point
out your username. Also, if you have previously entered information into your
CellarTracker cellar, please let us know if it should be wiped before the
import is processed.
Finally, please be patient while waiting for us to do initial processing of
your data, as this can sometimes take a day or two. The cleaner your
information is, the more quickly we can process it. In contrast, custom
spreadsheets or spreadsheets with inconsistent data can take hours to cleanup.
= The Process (high-level) =
There are three phases of the import. The first and last are largely handled by
CellarTracker administrator(s), and the middle phase is the responsibility of
the user.
1. **De-stemming and crushing**<br>In this first phase of the process,
you supply a spreadsheet (Microsoft Excel), database (Microsoft Access) or text
file (any CSV or tab delimited text should work). There may be some iteration
as CellarTracker administrators work with you to 'de-stem' the content
(a.k.a. determine what each column contains and the best way to import the
specific content into CellarTracker while retaining the original meaning). Once
this iteration is done, the 'crush' will ensue as a CellarTracker administrator
will import this into the database and match up the columns with the standard
naming conventions.<br><br>
1. **Fermenting**<br>This phase is largely in your control. The
'fermenter' is a holding tank of sorts for your cellar data and wine
descriptions. CellarTracker provides simple tools to help you search the
CellarTracker database for wines that match your descriptions so that you can
confirm the mapping for each unique wine. While a bit tedious, this process is
crucial to maintain the overall integrity of the CellarTracker wine database,
and it is necessitated by the fact that nearly everyone, left to their own
devices, describes the same wine in subtly different ways. If you have multiple
vintages of the same wine you only have to map one vintage to import them all.
This process starts with an automated pre-mapping where typically 25-40% of
your wines can be automatically mapped to their CellarTracker counterparts. For
these pre-mapped wines, you merely need to review them and confirm the validity
of the mappings. For the unmapped wines, you use the mapping tools to prepare
your data for import to the right wines.<br><br>
1. **Bottling**<br>When you are done mapping wines (or at any point
during the fermenting process when you want to take a breather), you can send
email to eric@cellartracker.com requesting that mapped wines be processed and
imported into your cellar. At this point, bulk content with mapped wines is
marked as completed, and the actual bottles and/or tasting notes are created in
your CellarTracker cellar. Any unmapped wines remain in the 'fermenter' for you
to continue mapping, and you do not need to do anything special to save your
progress.
= The Process (detailed) =
Now that we have described the process in somewhat superficial detail, here is
a more detailed explanation with some screenshots from the site. If you find
yourself at all confused during the import process, this section should be
helpful in clarifying things for you.
== 1. Background ==
The real challenge with import stems from the way that CellarTracker is built.
At the core of a site is a database of wines that is centrally maintained and
shared across all users. So if 10,000 users share the 1989 Lynch Bages, ideally
all of their information is 'hanging' from that same record in the database.
This allows everyone to easily see tasting notes and drinking window
recommendations from the rest of the community. Duplicates and errors do
appear, but we constantly look to merge wines and fix inaccuracies. Given this,
bulk import presents a unique challenge, as collectors catalog their wines in a
variety of ways, using subtly different names, storing different information
etc. The bulk import tools are designed to overcome this challenge while at the
same time making it easy for people to import information about hundreds or
even thousands of bottles in just a few hours.
== 2. De-stemming and crushing ==
In this first phase of the process, you supply a spreadsheet (Microsoft Excel),
database (Microsoft Access) or text file (any CSV or tab delimited text should
work). There may be some iteration as CellarTracker administrators work with
you to 'de-stem' the content (a.k.a. determine what each column contains and
the best way to import the specific content into CellarTracker while retaining
the original meaning). Once this iteration is done, the 'crush' will ensue as a
CellarTracker administrator will import this into the database and match up the
columns with the standard naming conventions.
Standard columns
There are two flavors of bulk import spreadsheets: those focused on inventory
(bottles, purchases etc.) and those focused on consumption history (when
consumed or sold, how much received, tasting notes and personal scores etc.)
* You can download a standard Excel inventory template from
http://www.cellartracker.com/bulkimport.xls.
* You can download a standard Excel tasting note template from
http://www.cellartracker.com/bulknote.xls.
Currently the bulk import tools support the following columns of information.
All columns are optional except for Vintage, UserWine1, quantity and Bottle
size.
**Vintage:** The year the grapes were grown. Non-vintage wines use an
underlying value of 1001.<br>
**UserWine1 - UserWine8:** Users can import up to 8 different fields describing
the wine. Typically these include the name of the producer, the varietal,
proprietary names, appellations, country, wine color and type etc. Please note
that //**these fields are not imported**//, but rather they are concatenated
together to help you search for a matching wine in CellarTracker. Only
UserWine1 is required.<br>
**Quantity & Bottle Size:** Self explanatory.<br>
**Storage Location & Bin:** The system lets you use two 40 character fields
to describe where the wine is stored.<br>
**Current Value:** An estimate for the current worth of the wine.<br>
**Begin & End Drinking:** This lets you specify the beginning and ending
years targeted for drinking.<br>
**Store:** The name of the store or supplier of a wine.<br>
**Purchase & Delivery Dates:** When a wine was purchased and the estimated
or actual date of delivery if different from the purchase date.<br>
**Delivery status:** If you mark the wine as pending (a value of '1' in the
Pending column), this shows up as a 'future' or pending delivery when
imported.<br>
**Notes:**<br>
You can add a 255-character bottle note (attached privately to each
bottle)<br>
You can add a 255-character purchase note (attached privately to each
purchase)<br>
You can store one private note per-wine, a long text field (6,144 characters)
that is useful for storing all sorts of information.<br>
**Review Scores:** For each of the Wine Advocate, Wine Spectator, Intl. Wine
Cellar and BurgHound, the site allows you to import a score as well as year
values for begin and end drinking windows). You can also import up to 6144
characters of review text per review.<br><br>
http://www.cellartracker.com/images/bulkfaq_clip_image002.jpg
https://www.cellartracker.com/images/bulkfaq_clip_image002.jpg
It's also important to note that the same wine can have multiple rows in a
spreadsheet if, for example, you choose to track separate purchases of the same
wine, different bottle sizes etc.
=== Common errors ===
Many people maintain spreadsheets for their wine inventory, but in reviewing
quite a few we have come to see some standard errors that require cleanup
before they can be imported.
* **Muddled bottle sizes:** Often times, people will not have a separate
bottle size column but rather demarcate some aspect of the wine name to include
"(magnum)" or some other size descriptor. Ultimately, this data has
to be culled out to a separate column of information. Also, for mapping
purposes, a 1.5L and 750ml bottle of the same wine will be mapped to the same
wine in CellarTracker. If these are described differently in the spreadsheet,
they each need to be mapped.
* **Combined reviews:** This is probably the most common error. People will
often have one "Score" column and a separate "Source"
column to indicate if the score was from the Wine Advocate, Wine Spectator etc.
For purposes of bulk import, these scores need to be in separate columns that
are clearly dedicated to a specific publication.
* **Extra rows:** People often seem to break their spreadsheets into separate
sections based on region or other classifications. They will have a few rows in
between with counts of bottles etc. For purposes of bulk import, these
intermediary rows need to be deleted to create a regular grid.
* **Irregular use of columns:** Sometimes people use a spreadsheet as a
somewhat freeform grid, laying things out visually. These sheets require the
most work to clean up.
* **Muddled bins/locations:** CellarTracker allows you to demarcate bins
and/or locations for every single bottle in your collection. These however need
to appear in separate rows of your spreadsheet rather than having comma
separated lists or things like "Cellar (2 bottles); Offsite (3
bottles)". **Repeat, these need to be in separate rows with quantities
matching the location.**
=== Supported formats ===
Bulk import itself happens on a Windows Server 2003 machine using the Data
Transformation Services of Microsoft SQL Server. As such, accepted formats
include Microsoft Excel, Microsoft Access, dBASE, Paradox, FoxPro, and various
text formats (tab-delimited, comma-separated, etc.) In the future we hope to
install and run tools like FileMaker or Microsoft Works, but for now, if you
store data in those tools, you will need to use their export facilities to
generate any of the above formats. In extreme cases, we have accepted tables in
Microsoft Word or HTML (which can be easily copied and pasted into Microsoft
Excel for subsequent massaging).
== 3. Fermenting ==
This phase is largely in your control. The 'fermenter' is a holding tank of
sorts for your cellar data and wine descriptions. CellarTracker provides simple
tools to help you search the CellarTracker database for wines that match your
descriptions so that you can confirm the mapping for each unique wine. While a
bit tedious, this process is crucial to maintain the overall integrity of the
CellarTracker wine database, and it is necessitated by the fact that nearly
everyone, left to their own devices, describes the same wine in subtly
different ways. If you have multiple vintages of the same wine you only have to
map one vintage to import them all. This process starts with an automated
pre-mapping where typically 25-40% of your wines can be automatically mapped to
their CellarTracker counterparts. For these pre-mapped wines, you merely need
to review them and confirm the validity of the mappings. For the unmapped
wines, you use the mapping tools to prepare your data for import to the right
wines. If you have multiple vintages of the same wine you only have to map one
vintage to import them all.
=== Fixing validation errors ===
There are a variety of errors and inconsistencies that need to be cleaned up
before information can be imported. For example:
1. Invalid or non date values for purchase dates or drinking windows.
1. End drinking windows dates that occur before beginning dates.
1. Non-numeric values for vintages or quantities.
1. Non-currency values for price or valuation.
Any wines with errors will show an indicator that it needs to be fixed. For
example, the row below has a typo of 202 instead of 2020 for the end
window.<br>
http://www.cellartracker.com/images/bulkfaq_clip_image004.jpg<br>
https://www.cellartracker.com/images/bulkfaq_clip_image004.jpg<br>
=== Mapping to Existing Wines ===
This is the core of the process.
For each unique wine name employed in your cellar, you need to establish a
mapping to the same wine in the CellarTracker database. For an individual wine
you simply click the "Need to MAP" link as shown below.<br>
http://www.cellartracker.com/images/bulkfaq_clip_image006.jpg
https://www.cellartracker.com/images/bulkfaq_clip_image006.jpg
That brings up the following search window with a preset search based on the
user wine name fields. In this case there is one match to the CellarTracker
database.<br>
http://www.cellartracker.com/images/bulkfaq_clip_image008.jpg
https://www.cellartracker.com/images/bulkfaq_clip_image008.jpg
Pressing the PICK button brings up the following mapping screen:<br>
http://www.cellartracker.com/images/bulkfaq_clip_image012.jpg
https://www.cellartracker.com/images/bulkfaq_clip_image012.jpg
The process is even more efficient if you choose to summarize by wine name,
since then you can more clearly just establish one mapping to cover many
vintages of the same wine.<br>
http://www.cellartracker.com/images/bulkfaq_clip_image014.jpg
https://www.cellartracker.com/images/bulkfaq_clip_image014.jpg
So clicking on the map… link here and following the same steps leads you
to this screen with 14 vintages mapped in seconds:<br>
http://www.cellartracker.com/images/bulkfaq_clip_image018.jpg
https://www.cellartracker.com/images/bulkfaq_clip_image018.jpg
=== Mapping to Newly Created Wines ===
Generally we find that 95-99% of the wines in a collector's cellar are
**already represented in the CellarTracker database**. So on rare occasions you
will need to create new wines instead of mapping to existing ones. So for
example, here is a case where there is no default match:<br>
http://www.cellartracker.com/images/bulkfaq_clip_image020.jpg
https://www.cellartracker.com/images/bulkfaq_clip_image020.jpg
In this situation, the best thing is to adjust the default search string to
case the net more widely. So you could search on just "Knyphausen" to
get this:<br>
http://www.cellartracker.com/images/bulkfaq_clip_image022.jpg
https://www.cellartracker.com/images/bulkfaq_clip_image022.jpg
Or "Knyphausen Steinmorgen"<br>
http://www.cellartracker.com/images/bulkfaq_clip_image024.jpg
https://www.cellartracker.com/images/bulkfaq_clip_image024.jpg
Or "Knyphausen eiswein"<br>
http://www.cellartracker.com/images/bulkfaq_clip_image026.jpg
https://www.cellartracker.com/images/bulkfaq_clip_image026.jpg
So we can see a number of close matches (several wines from the producer, some
from the same vineyard, others that are Eiswein but different vineyard etc.) So
we are going to need to create a new wine. The simplest thing will be to create
a new wine based on the other Eiswein from the same producer. In the screen
above, you simply click on the (NEW…) text below the name of the Eiswein
bringing you to this screen:<br>
http://www.cellartracker.com/images/bulkfaq_clip_image028.jpg
https://www.cellartracker.com/images/bulkfaq_clip_image028.jpg
Now we are adding a new wine using the existing one as a template. In this
case, all we need to do is change the Vineyard from "Erbacher
Siegelsberg" to "Erbacher Steinmorgen". So you click on the
Change… button next to the vineyard name:<br>
http://www.cellartracker.com/images/bulkfaq_clip_image030.jpg
https://www.cellartracker.com/images/bulkfaq_clip_image030.jpg
The other vineyard name is the next in the list, so we pick it, press CLICK TO
SET VINEYARD, and then CLICK TO ADD NEW WINE on the next screen. Pretty simply
really!
So following these steps, you simply march through your cellar clicking on the
mapping links until you are done. You make the most immediate progress in the
wine summary view as shown below:<br>
http://www.cellartracker.com/images/bulkfaq_clip_image032.jpg
https://www.cellartracker.com/images/bulkfaq_clip_image032.jpg
What we have found is that most collectors can map about 75-100 wine names per
hour. So for the cellar above with 3,230 bottles, 793 actual wines and 452
unique wine names, it’s estimated that mapping will take from 4.5 to 6
hours. However, the pre-mapping phase would typically wipe out 1/3 of that
estimate to make things even faster.
== 4. Bottling ==
When you are done mapping wines (or at any point during the fermenting process
when you want to take a breather), you can send email to eric@cellartracker.com
requesting that mapped wines be imported into your cellar. At this point, bulk
content with mapped wines is marked as completed, and the actual bottles are
created in your CellarTracker cellar. Any unmapped wines remain in the
'fermenter' for you to continue mapping. Before importing mapped wines you do
need to ensure a few things:
* If any mapped wines have FIX indicators (as described above) these need to
be fixed before the entire import can proceed.
* If you have existing wines in your cellar, you need to let us know if these
should remain or if they are test wines which you would prefer to be cleared
out.
= Frequently Asked Questions =
**Q:** What if I already started to enter my cellar manually?<br>
**A:** That is no problem. Bulk imported data can be applied to an existing
cellar. However, if your spreadsheet is the master reference (and is
duplicative with information entered into CellarTracker already), you should
request to have your current CellarTracker inventory wiped before the fermented
content is bottled.<br>
<br>
**Q:** How much does this cost?<br>
**A:** Right now there is no charge for the bulk import service, although may
change if it proves to be too labor intensive. CellarTracker itself is free,
but users are encouraged to make annual, [VoluntaryPayment voluntary payments].
$36/year is the suggested contribution for users with fewer than 500 bottles in
their cellar, $75/year for users with 500 to 999 bottles, and $150/year for
users with 1,000 or more bottles. We sincerely hope that any user who
successfully employs the bulk import service at least pay the requested amount
for one year.<br>
<br>
**Q:** How long will the import take?<br>
**A:** That is a function of the size of your cellar and the number of unique
wines (ignoring vintage variation). We have generally found that users can map
75-100 unique wines per hour, and the typical 1,000 bottle collection has
200-300 unique wines.<br>
<br>
**Q:** What formats can you import?<br>
**A:** See supported formats above.<br>
<br>
**Q:** What about wine futures (pending orders)?<br>
**A:** Absolutely. Just mark the Pending column with a value of '1', and the
wine is created as pending delivery instead of being added directly to your
cellar. This allows you to use a standard CellarTracker feature which lets you
closely track your 'futures' and 'pre-order' purchases.<br>
<br>
**Q:** What about tasting notes?<br>
**A:** Absolutely. This is a newer feature of the bulk import tool. Now you can
build a spreadsheet of consumed bottles and/or dated tasting notes and import
this in parallel or in lieu of actual inventory.<br>
-------
CategoryFAQ