Return to main CellarTracker site...

Bulk Import

Front Page | Recent Changes | Title Index | Help
Difference from prior author revision.
major diff minor diff hide diff

This 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

Front Page | Recent Changes | Title Index | Help
Edit this page | View other revisions
Print this page | View XML
Find page by browsing, searching or an index
Edited November 22, 2011 (hide diff)