qimport3common.pas

Advanced Data Import Component Suite for Borland Delphi and C++ Builder allows you to import your da

unit QImport3Common;

{$I QImport3VerCtrl.Inc}

{$IFDEF VCL6} 
  {$WARN SYMBOL_PLATFORM OFF} 
{$ENDIF}

interface

uses
  {$IFDEF QI_UNICODE}
    {$IFDEF VCL10} WideStrings, {$ELSE} EmsWideStrings, {$ENDIF}
    EmsWideStringGrid,
  {$ENDIF}
  Classes, IniFiles, QImport3, DB, XLSMapParser3, Windows, Graphics
  {$IFNDEF NOGUI}, Grids, Controls, DBGrids, ComCtrls{$ENDIF}
  ,QImport3StrTypes;

const
  //---------------------
  //  Don't localize it !
  //---------------------
  QI_PALETTE_PAGE = 'EMS Advanced Import 3';
  QI_ABOUT = '(About EMS AdvancedImport 3)';
{$IFDEF CS}
  QI_REG_URL = 'http://www.componentsource.com';
{$ELSE}
  QI_REG_URL = 'http://www.sqlmanager.net/products/tools/advancedimport/buy';
{$ENDIF}

  QI_WIZARD_HELP = 'AImportWizard.hlp';

  QI_FULL_PRODUCT_NAME = 'Advanced Data Import for Delphi(R) and C++ Builder(R) (%s)';
  QI_COPYRIGHT = '(C) 1999-2009  EMS Database Management Solutions, Ltd.  All rights reserved.';
  QI_VERSION = '3.1.0.7';

  QIW_FIRST_STEP = 'FIRST_STEP';
    QIW_FILE_NAME = 'FileName';
    QIW_IMPORT_TYPE = 'ImportType';
    QIW_GO_TO_LAST_PAGE = 'GoToLastPage';
    QIW_AUTO_SAVE_TEMPLATE = 'AutoSaveTemplate';
  QIW_XLSX_MAP = 'XLSX_MAP';
  QIW_XLSX_OPTIONS = 'XLSX_OPTIONS';
    QIW_XLSX_SKIP_LINES = 'SkipLines';
    QIW_XLSX_SHEET_NAME = 'SheetName';
    QIW_XLSX_NEED_FILLMERGE = 'NeedFillMerge';
    QIW_XLSX_LOAD_HIDDENSHEET = 'LoadHiddenSheet';
  QIW_DOCX_MAP = 'DOCX_MAP';
    QIW_DOCX_SKIP_LINES = 'SkipLines';
    QIW_DOCX_TABLE_NUMBER = 'TableNumber';
  QIW_DOCX_OPTIONS = 'DOCX_OPTIONS';
  QIW_ODS_MAP = 'ODS_MAP';
  QIW_ODS_OPTIONS = 'ODS_OPTIONS';
    QIW_ODS_SKIP_LINES = 'SkipLines';
    QIW_ODS_SHEET_NAME = 'SheetName';
  QIW_ODT_MAP = 'ODT_MAP';
  QIW_ODT_OPTIONS = 'ODT_OPTIONS';
    QIW_ODT_SKIP_LINES = 'SkipLines';
    QIW_ODT_SHEET_NAME = 'SheetName';
    QIW_ODT_USE_HEADER = 'UseHeader';
  QIW_XLS_MAP = 'XLS_MAP';
  QIW_XLS_OPTIONS = 'XLS_OPTIONS';
    QIW_XLS_SKIP_COLS = 'SkipCols';
    QIW_XLS_SKIP_ROWS = 'SkipRows';
  QIW_TXT_MAP = 'TXT_MAP';
  QIW_TXT_OPTIONS = 'ASCII_OPTIONS';
    QIW_TXT_SKIP_LINES = 'SkipLines';
    QIW_TXT_ENCODING_OPTION = 'Encoding';
  QIW_CSV_MAP = 'CSV_MAP';
  QIW_CSV_OPTIONS = 'CSV_OPTIONS';
    QIW_CSV_DELIMITER = 'Delimiter';
    QIW_CSV_SKIP_LINES = 'SkipLines';
    QIW_CSV_QUOTE = 'Quote';
  QIW_DBF_MAP = 'DBF_MAP';
  QIW_DBF_OPTIONS = 'DBF_OPTIONS';
    QIW_DBF_SKIP_DELETED = 'SkipDeleted';
  QIW_HTML_MAP = 'HTML_MAP';
  QIW_HTML_OPTIONS = 'HTML_OPTIONS';
    QIW_HTML_SKIP_LINES = 'SkipLines';
  QIW_XML_MAP = 'XML_MAP';
  QIW_XML_OPTIONS = 'XML_OPTIONS';
    QIW_XML_WRITE_ON_FLY = 'WriteOnFly';
  QIW_MDB_MAP = 'ACCESS_MAP';
  QIW_MDB_OPTIONS = 'ACCESS_OPTIONS';
    QIW_MDB_PASSWORD = 'Password';
    QIW_MDB_SOURCETYPE = 'SourceType';
    QIW_MDB_TABLENAME = 'TableName';
  QIW_MDB_QUERY = 'ACCESS_SQL';
    QIW_MDB_SQL_LINE = 'Line_';
  QIW_BASE_FORMATS = 'BASE_FORMATS';
    QIW_BF_DECIMAL_SEPARATOR = 'DecimalSeparator';
    QIW_BF_THOUSAND_SEPARATOR = 'ThousandSeparator';
    QIW_BF_SHORT_DATE_FORMAT = 'ShortDateFormat';
    QIW_BF_LONG_DATE_FORMAT = 'LongDateFormat';
    QIW_BF_DATE_SEPARATOR = 'DateSeparator';
    QIW_BF_SHORT_TIME_FORMAT = 'ShortTimeFormat';
    QIW_BF_LONG_TIME_FORMAT = 'LongTimeFormat';
    QIW_BF_TIME_SEPARATOR = 'TimeSeparator';
    //QIW_BF_LEFT_QUOTATION = 'LeftQuotation';
    //QIW_BF_RIGHT_QUOTATION = 'RightQuotation';
    //QIW_BF_QUOTATION_ACTION = 'QuotationAction';
  QIW_BOOLEAN_TRUE = 'BOOLEAN_TRUE';
  QIW_BOOLEAN_FALSE = 'BOOLEAN_FALSE';
  QIW_NULL_VALUES = 'NULL_VALUES';
  QIW_DATA_FORMATS = 'DATA_FORMATS_';
    QIW_DF_GENERATOR_VALUE = 'GeneratorValue';
    QIW_DF_GENERATOR_STEP = 'GeneratorStep';
    QIW_DF_CONSTANT_VALUE = 'ConstantValue';
    QIW_DF_NULL_VALUE = 'NullValue';
    QIW_DF_DEFAULT_VALUE = 'DefaultValue';
    QIW_DF_LEFT_QUOTE = 'LeftQuote';
    QIW_DF_RIGHT_QUOTE = 'RightQuote';
    QIW_DF_QUOTE_ACTION = 'QuoteAction';
    QIW_DF_CHAR_CASE = 'CharCase';
    QIW_DF_CHAR_SET = 'CharSet';
  QIW_REPLACEMENTS = 'REPLACEMENTS_';
    QIW_RP_TEXT_TO_FIND = 'TextToFind';
    QIW_RP_REPLACE_WITH = 'ReplaceWith';
    QIW_RP_IGNORE_CASE = 'IgnoreCase';
  QIW_ITEM = 'ITEM_';
  QIW_LAST_STEP = 'LAST_STEP';
    QIW_COMMIT_AFTER_DONE = 'CommitAfterDone';
    QIW_COMMIT_REC_COUNT = 'CommitRecCount';
    QIW_IMPORT_REC_COUNT = 'ImportRecCount';
    QIW_CLOSE_AFTER_IMPORT = 'CloseAfterImport';
    QIW_ENABLE_ERROR_LOG = 'EnableErrorLog';
    QIW_ERROR_LOG_FILE_NAME = 'ErrorLogFileName';
    QIW_REWRITE_ERROR_LOG_FILE = 'RewriteErrorLogFile';
    QIW_SHOW_ERROR_LOG = 'ShowErrorLog';
    QIW_ENABLE_SQL_LOG = 'EnableSQLLog';
    QIW_SQL_LOG_FILE_NAME = 'SQLLogFileName';
    QIW_REWRITE_SQL_LOG_FILE = 'RewriteSQLLogFile';
    QIW_IMPORT_MODE = 'ImportMode';
    QIW_ADD_TYPE = 'AddType';
    QIW_KEY_COLUMNS = 'KeyColumns';


  QI_BASE = 'BASE';
    QI_FILE_NAME = 'FileName';
    QI_IMPORT_TYPE = 'ImportType';
    QI_IMPORT_DESTINATION = 'ImportDestination';
    QI_IMPORT_MODE = 'ImportMode';
    QI_MAP = 'MAP';
    QI_KEY_COLUMNS = 'KeyColumns';
    QI_GRID_CAPTION_ROW = 'GridCaptionRow';
    QI_GRID_START_ROW = 'GridStartRow';
    QI_COMMIT_AFTER_DONE = 'CommitAfterDone';
    QI_COMMIT_REC_COUNT = 'CommitRecCount';
    QI_IMPORT_REC_COUNT = 'ImportRecCount';
    QI_ENABLE_ERROR_LOG = 'EnableErrorLog';
    QI_ERROR_LOG_FILE_NAME = 'ErrorLogFileName';
    QI_REWRITE_ERROR_LOG_FILE = 'RewriteErrorLogFile';
    QI_SHOW_ERROR_LOG = 'ShowErrorLog';
    {QI_ENABLE_SQL_LOG = 'EnableSQLLog';
    QI_SQL_LOG_FILE_NAME = 'SQLLogFileName';
    QI_SQL_LOG_FILE_REWRITE = 'SQLLogFileRewrite';}
  BASE_FORMATS = 'BASE_FORMATS';
    BF_DECIMAL_SEPARATOR = 'DecimalSeparator';
    BF_THOUSAND_SEPARATOR = 'ThousandSeparator';
    BF_SHORT_DATE_FORMAT = 'ShortDateFormat';
    BF_LONG_DATE_FORMAT = 'LongDateFormat';
    BF_SHORT_TIME_FORMAT = 'ShortTimeFormat';
    BF_LONG_TIME_FORMAT = 'LongTimeFormat';
    //BF_LEFT_QUOTATION = 'LeftQuotation';
    //BF_RIGHT_QUOTATION = 'RightQuotation';
    BF_QUOTATION_ACTION = 'QuotationAction';
  BOOLEAN_TRUE = 'BOOLEAN_TRUE';
  BOOLEAN_FALSE = 'BOOLEAN_FALSE';
  NULL_VALUES = 'NULL_VALUES';
  DATA_FORMATS = 'DATA_FORMATS_';
    DF_GENERATOR_VALUE = 'GeneratorValue';
    DF_GENERATOR_STEP = 'GeneratorStep';
    DF_CONSTANT_VALUE = 'ConstantValue';
    DF_NULL_VALUE = 'NullValue';
    DF_DEFAULT_VALUE = 'DefaultValue';
    DF_LEFT_QUOTE = 'LeftQuote';
    DF_RIGHT_QUOTE = 'RightQuote';
    DF_QUOTE_ACTION = 'QuoteAction';
    DF_CHAR_CASE = 'CharCase';
    DF_CHAR_SET = 'CharSet';

  XLS_OPTIONS = 'XLS_OPTIONS';
    XLS_SKIP_COLS = 'SkipCols';
    XLS_SKIP_ROWS = 'SkipRows';
  ASCII_OPTIONS = 'ASCII_OPTIONS';
    ASCII_SKIP_LINES = 'SkipLines';
    ASCII_COMMA = 'Comma';
    ASCII_Quote = 'Quote';
    ASCII_ENCODING = 'Encoding';
  DBF_OPTIONS = 'DBF_OPTIONS';
    DBF_SKIP_LINES = 'SkipLines';
    DBF_CHARSET = 'CharSet';
  HTML_OPTIONS = 'HTML_OPTIONS';
    HTML_SKIP_LINES = 'SkipLines'; {!HTML}
  XML_OPTIONS = 'XML_OPTIONS';
    XML_SKIP_LINES = 'SkipLines';
    XML_WRITE_ON_FLY = 'WriteOnFly';
  DATA_SET_OPTIONS = 'DATA_SET_OPTIONS';
    DATA_SET_SKIP_LINES = 'SkipLines';
  ODS_OPTIONS = 'ODS_OPTIONS';
    ODS_SKIP_LINES = 'SkipRows';
    ODS_SHEET_NAME = 'SheetName';
    ODS_NOT_EXPAND_MERGED_VALUE = 'NotExpandMergedValue';
  ODT_OPTIONS = 'ODT_OPTIONS';
    ODT_SKIP_LINES = 'SkipRows';
    ODT_SHEET_NAME = 'SheetName';
    ODT_USE_HEADER = 'UseHeader';
    ODT_COMPLEX_TABLE = 'ComplexTable';
  XLSX_OPTIONS = 'XLSX_OPTIONS';
    XLSX_SKIP_LINES = 'SkipRows';
    XLSX_SHEET_NAME = 'SheetName';
    XLSX_LOAD_HIDDEN_SHEET = 'LoadHiddenSheet';
    XLSX_NEED_FILL_MERGE = 'NeedFillMerge';
  DOCX_OPTIONS = 'DOCX_OPTIONS';
    DOCX_SKIP_LINES = 'SkipRows';
    DOCX_TABLE_NUMBER = 'TableNumber';
    DOCX_NEED_FILLMERGE = 'NeedFillMerge';
  ACCESS_OPTIONS = 'ACCESS_OPTIONS';
    ACCESS_SKIP_LINES = 'SkipRows';
    ACCESS_TABLE_NAME = 'TableName';
    ACCESS_SQL = 'SQL';
    ACCESS_SQL_LINE = 'Line';
    ACCESS_SOURCE_TYPE = 'SourceType';
    ACCESS_PASSWORD = 'Password';

  XLS_EXT = '.xls';
  XLSX_EXT = '.xlsx';
  DOCX_EXT = '.docx';
  ODS_EXT = '.ods';
  ODT_EXT = '.odt';
  DBF_EXT = '.dbf';
  DB_EXT  = '.db';
  HTM_EXT = '.htm';
  HTML_EXT = '.html';
  XML_EXT = '.xml';
  TXT_EXT = '.txt';
  CSV_EXT = '.csv';
  MDB_EXT = '.mdb';

