Skip to content →

Category: SOCIAL MEDIA & IT

Build your first SQLITE3 database in Windows in less than 1 hour

Published October 10, 2011 by Jo Jordan

Here are some quick & dirty notes on getting SQLITE3 up-and-running on a Windows machine in less than an hour.

#1 Decide where to store SQLITE3 and where you will work

  • I decided to keep a copy of SQLITE3 in a sub-directory of my c:tools directory where I have stored other tools
  • I also decided to put a copy in the directory where I will keep the database. Being able to access SQLITE3 from the data directory saves me some typing later.
  • My data directory is what is sometimes called my desktop.  It is the Windows default setting.  To see the full “path” in Windows, click at the top of the screen where your browser bar usualy is.  C>USER> ….etc turns into the path looking like this C:USERUSERNAMEDocuments…  We’ll cut and paste this path later.

#2  Download SQLITE3

  • The Window binaries for SQLITE3 are here.
  • Download the command shell only.
  • Copy the zip file into both directories: c:toolssqlite and the data directory c:userusernamedocuments… [wherever you will be keeping your database]

#3 Prepare a little file to structure your database

  • As an example copy and paste this code into Wordpad
  • Save the file as “creategobble.sql”
  • Make sure it goes into the directory where you will be working (see above)
  • This file is going to make four tables in the new database.

