Bulk importing from CSV files

Working with bulk import of images to create a catalog

Premise
As a website designer you are presented with a CD/DVD containing hundreds of items (products) broken down into several categories that the site owner wants displayed on their website. Each product is supplied with one catalog image.
This scenario would normally then lead on to an ecommerce integration so that visitors to the site could view this catalog and then buy the items seen. These notes refer to building the catalog.

Manipulating the data
The CD has eight folders (categories) of products.
Each product has an image and a filename which is its item number.
The web catalog will consist of a page where one can chose the category.
The category page will display all the items in that category, paging over several pages if need be.
Selecting an item on the category page will load the product page with a medium sized image of the product, its part number and some standing text.
Selecting the medium sized image on the product page will load a large sized image - notionally the only one provided on the customer's disk.
So we need to get the data into the right shape so that it can be uploaded automatically.

Create the destinations
Before we start working on the data we need to create the places on the website that the date will be imported into.
In this example, two Tables will be required.

• A category table
• A product table

The Category Table will consist of the following fields:

• Record Id
• Category name
• Example image (used later).

The Product Table will consist of the following fields:

• Record Id
• Product name
• Thumb image
• Medium image
• Full image
• Category - as a RecordLink to Category table

Image manipulation
First the images are resized (using IrfanView) so that there is a thumb, medium and full sized version of the same image.
Each version is saved with a unique name: For example:
product1-125.jpg
product1-200.jpg
product1-600.jpg
Finally all the images are consolidated into one folder and that folder is zipped up.

CSV file
In order to upload them automatically a csv file must be associated with the zip file.
The csv file will consist, in this case, of five columns.
Product-Id, product1-125.jpg, product1-200.jpg, product1-600.jpg, category
The product id is derived from the image file name, this DOS/cmd instruction makes it trivial to derive file names:
dir /B > files.csv
This will create a file named files.csv, containing a list of the filenames, one per line.
If you then need to add extra columns in to this, you can open the files.csv file in Excel, add the columns, and resave it in csv format, and the csv can be opened in *Notepad* and file names manipulated using simple Find/Replace.
You will then end up with a csv file with one row for each product with multiple images referenced and the category for the product.

Getting the Bulk Data onto the site
In the site's layout Manager, add in an Upload Component.
Use the Upload Component to upload the CSV/Zip pair of files to the site.
There may be one consolidated csv/zip file contianing all products, so long the overall file size is less than 200MB per upload. However it is often administratively easier to break them down into logical groupings – Categories, in this example.
You can then follow the instructions on /1080
Tip: Use the 'U' designator, so that it is trivial to put replacement files up for records - you might not get it right first time!
Upload Script

Example script
Here is an example of an upload script:
D:\>cscript "D:\Program Files\Enstar\neatComponents\scripts\LoadFormFromCsv.wsf" /siteid 6107 /formid 52 /fields "[U50,54,53,52,51>48]" /csvpath "D:\Program Files\Enstar\neatComponents\wwwroot\sites\6107\53\files\bracelets.csv" /filespath "D:\Program Files\Enstar\neatComponents\wwwroot\sites\6107\53\files\bracelets.zip\bracelets"

Explanation:
This script will be working on site id 6107, in Table 52
The Table number is seen by mousing over the Table in the Layout Manager.
It will place the contents of the csv file in column order into the Table fields in the order shown.
CSV column 1 will go to Field 50, column 2 to field 54, column 3 to field 53, column 4 to field 52
In column 5 the csv contents will go to field 51 which is a Record Link field to 48 in the referenced table.
And the script indicates the location of the zip file containing the images that are referenced in the csv file.