Import Data Type Issues

<< Click to Display Table of Contents >>

Navigation:  Menus > Database > Import > Delimited Text Import >

Import Data Type Issues

Previous pageReturn to chapter overviewNext page

Evaluating text file source data

NULL values

All text file source fields are trimmed for leading and trailing spaces, and empty fields as well as fields containing only space characters (chr #32) will be considered empty and thus evaluated as NULL.

Decimal point character

The decimal point characters used in the imported data text files must comply with the current System settings if importing floating point values.
If the source file decimal pointer used does not comply, you may temporarily change your System setting concerning decimal notation to ensure correct import of these floating point values or of integer numerics using thousand separator notation interfering with proper import.
If you do not have the option to temporarily change the System settings, you may as necessary import numeric fields as character fields for later coping with this issue by re exporting as text and reimporting as numerics under the proper System conditions.

Date formats

All ISO formatted text file date and datetime values are correctly imported and converted to true date/datetime types.
If the source file uses a regional date/datetime formatting, the import will depend on the date-month-year to be in the same sequence as used in the current Windows System settings. If not, the import will depend on manual temporary change of the System setting to comply with the date-month-year sequence used by the source file. After import, the date/datetime display will at any time be correct and according to the current System setting.
If exporting using the ISO format, reimport will be correct irrespective of current System settings.

Logical / Boolean / True-False fields

AbsoluteSQL accepts various formats of the True/False values:
Any text value trimmed or untrimmed of True/TRUE/T as well as any non-zero integer value will import as True.
Any text value trimmed or untrimmed of False/FALSE/F as well as literal zero (0) values will import as False.
Any text value trimmed or untrimmed of Null/NULL as well as blank text field (;"";) will import as NULL.
For logical fields originating from MS Access exports - see Data Types.

String / Character field Hard Space characters

Though indistinguishable from common spaces the Hard Space character (#0160) may prevent standard field trimming and detection as apparent or intended empty / NULL value fields.
As a standard with AbsoluteSQL Hard Spaces are converted into normal space characters for all import source fields before trimming, except for string and character fields (String/FixedChar/WideString/FixedWideChar/Memo/WideMemo, etc.).
For string/character fields you may optionally choose preserving leading and trailing hard spaces - inside text hard spaces targeting string/character type import fields are always preserved. Fields with only spaces/hard spaces are always evaluated as empty fields.

AbsoluteSQL Application Manual - 13 March 2023 - Copyright © 2011 - 2023 Niels Knabe