const
  PathDelim  = {$IFDEF WIN} '\'; {$ELSE} '/'; {$ENDIF}

  SShortMonthNameJan = 'Jan';
  SShortMonthNameFeb = 'Feb';
  SShortMonthNameMar = 'Mar';
  SShortMonthNameApr = 'Apr';
  SShortMonthNameMay = 'May';
  SShortMonthNameJun = 'Jun';
  SShortMonthNameJul = 'Jul';
  SShortMonthNameAug = 'Aug';
  SShortMonthNameSep = 'Sep';
  SShortMonthNameOct = 'Oct';
  SShortMonthNameNov = 'Nov';
  SShortMonthNameDec = 'Dec';

  SLongMonthNameJan = 'January';
  SLongMonthNameFeb = 'February';
  SLongMonthNameMar = 'March';
  SLongMonthNameApr = 'April';
  SLongMonthNameMay = 'May';
  SLongMonthNameJun = 'June';
  SLongMonthNameJul = 'July';
  SLongMonthNameAug = 'August';
  SLongMonthNameSep = 'September';
  SLongMonthNameOct = 'October';
  SLongMonthNameNov = 'November';
  SLongMonthNameDec = 'December';

  SShortDayNameSun = 'Sun';
  SShortDayNameMon = 'Mon';
  SShortDayNameTue = 'Tue';
  SShortDayNameWed = 'Wed';
  SShortDayNameThu = 'Thu';
  SShortDayNameFri = 'Fri';
  SShortDayNameSat = 'Sat';

  SLongDayNameSun = 'Sunday';
  SLongDayNameMon = 'Monday';
  SLongDayNameTue = 'Tuesday';
  SLongDayNameWed = 'Wednesday';
  SLongDayNameThu = 'Thursday';
  SLongDayNameFri = 'Friday';
  SLongDayNameSat = 'Saturday';

  SystemCTNames: array[TQICharsetType] of Integer =
    (
        -1, 1252, -1, 20127, 850, 852, 866, 1250, 1251, 1256, 1257, -1, -1, 28597,
      28598, -1, -1, 20866, 21866, 870, 1026, -1, 10029, 10000, 20107, CP_UTF8, 1200, 12000,
      // unique in postrgesql
      28593, -1, -1, -1, 28595, 28596,
      //unique in db2
      1026, 1254, 1255, 1258, 437, 500, 737, 855, 856, 857, 860, 862, 863, 864, 865, 869,
      874, 875, 871,
      //unique in IB/FB
      950, 949, -1, 936, -1, 28599, 28594, 1252, 1253, 775, 858
    );

  QICharsetTypeNames: array[TQICharsetType] of string =
    (
      'Windows default',
      'latin1 (ISO 8859-1 West European)',
      'armscii8 (ARMSCII-8 Armenian)',
      'ascii (US ASCII)',
      'cp850 (DOS West European)',
      'cp852 (DOS Central European)',
      'cp866 (DOS Russian)',
      'cp1250 (Windows Central European)',
      'cp1251 (Windows Cyrillic)',
      'cp1256 (Windows Arabic)',
      'cp1257 (Windows Baltic)',
      'dec8 (DEC West European)',
      'geostd8 (GEOSTD8 Georgian)',
      'greek (ISO 8859-7 Greek)',
      'hebrew (ISO 8859-8 Hebrew)',
      'hp8 (HP West European)',
      'keybcs2 (DOS Kamenicky Czech-Slova)',
      'koi8r (KOI8-R Relcom Russian)',
      'koi8u (KOI8-U Ukrainian)',
      'latin2 (ISO 8859-2 Central European',
      'latin5 (ISO 8859-9 Turkish)',
      'latin7 ((ISO 8859-13 Baltic)',
      'macce (Mac Central European)',
      'macroman (Mac West European)',
      'swe7 (7bit Swedish)',
      'Unicode (UTF-8)',
      'Unicode (UTF-16)',
      'Unicode (UTF-32)',
      'latin3 (ISO 8859-3)',
      'latin4 (ISO 8859-4)',
      'latin6 (ISO 8859-10)',
      'latin8 (ISO 8859-14)',
      'iso8859-5 (Cyrillic)',
      'iso8859-6 (Arabic)',

      'CCSID 1026 - EBCDIC Turkey Latin-5',
      'Codepage 1254 - Windows Turkish',
      'Codepage 1255 - Windows Hebrew',
      'Codepage 1258 - Windows Vietnamese',
      'Codepage 437 - US, Europe',
      'CCSID 500 - EBCDIC Latin-1',
      'Codepage 737 - WIN Greece',
      'Codepage 855 - OS2 Cyrillic',
      'Codepage 856 - Hebrew',
      'Codepage 857 - OS2 Turkey',
      'Codepage 860 - Portuguese',
      'Codepage 862 - OS2 Hebrew',
      'Codepage 863 - Canadian-French',
      'Codepage 864 - OS2 Arabic',
      'Codepage 865 - Norway, Denmark',
      'Codepage 869 - OS2 Greece',
      'Codepage 874 - OS2/AIX Thailand',
      'CCSID 875 - EBCDIC Greece',
      'CCSID 871 - EBCDIC Iceland',
      'Big5',
      'Korean (Unified Hangeul)',
      'EUC Japanese',
      'Simplified Chinese (Hong Kong, PRC)',
      'Japanese',
      'iso8859-9 (Turkish)',
      'iso8859-4 (Baltic)',
      'Codepage 1252 - Western Europe, Amerika',
      'Codepage 1253 - Modern Greek',
      'Codepage 775 - Baltic',
      'Codepage 858 - Latin-1'
    );

