Bulk upload via MySQL

The format is integer unix utc timestamp (seconds since jan 01 1970 0:00:00 UTC).

Are you aware of the correlation between photo id and the filename of the thumbnails and display size image files, i.e. the wppa internal filestructure?

The format is integer unix utc timestamp (seconds since jan 01 1970 0:00:00 UTC).

ok, thanks

Are you aware of the correlation between photo id and the filename of the thumbnails and display size image files, i.e. the wppa internal filestructure?

Nope, I am not.
Could you please share that.
Any other thing I would need to keep in mind?

Thanks,

Can you pls tell me what data you have available and in what format?
I assume you still have the original photos? Do you have them in a filestructure with subfolders? We can import that structure keeping the (sub)folders and make them (sub)albums.

Any IPTC and EXIF data in the photofiles can be processed by WPPA.

Do you have other metadata, if so, what kind of and in what format.
We can import up to 10 custom datafields by a .csv file.

Pls explain me what you have, in what form, and i will advice you the way to import the stuff into wppa.

The data is currently in a G2 SQL database, i plan to run sqls and massage it to get into a format corresponding to the table “wp_wppa_photo” and export to csv.
Then import this csv into the table (overwriting the current values) using PHPmyAdmin. I would be doing this after I add custom datafields in WPPA and the structure of the table is fixed

1. Yes, I have all the original photos. I plan to replace some images my changing the appropriate entry in the csv.I have thumbnail files too but I plan to regenerate them seperately in Photoshop (automated)
2. They are currently in a single folder, irrespective of the album. On the current site i have 1 folder with 6 sub-folders.
3. Some image do have EXIF but i do not plan to use/display those values
4. With the image the data associated is date clicked, date uploaded, caption, title, description. In some there is common name, scientific name and families/genre etc as tags
To this I plan to add GPS/location (to automatically generate distribution maps) and put common name, scientific name into custom fields. Also have a fixed sequence of values that I would be adding into tags

I hope I am able to convey what I am planning to do.

Thanking you

1. Thumbnails will be generated by wppa. You can not import ready thumbnails, so do not waist effort in creating them.

2. Can you easely decide to what album they belong seeing the filenames?

3. Ok

4. WPPA processes gps info (from the exif gpx tag) automaticly, regardless if you use exif.

I think… the best way is:
– ftp them all to …/wp-content/wppa-depot/[your username]
– Create the albums you are going to need.
– Use the import screen to import them, you can select the album there. Use the Start Ajax Imort button.

This proc will place the originals in the …/wppa-source/uploads/album-[id]/ folder and create display and thumbnail files and register gps data and exif date if present in the orig photo file.

There is a proc to import a .csv for custom data, see:
https://wppa.nl/docs-by-subject/custom-data/

If needed i will write a proc to make it possible to import a .csv with photo name, description and tags (maybe more standard fields)

Note: You talk about caption, title and description. Standard fields in wppa are filename, name and description. Other text based fields must be implemented by the custom data fields (max 10).

Filling the db table directly with a .csv file is not a good idea, because it is not obvious which id belongs to which photo, and the (possibly downsized) display files and thumbnail files have their names changed into [id].[ext].
Example: Photo My-selfie.jpg may have the recored id in the wppa_photos db table of 4711. In this case, the pathname of the display file is
…/wp-content/uploads/wppa/4711.jpg or even
…/wp-content/uploads/wppa/47/11.jpg if you convert to treestructure, for very large systems to avoid max dir entries overflow.
and the thumbnail:
…/wp-content/uploads/wppa/thumbs/4711.jpg
or:
…/wp-content/uploads/wppa/thumbs/47/11.jpg
respectively.

During the import proc, the default is photoname = filename (other possibilities settable in Table IX-D13 of the Photo ALbums -> Settings admin page). Photoname is changeable, if you need it automated i could write a proc for it as mentioned above.

Question: How many photos approx? Over 50.000 ?

• This reply was modified 7 years, 9 months ago by Jacob N. Breetvelt.

