Exportizer Users' Guide vlsoftware.net

Creating Schema for Text Table

Contents Index Previous Next

If you have problems with opening text or CSV file in Exportizer or its field structure is not as you expected, it probably means that the file does not have a schema describing its field structure or the schema was incorectly defined. In such cases, you should create or edit the schema. We recommend that each such a file has a schema. Schemas are located in separate files (schema files), therefore creating, deleting, or changing the schema will not change the corresponding text or CSV file (data file). If data file does not have a corresponding schema file, the schema is built in memory using defaults which may be not desirable in many cases.

Schema files are plain text files which must be placed in the folder, where data files are located. For BDE connections, schema file must have the same file name as the data file, but with .SCH extension. For ADO connections, the schemas for all data files from certain folder are always kept in one file file named Schema.ini.

The schemas can be created or edited in any text editor. Please read ADO or BDE documentation for details.

In Exportizer, when you try to open text table without the schema of field definitions, a prompt appears asking whether to create the schema. If you agree, a new window window will appear where you can define the schema in convenient visual mode and see how this will affect the table data view.

Notes

1. It is recommended that data files do not contain carriage returns inside field data, because the carriage returns are used to identify records in the text table.

2. For BDE connections, the data file must end with a carriage return.

For those who prefer to create or edit schemas manually in text mode, here are some examples:

Example 1. The fields in the data file are separated by vertical bar characters (|).

Contents of the data file (programs.txt):
1|Database Tour|www.databasetour.net|2291
2|Database Tour Pro|www.databasetour.net|2558
3|Exportizer|www.vlsoftware.net/exportizer|1182
4|Exportizer Pro|www.vlsoftware.net/exportizer-pro|1522
5|Icons from File|www.vlsoftware.net/icons-from-file|879
6|Rename Us|www.vlsoftware.net/rename-us|712
7|Rename Us Pro|www.vlsoftware.net/rename-us-pro|918
8|Free Renju|www.vlsoftware.net/free-renju|404
9|Logical Crossroads|www.vlsoftware.net/logical-crossroads|522
10|Hanoi Towers|www.vlsoftware.net/hanoi-towers|99
11|Reportizer|www.reportizer.net|2046
12|Reportizer Viewer|www.reportizer.net|822
How this file looks without schema (ADO connection):
Text table without schema
Contents of BDE schema file (programs.sch): Contents of ADO schema file (Schema.ini):
[programs]
CHARSET = ASCII
FILETYPE = VARYING
DELIMITER = |
Field1 = ID,NUMBER,18,0,0
Field2 = NAME,CHAR,30,0,18
Field3 = URL,CHAR,50,0,48
Field4 = SIZEKB,NUMBER,18,0,98
[programs.txt]
ColNameHeader=False
Format=Delimited(|)
CurrencyThousandSymbol=
CurrencyDecimalSymbol=.
DecimalSymbol=.
TextDelimiter=
MaxScanRows=25
CharacterSet=ANSI
Col1=ID Integer Width 18
Col2=NAME Char Width 30
Col3=URL Char Width 50
Col4=SIZEKB Integer Width 18
How this file looks with schema:
Text table with schema

Example 2. The fields in the data file are separated by commas. The field data which contain commas, are delimited by double quote marks.

Contents of the data file (rptfuncs.txt):
MIN(x),Returns the lowest value of all records in field x
MAX(x),Returns the highest value of all records in field x
SUM(x),Sums values of all records in field x
COUNT(x),Returns count of all non-empty values of all records in field x
AVERAGE(x),Returns the average value of all records in field x
"MINEX(x, y)","Returns the lowest value of all records in field x, where condition y is True or y > 0"
"MAXEX(x, y)","Returns the highest value of all records in field x, where condition y is True or y > 0"
How this file looks without schema (BDE connection):
Text table (comma separated) without schema
Contents of BDE schema file (rptfuncs.sch): Contents of ADO schema file (Schema.ini):
[rptfuncs]
CHARSET = ASCII
FILETYPE = VARYING
DELIMITER ="
SEPARATOR =,
Field1 = FUNCNAME,CHAR,25,0,0
Field2 = FUNCDESCR,CHAR,120,0,25
[rptfuncs.txt]
ColNameHeader=False
Format=Delimited(,)
FixedFormat=RaggedEdge
CurrencyThousandSymbol=
CurrencyDecimalSymbol=.
DecimalSymbol=.
MaxScanRows=25
CharacterSet=ANSI
Col1=FUNCNAME Char Width 25
Col2=FUNCDESCR Char Width 120
How this file looks with schema:
Text table (comma separated) with schema

Example 3. The fields in the data file have fixed width.

Contents of the data file (customer.txt):
1                   Sandra      Bush            Portland        United States   
2                   Eric        Miles           Edmonton        Canada          
3                   Berndt      Mann            Hamburg         Germany         
4                   Marek       Przybylsky      Krakow          Poland          
5                   John        Hladni          Bedford         United States   
6                   Bogdan      Vovchenko       Kyiv            Ukraine         
7                   Paul        Vogel           Hamburg         Germany   
How this file looks without schema (ADO connection):
Text table (fixed length fields) without schema
Contents of BDE schema file (customer.sch): Contents of ADO schema file (Schema.ini):
[customer]
CHARSET = ASCII
FILETYPE = FIXED
Field1 = ID,FLOAT,20,2,0
Field2 = FIRSTNAME,CHAR,12,0,20
Field3 = LASTNAME,CHAR,16,0,32
Field4 = CITY,CHAR,16,0,48
Field5 = COUNTRY,CHAR,16,0,64
[customer.txt]
ColNameHeader=False
Format=FixedLength
FixedFormat=RaggedEdge
MaxScanRows=25
CharacterSet=ANSI
Col1=ID Float Width 20
Col2=FIRSTNAME Char Width 12
Col3=LASTNAME Char Width 16
Col4=CITY Char Width 16
Col5=COUNTRY Char Width 16
How this file looks with schema:
Text table (fixed length fields) with schema