Exercise 2: Import data from the text files PostCast has superior Wizards used to import and export text files. The program is able to manipulate with any database text file format. Now we shall deal with importing your data from the text files to the program. Assume that you have a CGI script on your server that makes a log file with data picked up from the web form. Your data would probably look as in the example below: EXAMPLE 1 Johnson|Rick|Advanced Communications|10235 Panorama Ave|Portland OR 97233|USA Smith|George|HLS Designs|28935 Baker St|Sacramento 95820|USA There are many ways to enter data into text files. See the below example, for one: EXAMPLE 2 "Last Name","First Name","Company","Street","City","Country" "Johnson","Rick","Advanced Communications","10235 Panorama Ave","Portland OR 97233","USA" "Smith","George","HLS Designs","28935 Baker St","Sacramento 95820","USA" Note that this inscription consists of three lines, the first of which includes column names. In the first example the data was distinguished using the Pipe (|) character. The character which distinguishes the data is called a 'delimiter'. In the second example the delimiter role is taken by the comma (,). The quotation mark (") is a character that we shall call 'text qualifier' in this case. So, let us begin: 1. Select 'Tools/Import data' option from the program menu. You have just started the Import Wizard which will import all your data from the text files into the defined table. (note: in order to import the data successfully you should have a pre-defined database within the program. Read Exercise 1 to learn how to do it.) 2. Meanwhile, if you had defined the database using the program, 'Database file' text box would contain its path. You can also enter location of your database if you click the 'Browse' button. Provide a path for the database file wherein you would like to import the data. 3. The 'Import file:' text box should contain the text file path which, in turn, contains the data. Click the 'Browse' button and provide the path to 'Exercise 2.txt', which can be found in the PostCast directory (usually 'C:\\Program Files\\PostCast\\Exercise 2.txt' file). Click the 'Next' button to continue. 4. Now we should define the saving format. Please try to remember the introduction and all that has been said about delimiters and qualifiers. In this case we will be using example 2. The first couple of text lines would be displayed in the text box at the bottom of the screen, thus enabling you to easily determine the delimiter and the qualifier, in your particular case. Therefore, click the 'Comma' option box in order to define the delimiter. Then enter the quotation mark (i.e. "), as the text qualifier, into the 'Text Qualifier'. Select the 'First Row Contains Field Names' check box, as the first line in Example 2 contains column names. What should we do if the data are arranged like this? atm@hotmal.com hans@peoas.net 2u@gate.co.uk In this case we have a character distinguishing your data. This time the delimiter is the the new row behind each e-mail address. How will you input this into the Wizard? Click the 'Other' option box and then the empty text box on the right. Press the 'Enter' key on your keyboard. You have just introduced a new row into the text box and thus made the program aware that, in this situation, your data is distinguished by a new line. Click the 'Next' button. 5. If you have already created the database following the instructions in Exercise 1you will already have selected tables: 'Customers' and the 'I would like to store my data in an existing table' option. This means that the data from example 2 will have to be imported to your Customers table. Click 'Next'. 6. In the next wizard screen, you may change the column names from the text files. Since our data from example 2 contains column names in the first line, and since we had selected the 'First Row Contains Field Names' check box when we were defining delimiters and qualifiers, your column names will be displayed on the left side of the screen. How would your columns be displayed in such a way as to show the data import from example 1 which are distinguished with a Pipe (|) character that do not contain the column names in the first line? Answer: you should define the column names manually. For instance, you would enter Last Name into the 'Field Name' text box, click the 'Add' button, then First Name click the 'Add' button with respect to Johnson|Rick line. Click the 'Next' button to go to the following screen. 7. In this section of the Wizard you are supposed to specify to the program which column from the import text file coincides with one of the columns from your database table. All the database columns will be shown on the left, while all the columns defined in step 6 will be displayed on the right. If you have followed the above instructions, your screen should look like this: In order to set up the program successfully you should position identically the named columns on the left within the same plane with those on the right. For example, you would click Last Name column on the left and, using the up and down arrows which can be found at the bottom of the screen, move the inscription until it coincides with the Last Name column on the right. You should do likewise with other columns. Click the 'Next' button to pass to the next screen. Note: if you have followed the above instructions you would not need to adjust and compare the columns. They would be adjusted automatically. Columns on the left side of the screen that have no corresponding pair on the right would not be taken into consideration during the import and they would occupy a blank space. 8. the next Wizard screen enables you to filter the import data. This option will not be further discussed, and we shall suppose that you want to import all the data from the text file. Click the 'Next' button to proceed to the next screen. 9. Click the 'Finish' button to import the data. You will get a 'Your data has been imported to the database' message, provided that everything went OK. Click the 'Database' button from the Toolbar and then 'Refresh' within the database program section. Your screen should look as in the figure below: We have created a database with a single, primary, Customers table, and then imported the data from the text file to the table quite successfully. Now you are on your way to learning about other advanced options. We shall thus proceed to a very important option: Extract data from web forms.