|
<< Click to Display Table of Contents >> Importing delimited text |
  
|
|
<< Click to Display Table of Contents >> Importing delimited text |
|
A hallmark for any qualifying database application is easy and efficient intercommunication with other data sources via some interchangeable data format.
Concerning import it is important being able to map all the data fields carried via uniform text fields to the relevant data formats when imported into a proper database.
The "delimited" term refers to the use of a delimiter character separating the fields in each line in the text file.
This is done in the PdxEditor via the Delimited Text import window.
Besides being done by manual selection for each field, this may also be accurately guided by importing an associated profile or schema file for the specified text data file.
The schema file should have the columns:
"DataType";"Size";"FieldName"
or
DataType;Size;FieldName
although column headings may be omitted.
The schema files are to be named equivalent to the data files except bearing the *.sch file extension.
Schema files with the extension filename_profile.csv will also be accepted by manual selection via the Load Profile dialog.
See also Schema file.
Paradox and dBASE 7 tables do not accept inserting values into Autoincrement fields.
As PdxEditor, when importing from delimited text, uses a method inserting data record by record, Autoinc fields and it's data when imported are converted into integer (long) type data*.
The mapping data types are according to the SQL compliant native data types which will eventual map to the data types available in the selected active table format and table version.
If for instance importing data of the data type 16-bit "Short" (Short Integer/Small Integer), this will map to the 32-bit "Long" integer if importing into dBASE 7, as dBASE 7 does not provide the Short integer data type.
Likewise "Short" will map to the universal "Numeric" data type if importing into a dBASE 5 format, as dBASE 5 does not provide any integer specific numeric data type.
For Paradox importing a "Long" integer data type into a Paradox 4 data table will map to the more capable "Number" floating field type, as "Long Integer" was not available until the Paradox version 5 format, and the "Short" integer available in Paradox 4 tables may not give sufficient space for the imported Long integer data.
In the Delimited Text import window the mapping types are SMALLINT and INTEGER for the Short and Long integer data respectively and only selectable if available in the currently active destination table format.
* Note: If mandatory to reapply the Autoinc type on imported data, you may create a copy of the structure of the imported table, but with the desired field as Autoinc, and then transferring data to this structural copy using a
INSERT INTO TargetTable SELECT * FROM SourceTable;
as this will allow inserting data into an Autoinc'ed table, the Autoinc field and values however being re-initialized.
To preserve the unique data from an imported Autoinc field, you should rather set the indexed field as an PRIMARY KEY field preserving it as an INTEGER field.
The expected text qualifier in text import source files is the double quote character (").
This has to surround all source fields containing spaces or punctuations if equal to the delimiter character as well as if the field per se contains quotations marks. If the field contains quotations marks equal to the text qualifier these quote marks must each be doubled in the source fields - e.g.:
PreviousField,"The ""Hamlet"" is written by Shakespeare",NexField,"A,B,C",etc.