How to make a word-a-day site like this

If there’s a language you’d like to make a word-a-day site for, I’m pasting here the notes I made as I built jerriaisword.com

Find a dictionary you can use (think of GitHub, Wikipedia, MetaShare, language promotion agencies, etc.)

Import the dictionary into Excel, with Language 1 in column one, language 2 headword in column 2 and alternative words and definitions in columns 3+

1.

weed out words starting with capitals Proper Nouns

=CODE(LEFT(B2,1))

where words are in cell B2, drag the formula down.

————————————

2.

we don’t want adjacent cells to be the same

so

=IF(A1=B1,”Match”,””)

sort a-z then delete duplicate like air=air

—————————————

3.

use Remove duplicates first for one language column
then the other

———————————–

4. weed out inappropriate rows manually
sort a-z to remove blank rows, keeping header at top

identify any bad ? in words and either trash from post list or edit them to repair.

—————————-

5 do a kinda excel mailmerge

=CONCATENATE(F2, ” in Jèrriais is “, A2)

twice, once for Title, the other time for Content

I’ve tried hiding no-longer-needed columns to simplify

Reason: you’re trying to construct a sentence which you can use as the Title for a

wordpress article about each word.

N.B.  Mailmerge
—————

promblems with find and replace «EN»
couble arrow not replaced properly
need to copy the field ont o your clipboard and use Rplace: ^C as describec not at the top

of this page:
http://www.wordbanter.com/showthread.php?t=104181

————————————

NNB open each in word
change case to title case

save as txt from word
but be sure to select utf8

find ^p replace ^t
find 1:: replace ^p1::

Or find ^p:1:^p Replace ^t

6.

=RAND()
to jumble up the rows
Because you don’t want to publish words in alphabetical order

———————

7. bring in the right header names from a good past csv and then sort dates out

————————-

8.use copy and pase special:actual values
to groom data. Make a new Excel doc which only has the columns you need

——————————–

9. export it as comma delimited )check accents!!(

Jèrriais
Jèrriais language of Jersey – learn a word a day

‘Ultimate csv importer’ struggles with accented characters
so try ‘Import CSV Files’

Need to get date format right
was
14/11/2010  11:55:00 which didn;t work.

so try a ‘timestamp’ of
Mar 19, 2008 16:54:11 PM
Jan 01, 2012 12:30:00 PM

to do this, use two columns of excel:
Col 1: date, increment by one per cell and drag down. _Then_ format the date to this mask

using Spe3cial or custom:
mmm dd, yyyy
Col 2: drag the same time to the bottom, format something like:
hh:mm:ss AM/PM

I tried to Concantate the two – but it didn’t work
So I selected both time and date columns, copy, paste into pspad (a free portable text

editor)
then replace the tab with a space on each line using reg expression

\t

—————————-

10

save as tab delimited utf8 – called ‘unicode.txt’
rename to csv
import into WP using
‘Import CSV Files’ not ‘Ultimate csv importer’, which struggles with accented French

characters

11

for dBs longer than 3000 rows, split into multi parts

12

Prepare the domain by adding nameserver credentials at Namecheap or similar.

13
On your hosting service

———————————————–

in CPanel
create jerriaisword dB
add <username>
with all privileges

—————————

use ftp to login remotely, rename the existing blank wp install to a directory called

jerriaisword
and reupload the next blank wp install, ready for next time.

——————————-

set directory and addon domain in hosting

——————————-

Once everything’s propogated you’ve now got a loive site on the internet.

14

I like to build the site in a local sandpit and export the posts so I can import them into

the live instal of WorkPress.

So, export posts in two or more hops, because one’s too big.
use the Bulk Delete plugin in between

15
——————————-

Then instal wp on live server
do the normal housekeeping:
install jetpack with quickstart
install or just activate imageinject
activate plugins
choose theme
do updates
tweak settings category url naming
don’t forget to untick nested folder images
import both (or more) all posts xml files.

———————————–

15

Start adding images

————————-

16

create twitter account

link twitter using Share

find pixabay images

configure colours

=============================================

 

Pob lwc; good luck.

Gareth Morlais

 

P.S. To ease sharing and to help more people make sites like this I’m licencing the instructions on this page CC-BY.