BATCH UPLOADING OF IMAGES - Aug 3rd, 2010


The new multi-file upload facility built in to CMSB Version 2.02 has kind of eliminated the need for this recipe, but
I’m leaving it here because it explains a number of things about manipulating MySQL and Excel data.

That said...

I had a project that required the uploading of over 800 images and thumbnails overnight and displaying the thumbnails in
a table. In my case there were no titles or captions for the images, just the images.

There were a some images that needed to be deleted after the upload was finished and some that needed to be re-ordered.
These tasks were accomplished in CMSB after all the images were uploaded via FTP and the database was modified to show
the images.

In CMSB, all uploads are referenced in a single database table called “yourprefix_uploads”. There are many columns
of information but the data in them is pretty straightforward. By manipulating the data in the columns and uploading all
of the images to upload folders I was able to accomplish the task in a few hours with a minimum of effort.

The instructions look complex, but they are much simpler to implement than they look.

TOOLS REQUIRED

Navicat for MySQL http://navicat.com/ to access the on-line databases and export and import information.
EXCEL to manipulate the data
Photoshop (or the free image editor Irfanview http://www.irfanview.com ) to batch create the thumbnails and full sized
images to be uploaded.
A free batch file renaming utility like http://www.bulkrenameutility.co.uk/

THE PROCESS
IMAGES
Rename all the project images sequentially with the bulk rename utility. (I used 1.jpg through 850.jpg but the names can
be more complex)
Batch resize the images to create both the thumbnail and full sized images
Upload all images to the appropriate sub folders in the cmsAdmin folder on your server

EDITOR AND VIEWER
Set up a multi record editor with an upload box with an unlimited amount of images per record. Modify the size
information to match the size of the thumbnails and images that you created.
Set up a list page viewer to display the thumbnails
Upload a few test images and make sure that your viewer displays correctly. (This will also be helpful when you add data
to the database table

DATABASE
Use Navicat to download the “yourprefix_upload” table in excel format
Create a backup copy of the original “yourprefix_upload” table ***IMPORTANT***
Add the appropriate series of information to the columns in the table
Upload the revised “yourprefix_upload” table to overwrite the old table
If any errors messages appear in Navicat, go back to your spreadsheet and correct them (usually typos or missing
information) and upload again.
If you were careful, you’re done, and the images will appear in your viewer.

THE DETAILS
In the spreadsheet you’ll find columns for:

num (find the highest number in this column and add sequential numbers in ascending order to rows below the last row
beginning with the highest existing number plus 1. Create the exact number of rows to match the amount of images that
you’ll be adding.)
order find the highest number for the Table name you are adding to and add sequential numbers in ascending order to rows
below the last row beginning with the highest existing number plus 1)
createdTime just extend the values down to fill the required rows from the last value in this column. This is not a
critical value.
Table name this row contains the name of the table where you will be adding the images. Add the table name to all rows
that will contain images
fieldName this is the name of the field in the table where you will be adding the images. Add the field name to all rows
that will contain images
record number since there is only one record in your gallery, the value in each one of these rows is 1
preSaveTempId not used
filePath this is the full path to the full sized images. Each row contains the full path and file name for one image.
Find the sample image entry and use this format as a model.
Since the increasing value is not at the end of the final cell contents, you’ll have to create the path information
with sequential numbers at the end and then add (concatenate) the .jpg to the end of the values. (SEE WORKING WITH
SERIES IN EXCEL, BELOW)
urlPath this is the folder, sub folder and file name location of each full sized image. Each row contains the location
information for one image. Find the sample image entry and use this format as a model. Create the entries using the
concatenation instructions below.
width and height the size of your full sized images in pixels (the values in each column will be identical)
thumbFilePath, thumbUrlPath, thumbWidth, thumbHeight the information for thumbnail 1. (see filepath above)
thumbFilePath2,3,4 etc. (as required) If you are not using a particular thumbnail, enter a value of 0 for each row in
their width and height columns.

WORKING WITH COMPLEX SERIES IN EXCEL
In those columns where the increasing value is the last characters in the cell (simple numbers, repeating values)
working with series is extremely simple using step 1 and 2 below.

To create a series of entries in an Excel spreadsheet where the increasing value is not last characters in the cell.
(Image1.jpg, image2.jpg) You’ll have to perform the operation in 2 steps. 1) create the series of increasing values.
(Image1, image2, etc.) and 2) add (concatenate) the .jpg to all values.

Here’s how:

1) In the target column, where the resultant series is to be “Image1.jpg, image2.jpg” remove the .jpg so that the
number is the last value (image1, image2)
2) To fill the cell range in the column with a series of increasing numbered contents highlight the range then (edit>
fill> series)
3) If there is more than one column that requires a series, repeat step 1 and 2 as necessary.
4) Create a column where all cells in the series range of rows have the contents “.jpg”
5) in a blank column in the first row to contain the concatenated contents, enter the formula =CONCATENATE(A1,A2) where
A1 is the cell that contains the contents “image1" and B1 is the cell in the same row that contains the contents
“.jpg"
6) Highlight the cell in the blank column and copy (CTRL-C)
7) Click in the cell in the row directly below that one and drag to the end cell in the series
8) Paste using CTRL-V
9) To replace the contents of the target column in step 2 with the concatenated version:
A) highlight and copy all the concatenated cells in the new column.
B) place the cursor in the first cell of the range in the target column (step 2)
C) choose Paste - Values Only from the paste menu

10) Change the formula in the cell referred to in step 4 to represent the new target row and repeat step 6 through 9

It’s a bit cumbersome, but it works. If anyone has a simpler approach, please share it and I’ll include it in the
Cookbook.



The materials on this web site have been created for use with CMS Builder content management software. CMS Builder software is published and licensed for use by InteractiveTools.com. Please contact Interactive Tools for information on the downloading of the software or the purchasing of licenses.


Terms of Service