var
  DefShortMonthNames: array[1..12] of string = (SShortMonthNameJan,
    SShortMonthNameFeb, SShortMonthNameMar, SShortMonthNameApr,
    SShortMonthNameMay, SShortMonthNameJun, SShortMonthNameJul,
    SShortMonthNameAug, SShortMonthNameSep, SShortMonthNameOct,
    SShortMonthNameNov, SShortMonthNameDec);

  DefLongMonthNames: array[1..12] of string = (SLongMonthNameJan,
    SLongMonthNameFeb, SLongMonthNameMar, SLongMonthNameApr,
    SLongMonthNameMay, SLongMonthNameJun, SLongMonthNameJul,
    SLongMonthNameAug, SLongMonthNameSep, SLongMonthNameOct,
    SLongMonthNameNov, SLongMonthNameDec);

function StringToChar(const S: AnsiString; const Default: AnsiChar): AnsiChar;
function PadString(const S: AnsiString; Chr: AnsiChar; Len: integer): AnsiString;
function Str2Char(const Str: string; Default: Char): Char; overload;
{$IFDEF VCL12}
function Str2Char(const Str: string; Default: AnsiChar): AnsiChar; overload;
{$ENDIF}
function Char2Str(Chr: Char): string; overload;
{$IFDEF VCL12}
function Char2Str(Chr: AnsiChar): string; overload;
{$ENDIF}

