CSV stands for "comma-separated values" and is the name given to files consisting of lines of data, each of which contains individual data items separated from each other by commas. Such files are widely used to mail-merge using a wordprocessor (see Ch 9) and also to transfer data from one application to another, e.g. from a database to a spreadsheet or from a RISC OS database to a PC database. We will refer to each line in a CSV file as a record and each item of data in such a line as a field since records and fields are the source and destination of such data when it is exported from or imported into a Powerbase database. The following points should be noted:
(1) Fields which are non-numeric (e.g. plain text items such as names and addresses) are often enclosed in double quotes ("") whereas numeric data is not. This makes it possible for an application reading a CSV file to distinguish between numbers and strings (which might of course contain numerals) and also allows a comma to be used as a character within a string without being mistaken for a data separator. (Addresses often contain commas e.g. 112, Keighley Road). For many purposes the quotes aren't necessary and may be omitted.
(2) Null fields are usually included and can be located by looking for two commas with either nothing in between them or with only two double quotation marks between them, i.e. ,, or ,"",. Each record in such a file will always contain the same number of fields. Powerbase does, however, allow nulls to be omitted entirely when creating a file in which case the number of fields per record will vary.
(3) The way in which each record is terminated varies from one system to another. The last field in a record is followed not by a comma but by a line terminator. On RISC OS systems this is the same as in ordinary text files; the linefeed character (LF, ASCII value 10) and on PCs it is usually the carriage-return character (CR, ASCII value 13). You might, however, encounter CSV files in which both these characters are used, i.e. LF CR or CR LF. You can define any character or character-pair as the record terminator.
(4) Separators other than commas are sometimes used. The Tab character (ASCII value 9) is often used and such files are called TSV ("tab-separated values") files. Powerbase lets you define any character, or even a pair of characters, as the field separator. All such files created by Powerbase will be of type &dfe and display the CSV file icon (although the default filenames offered do differ: "CSVfile" where the separator really is a comma, "TSVfile" where it's a TAB and "?SVfile" otherwise). In what follows we will, for convenience, refer to them all as "CSV files" whether or not the separator is a comma.
(5) Some applications which accept a CSV file as input expect the first line to contain the names of the fields which comprise the subsequent records, e.g. if each record consists of a name and a four-part address this header record might read:
Powerbase can both export and import files with such a header. If importing a file from a non-Powerbase source you will need to substitute the correct field tags for the names.
Choosing Export CSV file => Options from the main menu displays the CSV options window which lets you specify all the file characteristics described earlier. Pop-up menus give you a choice of field-separators and record-terminators with space to enter your own if you wish. The first three option buttons cause an exported file to have, respectively, the following characteristics when the button is selected:
Having set up your options as described above, creating a CSV file is very like printing a list. First highlight the fields to be exported by clicking on each with ADJUST. Remember that the order in which the fields are highlighted is important. If a default print selection exists (i.e. a file in PrintRes called !Selection) it will be used to determine which fields are exported provided no alternative selection has been made.
Next choose Export CSV file =>Export (Ctrl X). A window featuring the Query panel appears. Type in a search formula to determine which records are exported. Finally, enter the name of the file and drag the file icon to a filer window. By default the file is saved in PrintJobs as usual, and you may simply click on the Export button or type Return. Experiment with saving CSV files with different settings of the CSV options and then loading the resulting files into Edit to examine them. If you select the Reverse button on the Query panel the CSV file will be created in reverse order. (See also 3.7).
The field-concatenation option (see 3.2.2) applies. This means that data which occupies separate fields in the Powerbase record need not do so in the exported CSV file. By holding down Shift when selecting the field with ADJUST, the comma (or other separator) which would normally follow is suppressed until you select a field without using Shift. A slightly problematic situation occurs when you concatenate a mixture of numeric and non-numeric fields with the In quotes option selected. In such a case Powerbase will enclose the whole concatenated group within two sets of double quotes. e.g. NAME, Z, M and SYM from the Elements database would be exported, for actinium, as:
The Spacer (see 3.10.2) - in the above instance it is the default setting of one space - is used to separate the concatenated fields. To export the displayed record only hold down Shift when starting the CSV export.
When you include a Scrollable list as one of the fields in a CSV file it will be written as a single CSV field if the format is selected in the Print options window is As single line. The character used to separate data from different rows of the Scrollable list is set in Scroll term in the CSV options window and the default is a semicolon. The data from individual cells in the same row of the scrollable list are separated by the same CSV separator as is used between CSV fields.
The above description might seem confusing but will become clearer if you export data from the sample database Scroller using the default settings but with In quotes selected in CSV options. You will see that the entire Scrollable list from each record is enclosed in double quotes, between which the items from within each row are separated by commas, a semicolon marking the end of each row.
A file exported in this way can be re-imported into a Powerbase database. You must ensure that the scrollable list data goes back into a scrollable list field, although it need not have the same number of columns as the one from which it was exported. If the new list has more columns than the old there will be one or more blank columns on the right hand side; if there are fewer columns data from the missing columns will be discarded. This closely resembles what happens when there are more or fewer fields in the database than in the one from which the file was exported (see 8.4.2).
If you change the CSV printing format in Print options from As single line to As columns and export the CSV file again (in quotes as before) you will see the contents of each row enclosed in double quotes and separated by commas, the items from within each row being also separated by commas.
Exporting this second file without the quotes (or the first file if you first change Scroll term to a comma) will result in a file in which every single cell of the scrollable list appears as a separate CSV field, separated from the next by a comma. This might prove useful if transferring data from a database which contains a scrollable list to one which doesn't.
Transferring data from and to scrollable lists in individual records was covered in 2.6.4.
8.4 Using CSV files to import data
If you drop a CSV file on the record window of an open database the CSV options window (see 8.2) appears with the title changed to "Import CSV file" and some additional icons, one of which displays the pathname of the file. Clicking on Import will make Powerbase try to create new database records from the file. If you decide not to do this you should click on Cancel. If you do wish to import the data there are some important consideration which will now be explained.
If the CSV file originally came from a Powerbase application the settings in the CSV options window need to be exactly the same as they were when the file was exported. The exception to this is the In quotes button which is shaded on import because Powerbase doesn't need it. If the CSV file came from a PC or another RISC OS application you might have to load it into Edit to find out what separator and terminator are used. You will also probably need to set the filetype to &dfe (omit the "&") as well.
There are three more option buttons at the bottom of the window. One causes each record to be displayed as it is imported. The import process is slower with this turned on but much more informative if you like to know how things are progressing. The second button, when selected, strips any trailing spaces in the imported data-fields. Some database programs pad all fields to their maximum length by adding spaces to the end of the data where necessary. If you import such a file into Powerbase you will find that the caret will always be at the far right of the field even though visible characters do not fill the field, and some queries won't work properly. Setting the Strip spaces button before importing the file overcomes the problem. The third option button determines how Sequence number fields are handled. If the button is ON imported sequence numbers are ignored and new ones assigned in accordance with the field's sequence number counter. With the button OFF sequence numbers from the CSV file are imported without alteration.
If no fields on the record window are highlighted (i.e. with ADJUST) and the CSV file does not contain a header record (see 8.1) then the import process proceeds according to the following rules:
(1) Highlight the required Powerbase fields with ADJUST before starting the import. Data will then be read only into the highlighted fields, all other fields being ignored. The order in which the fields are filled is the order in which you highlighted them. The rules given above about underflow and overflow of data still apply.
(2) Give the file a header record containing the tags of required fields in the Powerbase record. There is nothing to prevent you from using Edit to add such a header to a CSV file which did not originate from a Powerbase application. The format of the header is illustrated in 8.1 (5). Importing then occurs just as if those fields were highlighted. Do not use both a header and highlighting.
It was explained in 8.1 (4) that files created as described above (8.3) can have separators other than a comma. If the file has been created using Powerbase's CSV exporting facility it will have been given the file-type &dfe as if it was a true CSV file. If it comes from some other source it is likely to be of type &fff (Text). It can still be imported but caution is needed because there are other circumstances in which a text file might be dropped on the record window. A properly-written script file (see Ch 12) would be recognised as such and therefore cause no problem, but any text file dropped onto the appropriate type of External field (i.e. a Text, Text Block or Remote field) would become linked to that field instead of being treated like a CSV file. If you are importing data from a plain text file and your record contains fields of the aforementioned types be sure to drop the file on the window background, not on the External field The appropriate window will then appear with the title "Import text file".
You are strongly advised to use proper CSV files if at all possible. You could, in fact, use the filer to set the filetype of such non-standard files to &dfe (omit the "&") so that the problem of Powerbase taking the wrong action doesn't arise. Be sure to use Options to set the correct field separator and record terminator though.
There are two situations in which this can happen. The database might not contain enough free records to hold all the imported data and so you get a "Database full when reading CSV file" error. To avoid this either make sure the database is big enough before you start or place a suitable value in the Increment for expansion icon in the Change length window. The latter is accessible from the Utilities submenu of the iconbar menu (see 10.5).
The second situation is where an item is too long for the destined database field. When importing data Powerbase maintains a file called TooBig inside the database's PrintJobs directory. Anything which won't fit in the target field is instead written to this file together with information about where it was intended to go and an "@" character is placed in the database field to draw your attention to it. No writable Powerbase field may be longer than 246 characters and if an item of imported data exceeds this a note will be made in the TooBig file advising you to define an External field (Text Block or Text) for such data. After completing a CSV import operation it is advisable to look at the TooBig file to see if any remedial action is needed.
Most database programs which support CSV import allow it to be used only for creating new records. Powerbase is unusual in that you can use a CSV file to modify existing records. This capability should be used with caution since careless use can irrevocably garble a database. There are three relevant radio buttons in the CSV options window. They are called Modify existing, With primary key and With record number and their actions are as follows:
Modify existing No new records will be created when a CSV file is dropped onto the record window. The existing records will be accessed in the order determined by the current index and the new data will be merged into these records. You should, of course, either use ADJUST to highlight the fields into which the data is to go or place a header in the CSV file specifying the field tags and then turn on the With header button (see 8.4.2). If all the records are modified before the end of the CSV file has been reached a warning message is displayed. Note that it is the user's responsibility to ensure that the data in the CSV file is in the correct order since Powerbase has no way of telling which data is destined for which record and can only proceed sequentially.
With primary key This affects both export and import. When you export data with this button selected each record of the CSV file includes the primary key of the Powerbase record. (Try creating a CSV file with and without this option set and compare the files using Edit.) When importing such a file Powerbase will attempt to locate records with the same primary keys as the records in the CSV file. If a matching key is not found a new record will be created to receive the data. If the key does exist then the CSV data goes into the same record, overwriting any data which the target fields already contain. The option is only useful for transferring data between Powerbase databases which have the same primary key field(s) and structure. Don't forget that Powerbase, by default, allows duplicate primary keys so it is possible for the new data to go into the wrong record. You shouldn't trust this method of import unless you are sure each primary key is unique.
With record number This resembles the previous option. It allows data to be exported with the Powerbase record numbers included. Importing into another (or the same) database places the data in records having the same record numbers, again overwriting data which may already be present in the target fields. This option is only useful for transferring data between Powerbase databases in which corresponding records have identical record numbers. One use for it might be to export a set of data, load it into an editor and do some extensive searching-and-replacing, then put the modified data back into the original database.
When any of the above three buttons is selected, clicking Import will warn you what is about to take place and ask for confirmation. If you cancel the operation the radio button will be deselected and the normal default (Create new records) selected instead.
It was explained in section 5.6 that the contents of a validation table can be exported as a CSV file. You may also import data by dropping a CSV file onto the table. The Options window appears as described in 8.4 so that the appropriate separator and terminator can be selected if necessary.
It is not possible to increase the number of rows or columns in a validation table by this method. Only those items for which a space exists will be imported; the rest are ignored. Over-long data items are truncated. If a CSV file is dropped onto a table with more columns than there are fields in the CSV record, one or more columns in the table will be left blank.
This facility makes it possible to convert an old-style (i.e. non-modifiable) validation table to a new-style (modifiable) one without having to re-type all the data. First export the data as a CSV file, then create a new table (which may have more rows and/or columns than the old one if you wish) and drop the CSV file onto it. If you give the new table the same name as the old one the former will overwrite the latter when the database is closed.
There is an option button in the CSV options window called With field data which only becomes active when the With header button is selected. With the button selected the header record of a saved CSV file contains not only the field names but also the field lengths and types. Again it is suggested that you create a file and look at it in Edit.
Each item in the header contains four items of information separated by | (vertical bar) characters. These are, in order:
(a) the field type number (These may be examined in the file !Powerbase.Resources.ValStrings.)
(b) the maximum length of the field in characters
(c) the field descriptor
(d) the field tag
The CSV separator, as used in the remainder of the file, is used to separate each of these groups from the next. Assuming the separator to be a comma the structure of the header is:
<type>|<length>|<desc>|<tag>,<type>|<length>|<desc>|<tag>, ...
It is quite possible to take a CSV file from a non-Powerbase source and add such a header to it by hand. The field-type can be omitted, in which case the type is set to 0 (Unrestricted) and remaining number is assumed to be the length. It is also possible to omit either the descriptor or tag, but not both. If only one string is supplied it will be used for both descriptor and tag. The minimum specification for each field is therefore:
You can force a null descriptor by putting two bar characters together (a null tag is, of course, not allowed):
A file created in this form is not meant to be dropped onto the window of an open database. It should be dropped onto the Powerbase icon on the iconbar when no database is open. There is enough information in the header to enable Powerbase to convert the file into a functioning database. All the fields will be ranged on the left of the record window, one beneath another and the primary key will consist of the first four characters of the first field, but these characteristics can be changed if desired. Don't try to create databases containing Check-box, Button or External fields using this method; it's only meant for the most basic type of database in which all fields are of the Editable class.
It was noted in section 2.5.2 that the contents of a record field can be dragged by means of SELECT and dropped into another field, a writable icon in a dialogue box, an editor or wordprocessor, or a filer window. In the last case the data is saved as a text file named from the tag of the data field.
This drag-and-drop method can be extended considerably. If a number of fields are selected, as in a print selection, then dragging and dropping as above will transfer the whole selection to an editor, wordprocessor or to the filer (where it is saved as a file called Selected). You can't transfer multiple fields between databases in this way though, and dragging such a selection to a writable icon transfers the first field in the selection only. When dragging a selection like this the pointer can either be over a field (not necessarily a selected one) or over the window background; it doesn't matter.
An even more powerful extension is the ability to export all the exportable fields in a record by holding down Ctrl and dragging from anywhere in the record window. If you have two identical databases open this method can copy a record from one to the other as well as to editors, wordprocessors and the filer.
The main menu has an entry Export selected which displays an ordinary Save box. This is an old feature of Powerbase, now superseded by the drag-and-drop facilities described above apart from the fact that the older method lets you choose your own filename for saved data.