Differences

This shows you the differences between two versions of the page.

--- integration:rawdata:delimited:example [2015/09/22 18:04]
doc
+++ integration:rawdata:delimited:example [2016/06/28 22:38]
@@ Line 1: / Line 1: @@
-~~NOTOC~~
-{{page>:top_add&nofooter&noeditbtn}}
-====== Integration: Raw Data Files ======
-===== Delimited Files =====
-[[integration:rawdata:delimited:home|Overview]] |
-[[integration:rawdata:delimited:environments|Environments]] |
-[[integration:rawdata:delimited:options|Options]] |
-[[integration:rawdata:delimited:datatypes|Datatypes]] |
-[[integration:rawdata:delimited:ofx|Offset Indexes]] |
-[[integration:rawdata:delimited:export|Exporting]] |
-[[integration:rawdata:delimited:limitations|Limitations]] |
-**[[integration:rawdata:delimited:example|Example]]**
-----
-==== 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:  **{{:integration:rawdata:delimited:delimited_example.zip|Download ZIP File}}**.
-The following statements will create and build an Omnidex Environment containing delimited files:
-<code>
-> 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
-</code>
-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:
-<code>
-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
-</code>
-=====  =====
-**[[integration:rawdata:fixed:limitations|Prev]]**
-====== Additional Resources ======
-See also:
-{{page>:integration:rawdata:see_also&nofooter&noeditbtn}}
-{{page>:bottom_add&nofooter&noeditbtn}}

Back to top

integration/rawdata/delimited/example.txt · Last modified: 2016/06/28 22:38 (external edit)

Sitemap Recent Changes