First of all thank you for your prompt and detailed response. That too on a Saturday, highly appreciated.

1. Thumbnails will be generated by wppa. You can not import ready thumbnails, so do not waist effort in creating them.

Thanks for the heads up

2. Can you easely decide to what album they belong seeing the filenames?

Yes, I can. If needed can update in csv.

4. WPPA processes gps info (from the exif gpx tag) automaticly, regardless if you use exif.

99.9% of the images wont have GPS info as its all stripped when the low-res image for the web is created.
I plan to enter the gps/lat-long in a custon data field or generate the lat-long from the location data which is another custom field.

I think… the best way is:
– ftp them all to …/wp-content/wppa-depot/[your username]
– Create the albums you are going to need.
– Use the import screen to import them, you can select the album there. Use the Start Ajax Imort button.

This proc will place the originals in the …/wppa-source/uploads/album-[id]/ folder and create display and thumbnail files and register gps data and exif date if present in the orig photo file.

Can i do this multiple times?
i.e ftp say 500 images to the above location and then use Ajax import.
after the process is complete clear that folder and add a new set of images for import.

There is a proc to import a .csv for custom data, see:
https://wppa.nl/docs-by-subject/custom-data/

I had seen this but my understanding was that this just imported the custom fields from images already in the album.
Please correct me if I am wrong

If needed i will write a proc to make it possible to import a .csv with photo name, description and tags (maybe more standard fields)

Thanks a lot. I shall certainly take you up on this when I am finalised with the fields

Note: You talk about caption, title and description. Standard fields in wppa are filename, name and description. Other text based fields must be implemented by the custom data fields (max 10).

These were the field names from the earlier database will put them in appripriate fields in WPPA

Filling the db table directly with a .csv file is not a good idea, because it is not obvious which id belongs to which photo, and the (possibly downsized) display files and thumbnail files have their names changed into [id].[ext].
Example: Photo My-selfie.jpg may have the recored id in the wppa_photos db table of 4711. In this case, the pathname of the display file is
…/wp-content/uploads/wppa/4711.jpg or even
…/wp-content/uploads/wppa/47/11.jpg if you convert to treestructure, for very large systems to avoid max dir entries overflow.
and the thumbnail:
…/wp-content/uploads/wppa/thumbs/4711.jpg
or:
…/wp-content/uploads/wppa/thumbs/47/11.jpg
respectively.

During the import proc, the default is photoname = filename (other possibilities settable in Table IX-D13 of the Photo ALbums -> Settings admin page). Photoname is changeable, if you need it automated i could write a proc for it as mentioned above.

I am not sure if I completely understood this. I get it that thumbnail creation is via the system but isnt the filename the same as what is entered in the csv (assuming that I dont change the default settings)
Or do you mean to say that copying csv into the db directly wont create the thumbnails (and any other settings/field in the database)?

Isnt the add image to gallery page doing the same thing or is there something else that it does?

Question: How many photos approx? Over 50.000 ?

The current database transfer might be much less. But eventually that number will crossed and I plan to use bulk imports to get to that point

its all stripped when the low-res image for the web is created.

Please import the ORIGINAL photo files. wppa will take care of resizing for display purposes (according to the settings in Table I-A 1..3). You can even do this later again, e.g. when you decide to change the diaplaysize.
You will also have the gps data in the right datafield and in the right format to display maps like here

Can i do this multiple times?

As many times as you wish. Regardless if it is one or 10.000. There is a checkbox to delete them after successful import, so even clearing afterwards is not needed.

my understanding was that this just imported the custom fields

True. My plan is to extend this to cover ‘standard’ fields also. Should not be too much effort to do this.

copying csv into the db directly wont create the thumbnails

What i mean is that copying the csv into the db directly is tricky, because the display- and thumbnail files must still be created using the data in the db record and the source photo files. This will only work if:
– You put the ORIGINAL photofiles in .../wp-content/uploads/wppa-source/album-1/ with exactly the same names as the filename field in the db. The filenames must be sanitized.
– You run the special remake procedure in Table VIII-A11 (Remake) WITH THE BOX IN TABLE IX-H6: (Remake add) ticked.

