Integration: Raw Data Files

Delimited Files

Example

The following example is a simple database using delimited files. Each table uses a different combination of column and record delimiters. The Omnidex Environment File is displayed below; however, the entire example, including sample data files, can be downloaded as a ZIP file at the following link: Download ZIP File.

The following statements will create and build an Omnidex Environment containing delimited files:

> use sql/create_all.sql
| use sql/simple.sql
-> create environment
>>  in                   "simple.xml"
>>  with                 delete;
Environment created in simple.xml
-> ;
-> ;
-> create database       "SIMPLE"
>>   type                FILE
>>   index_directory     "idx"
>>  in                   "simple.xml";
Database SIMPLE created in simple.xml
-> ;
-> ;
-> create table          "COUNTRIES"
>>  options              "DELIMITED COLUMN=',' RECORD='\r\n' QUOTES"
>>  physical             "dat/countries.csv"
>>  (
>>   "COUNTRY"           CHARACTER(2)      omnidex,
>>   "DESCRIPTION"       STRING(47)        quicktext,
>>   "LATITUDE"          FLOAT             omnidex      usage "LATITUDE",
>>   "LONGITUDE"         FLOAT             omnidex      usage "LONGITUDE",
>>   "CAPITAL"           STRING(31)        quicktext,
>>   "CAPITAL_LAT"       FLOAT             omnidex      usage "LATITUDE",
>>   "CAPITAL_LONG"      FLOAT             omnidex      usage "LONGITUDE",
>>   constraint COUNTRIES_COUNTRY_PK primary ("COUNTRY")
>>  )
>>  in                   "simple.xml";
Table COUNTRIES created in simple.xml
-> ;
-> ;
-> create table          "STATES"
>>  options              "DELIMITED COLUMN='\t' RECORD='\r\n' QUOTES"
>>  physical             "dat/states.txt"
>>  (
>>   "STATE"             CHARACTER(2)      omnidex,
>>   "DESCRIPTION"       STRING(31)        quicktext,
>>   "STATE_CODE"        CHARACTER(2)      omnidex,
>>   "REGION"            CHARACTER(2)      omnidex,
>>   "COUNTRY"           CHARACTER(2)      omnidex,
>>   "TAX_RATE"          FLOAT             omnidex,
>>   constraint STATES_STATE_PK primary ("STATE"),
>>   constraint STATES_COUNTRY_FK foreign ("COUNTRY") references "COUNTRIES"
>>  )
>>  in                   "simple.xml";
Table STATES created in simple.xml
-> ;
-> ;
-> create table          "GENDERS"
>>  options              "DELIMITED COLUMN='|' RECORD='\r\n' QUOTES"
>>  physical             "dat/genders.txt"
>>  (
>>   "GENDER"            CHARACTER(1)      omnidex,
>>   "DESCRIPTION"       STRING(31)        quicktext,
>>   constraint GENDERS_GENDER_PK primary ("GENDER")
>>  )
>>  in                   "simple.xml";
Table GENDERS created in simple.xml
-> ;
-> ;
-> create table          "HOUSEHOLDS"
>>  options              "DELIMITED COLUMN='##' RECORD='\n' QUOTES"
>>  physical             "dat/households.txt"
>>  (
>>   "HOUSEHOLD"         CHARACTER(12)     omnidex,
>>   "ADDRESS"           CHARACTER(50)     quicktext,
>>   "CITY"              CHARACTER(28)     quicktext,
>>   "STATE"             CHARACTER(2)      omnidex,
>>   "ZIP"               CHARACTER(5)      omnidex,
>>   "COUNTRY"           CHARACTER(2)      omnidex,
>>   constraint HOUSEHOLDS_HOUSEHOLD_PK primary ("HOUSEHOLD"),
>>   constraint HOUSEHOLDS_STATE_FK foreign ("STATE") references "STATES",
>>   constraint HOUSEHOLDS_COUNTRY_FK foreign ("COUNTRY") references "COUNTRIES"

>>  )
>>  in                   "simple.xml";
Table HOUSEHOLDS created in simple.xml
-> ;
-> ;
-> create table          "INDIVIDUALS"
>>  options              "DELIMITED COLUMN='%%' RECORD='~~' QUOTES"
>>  physical             "dat/individuals.txt"
>>  (
>>   "INDIVIDUAL"        CHARACTER(12)     omnidex,
>>   "HOUSEHOLD"         CHARACTER(12)     omnidex,
>>   "NAME"              CHARACTER(50)     quicktext,
>>   "GENDER"            CHARACTER(1)      omnidex bitmap,
>>   "BIRTHDATE"         ANSI DATE         omnidex,
>>   "PHONE"             CHARACTER(14)     omnidex,
>>   "EMAIL"             CHARACTER(60)     quicktext,
>>   constraint INDIVIDUALS_INDIVIDUAL_PK primary ("INDIVIDUAL"),
>>   constraint INDIVIDUALS_HOUSEHOLD_FK foreign ("HOUSEHOLD") references "HOUSEHOLDS",
>>   constraint INDIVIDUALS_GENDER_FK foreign ("GENDER") references "GENDERS",
>>  )
>>  in                   "simple.xml";
Table INDIVIDUALS created in simple.xml
-> ;
-> connect simple.xml
Connected to D:\class\lab2a\simple.xml
->
-> ; Update the Omnidex indexes
-> update indexes;
Updated indexes for COUNTRIES (239 rows, 0.031 cpu, 0.390 elapsed)
Updated indexes for STATES (76 rows, 0.016 cpu, 0.031 elapsed)
Updated indexes for GENDERS (2 rows, 0.000 cpu, 0.031 elapsed)
Updated indexes for HOUSEHOLDS (1,909 rows, 0.016 cpu, 0.094 elapsed)
Updated indexes for INDIVIDUALS (5,000 rows, 0.031 cpu, 0.390 elapsed)
Omnidex indexes updated for environment USER_ENVIRONMENT
-> ;
-> ; Update statistics to improve performance optimization;
-> update statistics;
Updated statistics for COUNTRIES (239 rows, 0.063 cpu, 0.063 elapsed)
Updated statistics for STATES (76 rows, 0.047 cpu, 0.047 elapsed)
Updated statistics for GENDERS (2 rows, 0.031 cpu, 0.031 elapsed)
Updated statistics for HOUSEHOLDS (1,909 rows, 0.094 cpu, 0.094 elapsed)
Updated statistics for INDIVIDUALS (5,000 rows, 0.125 cpu, 0.125 elapsed)
Statistics updated for environment USER_ENVIRONMENT
-> ;
-> ; Update the text database to enable PowerSearch;
-> update text;
Updated text for COUNTRIES (0.016 cpu, 0.015 elapsed)
Updated text for STATES (0.000 cpu, 0.000 elapsed)
Updated text for GENDERS (0.000 cpu, 0.000 elapsed)
Updated text for HOUSEHOLDS (0.063 cpu, 0.062 elapsed)
Updated text for INDIVIDUALS (0.063 cpu, 0.078 elapsed)
Text metadata updated for environment USER_ENVIRONMENT
-> ;
-> disconnect
Disconnected