#4 Go to your command prompt

  • Go to Start and type in “command prompt”.  You should see it come up top right
  • Select Command Prompt
  • Go back briefly to the directory holding your data and copy the path at the top (click on the >Libraries etc and the path will come up C:Users…etc
  • Go back to the Command Prompt and type in cd (for change directory) followed by the path (right click and paste).  Return
  • You will see that you are now working in your data directory

#5 Set up your database

  • Type in “sqlite3 gobble” (gobble is the name of our database)
  • You will see the sqlite come up
  • Type in this line: .read creategobble.sql <return>
  • Hey presto you have a made the little database

#6 Now get some data

  • Open Excel and label four sheets, one for each table : Person, Frequents, Eats, Serves
  • Add data to each table.  Either cut and past the information below, or open this teaching file from Stanford and cut and paste their data.

Person

1 Amy 16 female

2 Bert 20 male

3 Charles 22 male

Frequents

1 Amy PizzaHut

2 Bert Dominos

3 Charles PizzaHut

Eats

1 Amy mushroom

2 Amy cheese

3 Bert cheese

4 Bert seafood

5 Charles cheese

Serves

1 PizzaHut cheese 7.75

2 Dominos cheese 9.75

3 PizzaHut mushroom 8

4 Dominos seafood 11

  • Save each sheet as separate .csv file called persontable.csv, eatstable.csv, etc.

#7 Load your data into your table

  • Go back to your command prompt
  • If you have closed it, then check you are in the right directory.  If not look at #4.
  • Re-open your database “sqlite3 gobble”
  • Tell Sqlite3 that you are uploading .csv files.  Type in “.mode csv” <return>
  • Upload all four files one-by-one: .import persontable.csv person <enter>

#8 Prove to yourself that your data is in the database

  • Type “select * from person;” <return>
  • You should see your data.

There you are. You have made your first database in SQLITE3 and you are ready to play around with it and learn more commands.

CHECK OUT SIMILAR POSTS

Leave a Comment

3 steps to download xmllint

Published October 8, 2011 by Jo Jordan

Xmllint is an XML validator. What does that mean?

XML are the codes, or tags, we put around data so we can send it easily from computer to computer.  xmllint audits my tags for errors.

There are three steps to download xmllint onto a Windows machine.

We will get the code from XMLsoft. Looking carefully on their home page, we see that Ivor Zlatkovic has precompiled what we need for Windows.  I didn’t get mine downloaded correctly first time.  So I have prepared an outline to read before we read the more technical notes from Ivor.

Step 1.   Decide where you are going to store your xmllint files

  • I followed Ivor’s suggestion and created a directory in my C: folder called tools.
  • And then I created a sub-directory in C:tools called libxml
  • The complete name of this sub-directory is C:toolslibxml

Step 2.  Download the software for xmllint to run and put it in C:toolslibxml

  • In addition to Ivor’s page at XML soft, these instructions are also worth reading.
  • Basically, you will download 4 zip files (not 3 as the second set of instructions says).  This involves 4 steps.
  • Step One.  Look where Ivor lists the files for the latest win32 versions of libxml2, libxslt, iconv and zlib.
  • Step Two.  Download all four zipfiles. They will go to your default download folder.
  • Step Three.  Open all four zipfiles and look for their bin directory.  In Windows 7, the zipfiles open without using winzip.
  • Step Four.  One-by-one, copy the files in each of the four bin directories into the folder you made earlier: C:toolslibxml

Step 3: Zdjust your Path statement so that if you can work keep this folder neat and work on XML files elsewhere.

  • Basically, you have three steps
  • Step A.  Find your Path statement.
  • Step B.  Edit your Path statement by adding “;C:toolslibxml”
  • Step C.  Save! And Restart your machine.

Instructions for finding your Path Statement.

Check everything is working.

  • Go to your Command Prompt (Go to Start and type in Command Prompt; Look at the top)
  • Type in “xmllint” <return>
  • A whole heap of information about xmllint should scroll down.

Now you can use xmllint to validate XML.

CHECK OUT SIMILAR POSTS

19 Comments

4 reasons why business students should understand XML

Published October 5, 2011 by Jo Jordan

I found that Bill Gates gives the simplest explanation of why we should at least have an intuitive understanding of XML.

First a reminder of how the internet and world wide web works

1. The internet is made of networks of networks of computers and computer-like devices like our smart phones.
2. We often (though not always) access information on a computer through webpage on a website.
3. A web page is laid out using HTML. For example, my headings on this page have tags (code) around them that looks like this “<h3>my heading</h3>”.

4. There are two more things we should know about this code. First, the code is not visible to us unless we ask to see it.
5. Second, we put the definition of, that is the size, the colour and the font in another set of code called CSS. The CSS might be at the top or bottom of the web page but is probably in another file.  To link up our webpage to that code, we simply put a line of code (invisible unless we ask to see it) telling the webpage where to find the CSS file and what it is called.

And now to why we need XML

If we only intend people to read a web page, then we only need to lay out our page with HTML (to make it look nice) and have a separate CSS (style) file which defines headings and other formats, like fonts, consistently.
But we often want people to use our data as well.

Example of when we want to tag our data with XML

For example, an airline wants me to read its page (so it should look nice and be consistent).
It also wants travel agents to pick up information about its flights and prices and put up-to-date relevant information into their websites.
XML is used to label the data itself (in addition to labelling is style using HTML tags). So the airlines will tag a departure time as , for example. They will tag an airport name as , perhaps.
By tagging the data, another computer knows immediately that some numbers are a departure time or a name is the name of an airport.

Business people need to understand XML

As Bill Gates puts it, “XML ‘unlocks’ data so that it can be organized, programmed and edited”.
Business managers need to understand XML because the need to understand
1. What data is shared in their industry and what the advantages are of doing so
2. How their data can be structured consistently so computers can work with it
3. Where and how XML tags are added so they can plan workflows and maintain systems efficiently
4. How to check and audit XML (unless they want to be taken to the cleaners)

 
In a later post, I’ll make a simple example of working XML so that you can see what you should be looking out for.

CHECK OUT SIMILAR POSTS

Leave a Comment

Testing my theme

Published October 4, 2011 by Jo Jordan

Links break when you move your site from WAMP to your hosting service?

The solution is to set up a parallel Virtual Host on WAMP so that http://mysite.localhost redirects to the default http://localhost/mysite (where mysite is the name of your site).

3 steps to set up a Virtual Host and solve those broken links when you move from WAMP to your hosting service

#1  Get oriented

  • You will only able to see your WAMP based site in your browser after you have turn on your WAMPSERVER by going to Start/All Programs.
  • Look where your files are stored by going into Windows Explorer or My Computer.
    • Look at your C://wamp/ folder
      • You will see c://wamp/www where you store all your websites (right?)
      • And c://wamp/bin/apache/conf/ folder where you see a file called httpd.conf
Leave a Comment

Stop links breaking when you move your site from WAMP to your hosting service

Published October 3, 2011 by Jo Jordan

Links break when you move your site from WAMP to your hosting service?

The solution is to set up a parallel Virtual Host on WAMP so that http://mysite.localhost redirects to the default http://localhost/mysite (where mysite is the name of your site).

Three steps to set up a Virtual Host and solve those broken links when you move from WAMP to your hosting service

#1  Get oriented

  • You will only able to see your WAMP based site in your browser after you have turn on your WAMPSERVER by going to Start/All Programs.
  • Look where your files are stored by going into Windows Explorer or My Computer.
  • Look at your C://wamp/ folder
  • You will see c://wamp/www where you store all your websites (right?)
  • And c://wamp/bin/apache/conf/ folder where you see a file called httpd.conf
  • Look at your C://Windows/ folder
  • Track to c://windows/system32/drivers/etc folder where you see a file called hosts
  • Inside the httpd.conf folder, we will list Virtual Server for each (and every) site that we are developing on WAMP for later export to a hosting service
  • That means you should edit this file every time you start work on a new website and clean it this file when you finish work on a website and remove it from your laptop.
  • Inside the hosts file, we are going to tell Windows (as distinct from WAMP) that when it sees a reference to mysite.localhost, it should redirect/loopback to 127.0.0.1 which is the IP address of your computer (and mine when I am working on mine.)
  • Windows 7 will stop you editing this file and we must work around that restriction.
  • Of course, you must do this for every site you are working on and remember to clean off what you no longer need at the end of a job.

#2  Set up your Virtual Server

  • Go to the httpd.conf file and open it in a text editor (Notepad or Wordpad)
  • Look for the line that reads: Servername localhost :80
  • Add the following text below this line.  Substitute your sitename for mysitename and remember to duplicate the last four lines for each website that you are working on

 NameVirtualHost *:80

<VirtualHost *:80>
DocumentRoot “c:/wamp/www/”
ServerName localhost
</VirtualHost>

<VirtualHost *:80>
DocumentRoot “c:/wamp/www/mysitename”
ServerName mysitename.localhost
ServerAlias mysitename
</VirtualHost>

  • Save as httpd.conf (don’t forget the extension)

#3 Tell Windows to redirect from mysitename.localhost to 127.0.0.1

  • Windows 7 will discourage you from editing the hosts file.  This is your workaround.
  • Go to Start and type in Notepad
  • Right click Notepad and open as Administrator.
  • Track to the hosts file (see #1) and open the file.  You may have to change the setting from Textfiles to All Files.
  • Look at the last line. It probably reads 127.0.0.1 localhost.
  • Add another line reading 127.0.0.1 mysitename.localhost
  • Close and save

Check you are redirecting your site successfully

  • Confirm nothing is broken
  • Go back to WAMPSERVER (look for the W, bottom right on the task bar)
  • Select localhost
  • Open your website and others too
  • Close the other tabs so there is no confusion
  • Enter the link above.
  • Everything still working?
  • Prove your redirect works
  • Shut all the tabs
  • Does your site open correctly?

 

Set up links that don’t break when you export your site

Now you can set up all your links without them breaking when you move your site.  Hmm. . . not sure this is the last word here.  I suspect the format is important.

 

CHECKOUT SIMILAR POSTS

Leave a Comment

5 minute install of DRUPAL on WAMP

Published October 2, 2011 by Jo Jordan

Install Drupal with WAMP

Situation: To develop DRUPAL on my laptop

I want to download DRUPAL onto my laptop so that I can develop DRUPAL websites on my laptop before I load them up to my hosted server accessible by the public.

I already develop WordPress and Wikis on my laptop and with a local server WAMP.  I got the instructions to download WAMP from Lifehacker.

Mission: A 5 minute install of DRUPAL on WAMP

I want to download Drupal and set it up successfully.

Execution:  Critical steps for downloading DRUPAL quickly and successfully first time

#1  Check my directory structures

  • C:WAMP
  • C:wampbinapached
  • C:wampwww. . . .[all my development sites]

#2  Make  a directory to receive DRUPAL

  • C:WAMPwwwmynewsite

#3  Start WAMPSERVER

  • Go to Start/All Programs
  • Active WAMPSERVER (my installation asks me to confirm permission)
  • Look for icon in the bottom right of the task bar (W)
  • Check options ( localhost, phpMyAdmin, PHP which includes php.ini)
  • Go to localhost and see the folder you create “mynewsite” (though there is nothing in it yet)

#4  Set up my new SQL database

  • Go back to the WAMPSERVER menu (W) and select phpMyAdmin
  • Look in the middle of the page for Create database
  • Insert the name of the database.  I usually use the name of the website. Hit Create
  • Check your privileges.  If you set up your WAMPSERVER using the instructions from Lifehacker, then you will have two root users and yourself and all three users have global privileges.  Note your username (!) and recall your password.  You will need them shortly.

#5  Download Drupal and unzip it into your folder : c:wampwwwmynewsite (or whatever you called it)

#6  Install Drupal

  • Go back to the WAMPSERVER menu ( W on taskbar bottom right)
  • Select localhost
  • Select “mynewsite” (or whatever you called it)
  • You should be looking at the Drupal installation screen.

#7 Activate the DRUPAL installation programme

  • Select standard install
  • In English (assuming that is your preference)
  • Leave the selection as SQL
  • Insert the name of your database
  • Insert your username (see step 4 above)
  • Insert your password
  • Let DRUPAL do its thing

#8  Now do as DRUPAL asks and put in the email address, admin name and password for the website

  • NB These are not the same as the database in steps 4 and 7.

#9  Go to configuration

  • Make sure “clean urls” (pretty permalinks) is ticked.

Confirmation

I now have a working DRUPAL website accessible through my WAMPSERVER/local host or through my browser http://localhost /mynewsite and in under 5 minutes.

It’s ready for tweaking and development prior to being loaded up to a hosting service for public use.

CHECK OUT SIMILAR WEBSITES

2 Comments

The last rule of Word and Outlook email merges that no one told you about

Published August 28, 2011 by Jo Jordan

Earlier today, I wrote on the first rule of Word and Outlook email merges – make the email account you will send from your default, and make its corresponding data file (.pst) the default data file too!

The last rule that no one tells you about either is make sure Outlook is open before you send.  When Outlook is closed, the messages will send from Word, but won’t leave Outlook.  The messages will sit  there until you open Outlook and hit the send button!

So the last unspoken rule is open Outlook before you hit the final merge and send button in Word!

CHECK OUT SIMILAR POSTS

3 Comments

The missing first step of Word & Outlook email merge

Published August 28, 2011 by Jo Jordan

I set up an email merge using Word and Outlook 2010 this weekend and in my test run, my emails were sent from my personal email address not my business email address.

My first thought was to change the default account.  But that turned out to be only half-the-solution. We also have to change the default data file (.pst).

Here is the missing first step of email merge in Word and Outlook in ten micro-steps

  1. Choose the sender’s email address (which one of your email addresses do you want to use?).
  2. Open Outlook and go to File at the top left of your screen.
  3. Look down the list and at the bottom, select Info – it is near the top.
  4. Look in the big box and at the top is the email address that you are using (default or recent).
  5. Use the drop down list of accounts to choose the address to send from.
  6. Click on account settings and AGAIN on the information box that opens up (odd but that’s what you have to do).
  7. Look at the tabbed box that opens up – we will use the tabs for Email and Datafiles.
  8. Select the email address that you want to send from and then make it the Default.  The Default button is between the tab and the list of files.
  9. Look down to the bottom of the list and see which datafile (.pst) matches to the address that you have chosen.
  10. Go the the Datafiles tab.  Open it and make the datafile (.pst) that matches your new default email address.

When you return to the ordinary message view, your folder list should have re-ordered itself with the new default folder at the top.  I am not sure if you have to close Outlook and restart for just a change of defaults.  If your test still sends from the wrong email address, try closing and opening Outlook before you start looking for anything more complicated.

Remember: the first step in an email merge is not opening Word.  It is choosing which email address to send from and making the email address and its corresponding datafile (.pst) your default in Outlook.

CHECK OUT SIMILAR POSTS

14 Comments

Two Important Tips when using WordPress on a local server like WAMP

Published July 2, 2011 by Jo Jordan

How to use WordPress on your laptop

Some basics first.  Beginners use the free, hosted WordPress blogs on www.wordpress.com.  The next step up is to self-host your blog by using free software from www.wordpresss.org at a self-hosting service whom you pay around USD10.00 a month.  The hoster, such as Dreamhost host a lot of WordPress blogs and will set up the basic shell for you within about 10 minutes.

If you want to be a  little bit more ambitious and make or edit your own theme, then you can turn part of your own computer into a self-hosting service.  The first step is to download  WAMP (assuming you use Windows).  WAMP gives you an Apache server, mySQL databases and PHPadmin.

All you do to get everthing running is

  • Download WAMP into a directory c:/wamp (follow the download instructions)
  • Set up passwords for root and IP 127… at PHPadmin activated following WAMPserver in Start/All Programs
  • Use Windows Explorer to go to c:/wamp/apps/PHPadmin and edit your password the config.inc file

$cfg[‘Servers’][$i][‘auth_type’] = ‘config’;
$cfg[‘Servers’][$i][‘user’] = ‘root’;
$cfg[‘Servers’][$i][‘password’] = ‘yourpassword’;

  • Set up  a new database ready to receive your wordpress.
  • Make a new directory under c:/wamp/www and upload an up-to-date version of wordpress from wordpress.org
  • Edit the config-sample.php file following instructions at wordpress.org

Two important tips to using Wordress on Wamp

  1. When you edit the config.inc file for WAMP save it using the Save As command.  Just closing the file updates the text but throuws up an error.  Not sure why but i wasted an evening on this.
  2. When you are working with a fresh installation of WAMP, go into into its menu box and make sure the module – rewrite is on.  If mod_rewrite is not on, you will not be able to use “pretty” permalinks.  Your wordpress will say you are using them but when you try to view the page, you will get an error because wordpress cannot find the page in mySQL even though it is on!

Two time-consuming obstacles that you can avoid!

Leave a Comment

Run a Python program on every file in a directory

Published May 24, 2011 by Jo Jordan

Use the cmd line to run a python program on every file in a directory

It took me several hours to get the command line right to run a python program on every file in directory and send the results to the same output file.

I am jotting down my notes here because it seems that much of the advice on the internet is plain wrong.  This is what worked for me

Assuming

  • We are working on a regular computer using Windows
  • Python is already installed
  • We have a Python program called myprog.py
  • We have data we want to analyse with that program
  • We have many, many data files in a directory and we want to analyze them one-by-one and put the results in one file.
  • The program file expects one or more data files as sys.argv. That is, the program expects us to tell it where to find the data files in the cmd line using this format.
    • Myprog.py sys.argv1 sysargv2 sysargv3  >output.txt

If we only have a few data files, or we are only using a few files in a directory, then we don’t need this procedure. It is easier simply to type out the command as shown above.

If we want to run the program on a thousand files, say, it would be tedious to type out the filenames (in place of sys.argv).

Gist of procedure to use cmd line to run a python program on every file in a directory

To run a python program on every file in directory, we use the for command.  Here are the steps in an orderly way.

Steps:

#1 Locate the directories of the python program and the data

For example

Python program physically sits in c:python27myprog.  The name of the program is progname.py.

Data physically sits on a removable drive e:mydata

#2 Decide where we are going to send the results

I always send my results to the same directory as the program but that is not necessary.  The reason I do that there is less chance of a type if I don’t have to type in the directory name and it is relatively easy to move a file to another directory manually.

In my example

My results will go to the same file as the directory c:python27myprog

#3 Open the cmd line in Windows

We are going to give commands directly to DOS, the operating system underneath Windows.  You may dimly remember it from 1980’s computers.  Yes – it still runs Windows machines.

  • Go to Start (bottom left – where you normally switch off your computer)
  • Select Run
  • Type cmd<enter> into the box : a black square should pop up.

#4 Set the directory of our program as our working directory

In the “command line window”, you should see something like c:mydocuments . .  Windows machines try to make us work in My Documents.   I change this to my program directory using

cd c:python27myprog

That means change directory to c:python27myprog

#5 Use the cmd line to tell python to run myprog iteratively with every file in the data directory

for %f in (e:mydata*.txt) do myprog.py %f >output.txt

Notes

  • Look at the syntax : for  variable in (set) do myprog.py variable >outputfilename
  • The variable name must be %f or %x or %t.  A percentage followed by one letter. Everything else throws up an error.  The variable name must be the same in both uses.
  • The directory holding the data is put in brackets : (dirname)
  • >outputfile.txt sends results to a textfile in the same directory as our program.  It must be a .txt file.

Conclusion: Use cmd line to run a python program on all the files in directory

And remember that if the program takes 1 seconds to run for one file, then for 1800 files, as I am running now,  then  the program takes 30 minutes to run.  If the original program takes 2 sec to run, the total batch will take an hour.

Leave a Comment