FoxPop
 

www.foxpop.co.uk

 

 

POWERBASE TUTORIAL 5b

By Laurie Jane Kern

Exporting to a Text File and Merging.

 

Before beginning on this section it might be a good idea to briefly review Tutorial 5a. There you first exported all of the records and just some of the Fields from the Contacts Table to a DATA file. Then you imported some of the Fields from the DATA file to a new Table called MYTEST1

This tutorial, 5b, will cover exporting to a TEXT file and merging data from a Text file into an existing Table.

Firstly make sure you have the database from the conclusion of the last lesson, or download it now SampleAdBook(04). Start by opening the newly created Table MYTEST1 in the List or Card View. You have 6 names in the Table and you are going to delete the first record, Ms. Jane Austen, so you can bring her back, by using an export from the Contacts Table.

So, set focus on her record and go to Menu| Records|Delete record. You should then see the message box below. Go ahead and press Yes.

tut5b.1.gif (14416 bytes)

 

Exporting to a Text File

Now open up the Contacts Table in List View and just set the focus on the window. Then using the Find box at the bottom of the screen enter the first part of Ms. Jane Austen's name (AUST) and press the return button. This will find Ms. Austen and make her record the only record selected. Notice the bottom of the Contact window reads "1 of 1".

tut5b.2.gif (12860 bytes)

Make sure you have the focus on the List View within the Contacts Table. Now go to the menu and File|More|Export Group... You will then be presented with the "Export dialog box" as shown below, it shows that you are exporting from the Contacts Table. On the File tab, select the default file type of Text, name the file TEST2.txt and create it in the Documents folder.

tut5b.4.gif (14060 bytes)

When the File tab is completed go to the Text Options tab, which is now enabled. In this instance check that you want Field Names in the first row exported - this is handy for many desktop applications. We will be using a Comma Separated Values format, which specifies that all text strings start and end with double quote and a comma separates each Field. There are other text qualifiers and field separators ; you will be using some in a few weeks when I show you how to build a email distribution list; but back to the matter at hand! When you have set up the options as shown in the screenshots, click the OK button.

tut5b.5.gif (15664 bytes)

We are once again taken to the Export screen. If you do not recognise it from the previous tutorial, then pause here and return to Tutorial 5a to review it. On the left half of the screen is a list of all the Fields in the Table that you are exporting from. On the right is a list of Fields in the View you are using. This time you are exporting all the Fields, so when you are ready click the "Export" button in the upper left corner. When PowerBase has finished exporting you will be taken back the main window and the program will flash "Completed" in the upper right corner.

Now go to your Documents directory and find the TEST2.txt file and open it. It should look like the screen shot below. You will see that there is only one real record exported, though expressed in "two" rows of information. The first row contains all the Field names and the second row contains all the data about Ms. Jane Austen from the Contacts Table. Also notice that each "Text String" is surrounded by double quotes, except for the last field which was the ID, and a number field.

tut5b.7.gif (9835 bytes)

Congratulations! You have successfully exported to a Text file and used a very simple filter to export just the record you wanted.

Merging into an Existing Table

So, now you have a Text file that you can use as a sample to merge into an existing Table in your PowerBase Sample Address Book. You will now put Ms. Jane Austen BACK into your MY TEST1 table; this is why you removed her, to prove to yourself that Jane Austen exists :)

Open MYTEST1 Table in Card or List View and notice that there are only 5 records. Set focus on the first record and then go to the menu and select File|More|Merge into table...

You are then shown the "Merge into MYTEST1" dialog box. The program is letting you know what table you are going to be merging into. Now select the FROM file type as Text and enter the file name as TEST2.txt if needed, and navigate to its location in the Documents Folder (as shown in the next screen shot)

tut5b.9.gif (14269 bytes)

Wait - there is more! Don't forget the Text Options tab, fill these fields in as shown and then click OK

tut5b.10.gif (14179 bytes)

PowerBase then takes you to the "Import" screen, which I hope will look familiar by now! PowerBase has listed the Fields it found in the Text file on the left side of the window and it shows the Field types using the icons you saw when you explored the Fields section in the Schema (Tutorial 3). On the right side of the import window are all the Field names that are in the Table you are merging into (together with their icons). Now, when this window first opens up, it does not look exactly like the one shown below. You MUST drag the Fields you want to merge from the Text file OVER to the Fields you want to MERGE them INTO. This is sometimes known as data mapping. You are telling the program what Field in the file MAPS to what Field/location in the Table.

But what if you don't want to import all the Fields? Well, you don't have to map them all! As you can see below, I only mapped some of the Fields. Look at the screenshot below and see the Fields I have selected for import, having dragged the ones I want over to the right-hand side. I am only going to merge 4 Fields.

tut5b.11.gif (12010 bytes)

When you are ready, click the "Merge" button and when it is done you will get a quick message saying the merge is completed. Now when you look at the MYTEST1 Table, there are 6 records again, and Ms Jane is the last one! NB. All new records are placed at the end of the table. If you change the sort order, of course the record number will change, but trust me she was added to the end of the list! In the screen shot below I have the card view showing the 4 Fields we merged in, and if you compare it to the screen shot at the beginning of this tutorial you will see that the data is the same, there is just less of it!

 

tut5b12.gif (12542 bytes)

 

Conclusion

Before we close, let me suggest that you look back at the conclusion of Tutorial 5a, where I listed some information about the advantages and disadvantages of the different file formats. I should also add now that when you IMPORT to a new Table using a Text file, all field lengths are set at 255 characters and should be set to a smaller number after determining the best length for the Text Field.

I will also mention again that a MEMO field, exported to a Text file is truncated at 255 characters and that when using a Text file, a string that is longer than 255 characters cannot be merged into a MEMO field. If you have a text string that is more than 255 and you want to merge into a Table, then I suggest that you merge it in to a Text field set at 255 - and either copy and paste to the MEMO Field OR leave it in the Text field and leave the Field length at 255.

I have shown you in tutorial 5 how to export to 2 different file formats, how do use one format to import into a new Table and to use the other format for a merge to an existing Table. Remember you are not limited to these two scenarios. To recap, you can export to either a Text or DATA file, you and also use either of these file types for an import to a new Table AND you can use either of these file types for a merge into an existing Table.

You can download SampleAdBook (05) which takes you to the end of tutorial 5b, and includes all the imports and amendments which have been the practical aspect of the tutorials so far.

© LJKern and FoxPop 1999

[Previous] [Index] [Next]