The ZIP file also contains the file sql/export.sql. This file gives examples of how to create delimited files from another Omnidex environment using EXPORT statements:

connect to    simple.xml

export        (select        *
                 from        COUNTRIES)
  to          dat/countries.csv
  with        DELIMITED COLUMN=',' RECORD=\r\n QUOTES DELETE;

export        (select        *
                 from        STATES)
  to          dat/states.txt
  with        DELIMITED COLUMN=\t RECORD=\r\n QUOTES DELETE;

export        (select        *
                 from        GENDERS)
  to          dat/genders.txt
  with        DELIMITED COLUMN=| RECORD=\r\n QUOTES DELETE;

export        (select        *
                 from        HOUSEHOLDS)
  to          dat/households.txt
  with        DELIMITED COLUMN=## RECORD=\n QUOTES DELETE;

export        (select        *
                 from        INDIVIDUALS)
  to          dat/individuals.txt
  with        DELIMITED COLUMN=%% RECORD=~~ QUOTES DELETE;

disconnect

Additional Resources

See also:

 
Back to top
integration/rawdata/delimited/example.txt ยท Last modified: 2016/06/28 22:38 (external edit)
 
 
chimeric.de = chi`s home Valid CSS Driven by DokuWiki do yourself a favour and use a real browser - get firefox!! Recent changes RSS feed Valid XHTML 1.0