Original and QVS Formats

*Pro Version Only*

‘CatalogOF’ format tables

You will find that some of the tables in the SQL database are called, for example, ChileCatalogOF or GreeceCatalogOF.

Any table name that ends in CatalogOF is a table that has been specifically designed to hold the catalog data of the source in its original format. [OF]

Generally it is expected that the data will have been provided in CSV form. If these are loaded into MS Excel by double-clicking the CSV file the resulting Excel page can be copied and pasted directly into the SQL table. You do not have to use Excel but the commas have to be changed to tabs to enable loading into SQL.

Please be aware that loading data in this manner can be very long winded! 10,000 lines can take quite some time to load so small chunks is a good way to go if you do not want the computer tied up doing the paste.

There are some routines in the QVS Data program that can write this data to the database for you, and more are being added. You will find these in the Data Centre.

You can then or at a later stage choose to copy this data into the main database tables where the program will access them in a common format. This is done from the QVS tables.

‘QVS’ format tables

Any table with QVS at the end of the table name is a common format table. This is the only format available in the standard program. A QVS table can be emptied and refilled at any time and does not have to contain all the data that might be available in the OF tables. For example you might want to hold only 1 year of data in the working QVS format tables.

Generally the data service in the program looks at 7 day files, or sets of data for 7 days with only one notable exception which is Iceland which is 2 days. As data is loaded into the QVS format tables duplicates are ignored, revisions are stored and the previous record archived, and deletions are archived. There is one single revisions table and one single deletions table for all the QVS format tables.

See the data dictionary entry for the QVS format tables for details. Any field that is omitted in original data is extrapolated or defaulted in the QVS format table.

Many catalogues use separate columns for year, month, day, hour, minute and second. QVS tables always use a single date/time format YYYY-MM-DDTHH:MM:SS.SSSZ

As an example say the New Zealand original format table contains this date/time information:

Year Month Day Hr Min Sec
1991 12 31 18 14 35.06118

This would be represented in the QVS Format table as 1991-12-31T18:14:35.061Z