{$IFDEF QI_UNICODE}
function QIAddQuote(const S, LeftQuote, RightQuote: WideString): WideString;
function QIRemoveQuote(const S, LeftQuote, RightQuote: WideString): WideString;
function QIUpperFirst(const S: WideString): WideString;
function QIUpperFirstWord(const S: WideString): WideString;
{$ELSE}
function QIAddQuote(const S, LeftQuote, RightQuote: AnsiString): AnsiString;
function QIRemoveQuote(const S, LeftQuote, RightQuote: AnsiString): AnsiString;
function QIUpperFirst(const S: AnsiString): AnsiString;
function QIUpperFirstWord(const S: AnsiString): AnsiString;
{$ENDIF}

{$IFDEF QI_UNICODE}
procedure CSVStringToStrings(
  const Str: WideString; Quote, Separator: AnsiChar; AStrings: TWideStrings);
{$ELSE}
procedure CSVStringToStrings(
  const Str: AnsiString; Quote, Separator: AnsiChar; AStrings: TStrings);
{$ENDIF}

procedure ReplaceTabs(var Str: qiString);

// StringGrid routines
{$IFNDEF NOGUI}
procedure GridDrawCell(Grid: TqiStringGrid;
  const SheetName: string; SheetNumber, ACol, ARow: integer; Rect: TRect;
  State: TGridDrawState; DefinedRanges: TMapRow; SkipCols, SkipRows: integer;
  IsEditing: boolean; Selection: TMapRow);
procedure GridMouseDown(Grid: TqiStringGrid;
  Button: TMouseButton; Shift: TShiftState; X, Y: Integer;  FirstCol, LastCol, FirstRow, LastRow,