Return to main CellarTracker site...

Bulk Import

Front Page | Recent Changes | Title Index | Help
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:

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:
  1. Please familiarize yourself with the high-level overview of the import process.
  2. Please make sure to browse to www.CellarTracker.com and press the link to register.
  3. Prepare your spreadsheet or text file so that it is clean and consistent. Please try to avoid common errors or attempt to use one of the standard Excel import templates. (There are separate templates for inventory and for consumed bottles and tasting notes).
  4. 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
    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.

  2. 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.

  3. 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 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.)

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.
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.
Quantity & Bottle Size: Self explanatory.
Storage Location & Bin: The system lets you use two 40 character fields to describe where the wine is stored.
Current Value: An estimate for the current worth of the wine.
Begin & End Drinking: This lets you specify the beginning and ending years targeted for drinking.
Store: The name of the store or supplier of a wine.
Purchase & Delivery Dates: When a wine was purchased and the estimated or actual date of delivery if different from the purchase date.
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.
Notes:
You can add a 255-character bottle note (attached privately to each bottle)
You can add a 255-character purchase note (attached privately to each purchase)
You can store one private note per-wine, a long text field (6,144 characters) that is useful for storing all sorts of information.
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.

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.

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.
  2. End drinking windows dates that occur before beginning dates.
  3. Non-numeric values for vintages or quantities.
  4. 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.

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.

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.

Pressing the PICK button brings up the following mapping screen:

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.

So clicking on the map… link here and following the same steps leads you to this screen with 14 vintages mapped in seconds:

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:

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:

Or "Knyphausen Steinmorgen"

Or "Knyphausen eiswein"

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:

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:

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:

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:

Frequently Asked Questions

Q: What if I already started to enter my cellar manually?
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.

Q: How much does this cost?
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, 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.

Q: How long will the import take?
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.

Q: What formats can you import?
A: See supported formats above.

Q: What about wine futures (pending orders)?
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.

Q: What about tasting notes?
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.


Category FAQ

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 (diff)

© 2003-14 CellarTracker! LLC. All rights reserved. "CellarTracker!" is a trademark of CellarTracker! LLC. No part of this web may be used, reproduced or distributed without the prior written permission of CellarTracker! LLC. (CellarTracker! Terms and Conditions)