This is somehow equivalent to importing, but you mau need to recuperate (Table VIII-A7) other mata data afterwards.

My concerns:
– The problem of getting the correct correlation between the db record id and the photo files.
– Getting the metadata in the right format in the right db fields, and not in custom fields when there is a native field for it.

I’d rather extent the software to import your metadata like explained above than taking the risk of corrupting the db with unsanitized data.

The import .csv functionality for custom datafields will be extended in the next version (6.6.13) so that the following ‘standard’ datafields also can be filled by this procedure:

album, name, description, p_order, linkurl, linktitle, linktarget, owner, timestamp, status, tags, alt, modified, location, views, clicks, exifdtm.

The first item of the header MUST be filename (i.e. NOT name or photoname).
Values are checked c.q. sanitized for the proper format; failing proper format is not fatal but will generate a warning message and skipping the input value.
Values can be input but not cleared/removed by this procedure.

The sequence order of items in the .csv data lines (standard and custom fields) is irrelevant.

Please import the ORIGINAL photo files. wppa will take care of resizing for display purposes (according to the settings in Table I-A 1..3). You can even do this later again, e.g. when you decide to change the displaysize.

The ‘original’ file is always in a RAW format and upwards of 20MB in size, this is never put in thr public domain to view or download. Instead it is post processed and then reduced in size (pixel & KB) to a web friendly jpg format. This is then the ‘Original’ file for the gallery for all practical purposes.
I have a supplemental question to this but instead of polluting this thread will start a new thread.

You will also have the gps data in the right datafield and in the right format to display maps like here

Thats cool.
Some of the way I plan to use it is.
1. User can view all submissions for a given location/area/country
2. User can search and the result set of images will be marked on the map. e.g. user searches for a ‘x’ species by ‘y’ photographer

The import .csv functionality for custom datafields will be extended in the next version (6.6.13) so that the following ‘standard’ datafields also can be filled by this procedure

Thats great news, thank you.
Will be waiting eagerly for the release

If you can not convert the raw images to jpg while keeping the exif data (gpx): you can manually add it if you enable Geo info edit in Table IX-B8.

You can edit it in real numbers, say 52.123 and 4.3456 being degrees lattitude and longitude.

The internal format in the db for the ‘location’ field is like:
N 20°54'44.84"/E 95°12'30.3"/20.912455555556/95.208416666667

I.E.: Readable latt/Readable long/numeric latt/numeric/long.

If you want the ‘location’ field also be fillable by .csv import, pls tell me in what format, i can do conversions during import.

Hi Jacob,

The import .csv functionality for custom datafields will be extended in the next version (6.6.13) so that the following ‘standard’ datafields also can be filled by this procedure:

I am trying to understand this but not able to build an appropriate csv file.
Is it possible for you to put up one sample row from a csv for an image with custom fields

Thanks in advance

This is my test .csv file:

filename,Sponsored By,description,Message,Email,Age,City,Major,timestamp,owner
Bird-38658.jpg,Gold's Gym,Beschrijving,Get well.,[email protected],6,Buffalo,Math,-123,piet
Bird-778.jpg,ABC Company,"This is a description",It's going to be ok.,[email protected],,Fredonia,Electronics,4711,piet
Bird-39674.jpg,Reeds Gems,"This is the description",Wishing you well.,[email protected],7,Williamsville,Nursing,1000000000,willem
Bird-32026.jpg,Pet Emporium,"This is a description","Hold on, it will all work out.",,12,Buffalo,Sociology,1,willem
Bird-25464.jpg,Up & Down Coffee Shop,"This is another description",You can do it!,[email protected],8,chataqua,Business Administration,102400000,willem

The header explaned:

filename,Sponsored By,description,Message,Email,Age,City,Major,timestamp,owner

