Lies, Damn Lies, Statistics, and SQL - Capturing the Data
(Page 3 of 6 )
Select all of the articles and related information on the PHP page, avoiding the table header, and copy them to the clipboard using menu options, or simply by simultaneously pressing “Control” and “c”. We’re going to use Access, so I’ll assume you are running Windows. Open WordPad – make sure you don’t use Notepad. Paste the contents of the clipboard into WordPad. Looks like quite a mess doesn’t it? I’ll bet you’re wondering how this can be any use to us – have faith and you’ll see shortly.
We are now going to save this file in text format so choose “Save As” from the “File” menu option. You should be presented with a file dialogue box. Choose “text document” as the file type and change the file name to “hits.txt”. Make sure you use the “.txt” extension. Access is picky about extensions when importing files and misbehaves if it doesn’t get its own way.
Before proceeding let’s have a look at the “hits.txt” file using Word. Right click the file name and open the “Send To” option. If you don’t find “MS Word“ you can add it by simply putting a shortcut in the “Send To” directory. If you don’t want to do this just open Word and open the file, making sure that you select the right file type from the “File Type” dropdown box.
It doesn’t look quite as funny as it did previously – in fact it looks rather commonplace. To understand what’s going on the formatting marks need to be displayed. This is done from the “Tools” menu, by clicking “Options”, and then the “View” tab. Under “Formatting marks”, check the “All” box. Now you should see the text separated by “arrows” and funny looking backward “P’s”. You’re looking at tabs and carriage returns. In order for Access to import a text file it needs to be able to tell where the columns and rows end. The tabs will separate columns and the carriage returns will separate rows.
Your first line of text should look like the following:
If it does then everything should probably work just fine. Make sure you don’t resave in Word format.
Next: Into the Database >>
More SQL Articles
More By Peter Lavin
