This shows you the differences between two versions of the page.
| — |
integration:rawdata:fixed:datatypes [2016/06/28 22:38] (current) |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| + | ~~NOTOC~~ | ||
| + | {{page>:top_add&nofooter&noeditbtn}} | ||
| + | |||
| + | ====== Integration: Raw Data Files ====== | ||
| + | |||
| + | ===== Fixed-length Files ===== | ||
| + | |||
| + | [[integration:rawdata:fixed:home|Overview]] | | ||
| + | [[integration:rawdata:fixed:environments|Environments]] | | ||
| + | **[[integration:rawdata:fixed:datatypes|Datatypes]]** | | ||
| + | [[integration:rawdata:fixed:export|Exporting]] | | ||
| + | [[integration:rawdata:fixed:limitations|Limitations]] | | ||
| + | [[integration:rawdata:fixed:example|Example]] | ||
| + | |||
| + | ---- | ||
| + | |||
| + | ==== Datatypes ==== | ||
| + | |||
| + | The record layout described in the CREATE TABLE statement must exactly match the record layout in the data file, including the order, datatype and length of each column. If these record layouts do not match, Omnidex will have problems reading the data from the data file. | ||
| + | |||
| + | <code> | ||
| + | create table "STATES" | ||
| + | physical "dat/states.dat" | ||
| + | ( | ||
| + | "STATE" CHARACTER(2), | ||
| + | "DESCRIPTION" STRING(31), | ||
| + | "STATE_CODE" CHARACTER(2), | ||
| + | "REGION" CHARACTER(2), | ||
| + | "COUNTRY" CHARACTER(2), | ||
| + | "TAX_RATE" FLOAT | ||
| + | ) | ||
| + | in "simple.xml"; | ||
| + | </code> | ||
| + | |||
| + | Most raw data files are sent from their supplier with some kind of record layout that can be used to construct the CREATE TABLE statement. For those that do not, the [[programs:odxsql:commands:dump_file|DUMP FILE]] command in OdxSQL can be helpful. This command will dump the contents of a file in both hex and character form so that the alignment of each column can be seen. | ||
| + | |||
| + | <code> | ||
| + | > dump file dat/states.dat | ||
| + | |||
| + | File dump for dat/states.dat | ||
| + | |||
| + | Record length [256]: 44 | ||
| + | |||
| + | dat/states.dat is 3.3K and has 76 rows | ||
| + | |||
| + | Rec #, Previous, Next, First, Last, or Quit: | ||
| + | Filename: dat/sta.dat Record: 1 Offset: 0 | ||
| + | 00000-00015: 41414172 6d656420 466f7263 65732041 AAArmed Forces A | ||
| + | 00016-00031: 6d657269 63617300 00000000 00000000 mericas......... | ||
| + | 00032-00043: 00002020 41465553 00000000 .. AFUS.... | ||
| + | Rec #, Previous, Next, First, Last, or Quit: | ||
| + | Filename: dat/sta.dat Record: 2 Offset: 44 | ||
| + | 00000-00015: 41454172 6d656420 466f7263 65732045 AEArmed Forces E | ||
| + | 00016-00031: 75726f70 652f4166 72696361 00000000 urope/Africa.... | ||
| + | 00032-00043: 00002020 41465553 00000000 .. AFUS.... | ||
| + | Rec #, Previous, Next, First, Last, or Quit: | ||
| + | Filename: dat/sta.dat Record: 3 Offset: 88 | ||
| + | 00000-00015: 41504172 6d656420 466f7263 65732050 APArmed Forces P | ||
| + | 00016-00031: 61636966 69630000 00000000 00000000 acific.......... | ||
| + | 00032-00043: 00002020 41465553 00000000 .. AFUS.... | ||
| + | </code> | ||
| + | |||
| + | Omnidex cannot verify that the record layouts exactly match, so it is always wise to retrieve a few rows and visually inspect that the column definition and the data file line up properly. | ||
| + | |||
| + | |||
| + | ===== ===== | ||
| + | |||
| + | **[[integration:rawdata:fixed:environments|Prev]]** | | ||
| + | **[[integration:rawdata:fixed:export|Next]]** | ||
| + | |||
| + | ====== Additional Resources ====== | ||
| + | |||
| + | See also: | ||
| + | |||
| + | {{page>:integration:rawdata:see_also&nofooter&noeditbtn}} | ||
| + | |||
| + | {{page>:bottom_add&nofooter&noeditbtn}} | ||