Wine list to Excel conversion and standardisation
Budget: $250 – $750 CAD
I need assistance gathering wine list data from restaurant
websites (normally in PDF), converting to Excel and
formatting into a standardised 2 column list (wine name,
colour) for upload into a database.
The challenge: restaurants lists vary in presentation from
restaurant to restaurant, meanwhile I need standardised
information. Converting each list into the required format
requires a significant amount of manual intervention. Each
list is different.
Issues
There are three principal issues / challenges: Colour - Name
- Formatting
Colour (white - rose - red)
Wine lists are normally organised by colour / style - typically:
sparkling wine (which are mainly white, but include rose),
white wines, red wine, dessert wines (normally white),
fortified (mixture of red and white). Lists normally include
general headers white wines - red wines, etc, but not always,
meanwhile I need a colour attached to every line in the csv
output file
Name
I need wine names in a standardised format that the
database will recognise.
The complete list of wines in the database can be
downloaded for comparison - however, Excel VLOOKUP is
not sophisticated enough for matching between the two data
sets
Some wines include maker name + wine name + region +
vineyard name. Others just include maker name.
E.g. for Bordeaux wines producer name only: Chateau
Margaux. For Burgundy producer name + village name +
vineyard name
Domaine ABC + Vineyard name + reference for Premier or
Grand Cru + Village name
Producer names and wine are often in different cells
sometimes the producer name will be at the top of a sub
section and the concatenation therefore needs an absolute reference
Formatting
Converting accented characters to non-accented
Removing and standardising certain words, phrases and
characters
Removing blanks and reducing the list to a standardised
three column format
Some of the formatting processes are relatively easy to run
through VBA modules (e.g. find and replace / delete)
I need this done quickly and with 100% accuracy.
Payment will be batches at the rate of $100 / 5000 lines of lines of output. Scope: 150 restaurant lists - size varies from list to list.
If you can come up with an automated solution using VBA so much the better
websites (normally in PDF), converting to Excel and
formatting into a standardised 2 column list (wine name,
colour) for upload into a database.
The challenge: restaurants lists vary in presentation from
restaurant to restaurant, meanwhile I need standardised
information. Converting each list into the required format
requires a significant amount of manual intervention. Each
list is different.
Issues
There are three principal issues / challenges: Colour - Name
- Formatting
Colour (white - rose - red)
Wine lists are normally organised by colour / style - typically:
sparkling wine (which are mainly white, but include rose),
white wines, red wine, dessert wines (normally white),
fortified (mixture of red and white). Lists normally include
general headers white wines - red wines, etc, but not always,
meanwhile I need a colour attached to every line in the csv
output file
Name
I need wine names in a standardised format that the
database will recognise.
The complete list of wines in the database can be
downloaded for comparison - however, Excel VLOOKUP is
not sophisticated enough for matching between the two data
sets
Some wines include maker name + wine name + region +
vineyard name. Others just include maker name.
E.g. for Bordeaux wines producer name only: Chateau
Margaux. For Burgundy producer name + village name +
vineyard name
Domaine ABC + Vineyard name + reference for Premier or
Grand Cru + Village name
Producer names and wine are often in different cells
sometimes the producer name will be at the top of a sub
section and the concatenation therefore needs an absolute reference
Formatting
Converting accented characters to non-accented
Removing and standardising certain words, phrases and
characters
Removing blanks and reducing the list to a standardised
three column format
Some of the formatting processes are relatively easy to run
through VBA modules (e.g. find and replace / delete)
I need this done quickly and with 100% accuracy.
Payment will be batches at the rate of $100 / 5000 lines of lines of output. Scope: 150 restaurant lists - size varies from list to list.
If you can come up with an automated solution using VBA so much the better