filename: mandatory for a mix of ‘system’ and ‘custom’ datafields, being the original filename of the photo. If you use ‘name’ or ‘photoname’ here you will get an errormessage and the proces will abort.
Sponsored By: The first – automaticly created – custom datafield caption (w#cc0 in Table II-J10.0a).
description: ‘system’ datafield, see the photo table definition:

$create_photos = "CREATE TABLE " . WPPA_PHOTOS . " (
				id bigint(20) NOT NULL,
				album bigint(20) NOT NULL,
				ext tinytext NOT NULL,
				name text NOT NULL,
				description longtext NOT NULL,
				p_order smallint(5) NOT NULL,
				mean_rating tinytext NOT NULL,
				linkurl text NOT NULL,
				linktitle text NOT NULL,
				linktarget tinytext NOT NULL,
				owner text NOT NULL,
				timestamp tinytext NOT NULL,
				status tinytext NOT NULL,
				rating_count bigint(20) NOT NULL default '0',
				tags text NOT NULL,
				alt tinytext NOT NULL,
				filename tinytext NOT NULL,
				modified tinytext NOT NULL,
				location tinytext NOT NULL,
				views bigint(20) NOT NULL default '0',
				clicks bigint(20) NOT NULL default '0',
				page_id bigint(20) NOT NULL default '0',
				exifdtm tinytext NOT NULL,
				videox smallint(5) NOT NULL default '0',
				videoy smallint(5) NOT NULL default '0',
				thumbx smallint(5) NOT NULL default '0',
				thumby smallint(5) NOT NULL default '0',
				photox smallint(5) NOT NULL default '0',
				photoy smallint(5) NOT NULL default '0',
				scheduledtm tinytext NOT NULL,
				custom longtext NOT NULL,
				stereo smallint NOT NULL default '0',
				crypt tinytext NOT NULL,
				PRIMARY KEY  (id),
				KEY albumkey (album),
				KEY statuskey (status(6))
				) DEFAULT CHARACTER SET utf8;";

Message: w#cc1
Email: w#cc2
Age: w#cc3
City: w#cc4
Major: w#cc5
timestamp: ‘system’ datafield
owner: ‘system’ datafield

Note: if a certain custom datafield in the .csv field already exists, it will be used, so the sequence order of the w#ccx fields in the db need not to be equal to the one in the .csv file.

Note: This example file is derived from a real life case: the site i designed custom field bulk imput for originally.

Note: only the ‘system’ fields: album, name, description, p_order, linkurl, linktitle, linktarget, owner, timestamp, status, tags, alt, modified, location, views, clicks, exifdtm can be entered this way.

Note: Supplying data in the wrong format is not fatal but will result in an errormessage and ignoring the value.

• This reply was modified 7 years, 9 months ago by Jacob N. Breetvelt.
• This reply was modified 7 years, 9 months ago by Jacob N. Breetvelt.
• This reply was modified 7 years, 9 months ago by Jacob N. Breetvelt.
• This reply was modified 7 years, 9 months ago by Jacob N. Breetvelt.

Hi,

I tried this, the image does get added to the gallery but all the data fields do not.
my header was like this
filename,description,Common Name,Scientific Name,state,GPS,date photographed
first 2 ‘system fields’ and the rest custom fields

out of this only the filename gets added the rest of the fields are blank

I am using a gallery (and DB) created earlier and this had a lot of entries. I added/changed custom fields and then did this import.
Would that have an effect?
Do i need to clean the whole gallery first?

Thanks

You need to have photos with their filename exactly matching the names in the first column of the .csv file

So, first upload or import the photos and then import a .csv file where the filenames are in accordance with the filenames in the album admin.

When processing the .csv file, the db is searched for the supplied filename (first item on each row) to find the photo id where to add the supplied metadata.

• This reply was modified 7 years, 9 months ago by Jacob N. Breetvelt.

So, first upload or import the photos and then import a .csv file where the filenames are in accordance with the filenames in the album admin.

I got it to work. I just did the ‘import’ twice
First time the images got added.
Copied the files again and imported again, this time pressing the ‘update’ button
the other fields got added.

Can I use this without causing any ill to the database?

What the difference between the 2 buttons “update” and “start import”

Thanks in advance