SMImport suite: FAQ
Q1 | How can I use translated resources (non-english)?
Q2 | I don't want to use a BDE. How can I disable it?
Q3 | Which third-party databases are supported by SMImport?
Q4 | By default after every loaded row the counter in animated status dialog is changed. How can I update it for every 100 rows?
Q5 | How can I speed the import?
Q6 | During data importing from MS Access database I have some warning dialogs inside Delphi/C++Builder IDE. What can I do?
Q7 | Can I load settings of specification file which is created by SMExport?
Q8 | Can I load some desired records only (using own criteria)?
Q9 | Can I abort an import process using custom criteria?
Q10 | Can I create own progressbar and update it during import process?
Q11 | How can I apply the updated version?
Q12 | How can I exclude a Paradox format for wizard dialog?
Q13 | User want to define the import settings but execute a long data loading tonight only. How can I do it?
Q14 | How can I modify a some value of external file before field changing?
Q15 | How can I define values for AutoInc field?
Q16 | How can I use TSMImportFromAccess component?
Q17 | How can I request update for new version?
Q18 | How can I modify parsed values before apply?
Q19 | How can I control the position for wizard dialog?
Q20 | How can I use field names with spaces?

Question: IHow can I use translated resources (non-english)? Answer: If your application is non-english (german or french, for example), just copy the ExCnst.pas unit from SOURCES\RESOURCES\* folder (just override unit with default english resources). In this folder you'll find a lot of sub-folders for existing translations (one sub-folder per one language). For example, GER contain german resources, ITA contains italian resources etc go top

Question: In own application I not use a BDE and don't want to deploy it. Can I disbale a BDE using for SMImport? If yes, then how? Answer: Yes, of course, you can disable a BDE using for SMImport. The SMImport allow to work with any database engine (BDE, ADO, DAO, IBX etc) and use a BDE for importing from Paradox/dBase only. So if you'll not use the TSMImportFromBDE component in own application or disable the Paradox and dBase formats from Formats property of TSMIWizardDlg component, your application will not require a BDE at all. go top

Question: Where can I find a list of databases which are compartible with SMImport suite? Answer: The SMImport suite allow to import a data into any rowset type which is inherited from standard TDataset type. For example, BDE: TTable, TQuery ADO: TADODataset, TADOTable, TADOQuery IBX: TIBTable, TIBQuery MIDAS: TClientDataset dbExpress DOA Advantage Apollo DBISAM Halcyon Flash Filer from TurboPower Diamond Access, Diamond dbExpress RxMemoryDataset from RxLib mODBC kaDAO and kaADO and others If you have some extended type of third-party database engine in which want to import a data, contact us. go top

Question: By default after every loaded row the counter in animated status dialog is changed. How can I update it for every 100 rows? Answer: Yes, you can change it using the Statistic.UpdateStep property: yourImportComp.Statistc.UpdateStep := 100 go top

Question: Any recommendations for fast data loading... Answer: I can suggest the next: 1. don't use a CachedUpdates = True the BDE will dublicate a log od changes on local disk and only after import finish this log will be "applied" to real database. So better to set CachedUpdates = false or call Dataset->ApplyUpdates() method after each 100-200 records 2. don't use animated status dialog (or at least to set Statistic->UpdateStep = 100 or 200) The repainting of current status and progressbar take a lot of time and better to update this status by batch instead each record (or disable this dialog at all) 3. the text file in "fixed width" format is faster than CSV because the positions for each field in each row are defined. But for CSV files for each row of text file the parser will "detect" these positions for each field 4. don't run an application within Delphi/C++Builder IDE (especially if you have a few breakpoints for debug) 5. disable animated status dialog (see AnimatedStatus property) or at least increase a number of rows in batch for repainting (see Statistic.UpdateStep property, by default 1 - to update after every loaded row) 6. to disable all events of updated TDataset (OnAfterPost/OnBeforePost/OnNewRecord etc). Also if you have a master/detail relations, disable this link before import starting... If you have some events on TFields of your TDataset, disable these events (OnValidate/OnSetText/OnGetText) too 7. the settings of BDE alias are very important too 8. better to use a direct database engine instead any universal engine (BDE, ADO, ODBC etc) 9. if you use a server database then you can: disable indexes disable constraints disable triggers disable any other checks for data validation flush a buffer by batch in 100-200 records (just commit a transaction) try to load a data in exclusive mode when any user'll not have an access to same data (no read, no write) I sure that these suggestions will increase a speed of data loading. You may download the demo project where I shown how to load large text file with weblog into Paradox table. On my computer 42 000+ rows from UNIX text file (CSV) are loaded in 28 seconds. The full time for text parsing and row processing included in this result time. This mean that 80-85 000 rows will be loaded per minute. Note that text file with fixed widths will be loaded even faster. go top

Question: During data importing from MS Access database I have some warning dialogs inside Delphi/C++Builder IDE. What can I do? Answer: I sure that problem in the next: I used try-except statement for activation of current instance of DAO (if exist) and if the instance is not available I'll create a new (in except clause). If you ran a project from Delphi IDE, you received a warning. But if you'll run a same project from Explorer (not from Delphi IDE), application will work without any warnings. PS: as alternative you can uncheck the "Stop on exception" option (or "Debug on exception" - it depends from Delphi version) in the Debugger Options item of Tools menu. You'll find this option on Language Exceptions page. go top

Question: Can I load settings from specification file which is created by SMExport suite? Answer: Yes, of course. Any export and import component have the SaveSpecification and LoadSpecification methods and specification file is compartible between these suites. The specification is the smi-file which contains any required information - exported format, delimiters/qualifiers/separators, field mapping etc Also don't forget that you can give a same functionality for your end-users by default - in wizard components (both in SMExport and in SMImport) your user can save/load the own specifications go top

Question: Can I load some desired records only (using own criteria)? Answer: Yes, of course, you can do it. To solve this task you can use the OnBeforeRecord event where you can accept or not any parsed row from external file. For example, don't accept rows which contain empty CustomerID column in text file:
    begin
      for i := VarArrayLowBound(Values, 1) to VarArrayHighBound(Values, 1) do
        if (Values[i][1] = 'CustomerID') then
        begin
          Accept := not VarIsNull(Values[i][2]) and not VarIsEmpty(Values[i][2])
        end
        else
    ...
    end;
go top

Question: Can I abort an import process using custom criteria? Answer: Yes, of course, you can do it. To solve this task you can use the OnAfterRecord event where you can change Abort parameter in True for import process canceling. Sample below shown how to cancel an importing when more than 10 errors are coccured during data loading:
    procedure TForm1.SMImportFromText1AfterRecordEvent(Sender: TObject; var Abort: Boolean);
    begin
      Abort := (SMImportFromText1.Statistic.TotalErrors > 10)
    end;
go top

Question: Can I create own progressbar for import process? Answer: Yes, of course, you can create an alternative progressbar instead default animated status dialog. For this task you may in OnAfterRecordEvent to check a Statistic property. This property contain any information which you need - number of loaded records, number of errors, number of added/updated/deleted records etc Also there you'll find the LastAction sub-property (is a last record is added or updated or deleted) and Result which you can to check for errors or cancel process (by user). go top

Question: How can I apply the updated version? Answer: You must apply it in two steps: 1. uninstall a previous version run a Delphi IDE select Close All from File menu select Install Packages item from Component menu select a package in listbox which you want to uninstall press Remove button 2. install a new version from sources select Open item from File menu change the FileType to package source (*.dpk/*.bpk) select the package file from folder where you unzipped files in the package window press the Install button after compile in the palette on SMImport page you can find the components IMPORTANT: you must check that folder name with previous version is removed from search path of Delphi/C++Builder IDE and your project. To check it you can in: Tools menu\Environment Options dialog\Library page\Library path Project menu\Options dialog\Directories and Conditionals page\Search path There you must have a folder with new version but not with previous. go top

Question: How can I exclude a Paradox type for wizard component? Answer: Do you want to show/hide some formats in wizard? If yes, then: to exclude Paradox and DBase from list of formats which will see the end-user: SMIWizardDlg1.Formats := SMIWizardDlg1.Formats - [teParadox, teDBase] to include Excel into list of formats: SMIWizardDlg1.Formats := SMIWizardDlg1.Formats + [teXLS] go top

Question: User want to define the import settings but execute a long data loading tonight only. How can I do it? Answer: Usually large sets of data must be loaded into database during the non-working hours (nights, week-ends etc). In this case you can define any settings for import process (field mapping, delimiter, qualifier etc) and save in the specification file (see a SaveSpecification method of any import component). When your application must execute a loading, you can load a saved specification (see a LoadSpecification method) and call Execute method: yourImportComp.LoadSpecification('c:\data\specs\ohio_packet.smi'); yourImportComp.Execute; Also in wizard component user can save and load specifications for future resuse. It's very useful because allow to save some predefined "templates" with more used settings which user can load and include some minor changes for succesful data loading. go top

Question: How can I modify a some value of external file before field changing? Answer: Every parsed value for any row of external file can be changed in OnGetCellParams event. There you'll receive any required information. Small sample:
    procedure TForm1.SMImportFromText(Sender: TObject; Field: TField; var Value: Variant);
    begin
      if Assigned(Field) and
         (Field.FieldName = 'CustomerID') then
      begin
        Value := 'OHIO_' + VarToStr(Value);
      end
    end;
go top

Question: How can I define values for AutoInc field? Answer: You can use a few different methods: just skip such field from Mappings property of import component and define any desired value in OnNewRecord event of your TDataset. as alternative, you can include AutoInc field into Mapping property of import component but assign a calculated ID (or read from SQL server) in OnGetCellParams event of import component go top

Question: How can I use TSMImportFromAccess component? Answer: Idea is a simple: drop a TSMImportFromAccess component on form define a Dataset property of import component - just select your TTable/TQuery (or another TDataset) which is a dataset in which you want to load a data from MS Access define SourceFileName property - this is a full file name to your mdb-file with MS Access database define TableName property - this is a table inside your mdb-file which you want to load into Dataset define a Mappings property - this is a correspondence between fields in your Dataset and fields in table of MS Access TableName. For example, CODE_DATASET=CODE_ACCESS NAME_DATASET=NAME_ACCESS NOTE_DATASET=NOTE_ACCESS ... set a Mode property. You can: - imAppend mode will append records from mdb-table - imAppendUpdate mode will update existing records and add non-exisitng records - imCopy mode will delete all current records from Dataset and will load records from mdb - imUpdate mode will refresh/update existing records only - imDelete mode will remove any records which are included in Dataset and mdb-file IMPORTANT: if you define a Mode as imAppendUpdate or imUpdate or imDelete, you must define a DatasetKeys property - list of unique fields in Dataset which are a key for search. For example, DatasetKeys = 'CUSTOMER;COUNTRY;DEPARTMENT' It's all - just call Execute method of import component and data will be loaded. go top

Question: How can I request the update for new version? Answer: For update you may send an email message to update@scalabium.com Please include in message body any registration information - order number or tracking id or password etc. Without this information you will not find your record in our database. When we'll recieve your message, we'll send you the confirmation that your request added in queue and will be processed soon. Most requests are processed during one-two business days and archive with registration version will be sent to you by email. Only delay is possible after new release when we recieve a lot of requests for update. IMPORTANT: if your mail server have some limiations (limited max message size or password-protected files are not allowed), please include a note in body of your request. In this case we'll provide you a direct link where you could download a file with registration version. Note that minor updates are free-of-charge. But all major releases are not free but available for discount. If you want to upgrade your licence for new major version, please contact sales@scalabium.com and we'll provide your a direct link for online order with discount. go top

Question: How can I modify parsed values before apply? Answer: To change parsed values you may in two different events: to change a value for some field you may in OnGetCellParams event to change values for couple of fields you may in OnBeforeRecordEvent For example, you want to remove all formatted chars for PRICE and PHONE fields and save pure digits:
    procedure TForm1.SMImportFromText1BeforeRecordEvent(Sender: TObject;
      const Fields: String; var Values: Variant; var Accept: Boolean);
    var
      i: Integer;
      s: string;
      v: Variant;
    begin
      for i:= VarArrayLowBound(Values, 1) to VarArrayHighBound(Values, 1) do
      begin
        s := UpperCase(Values[i][0]);
        if (s = 'PRICE') then
        begin
          v := Values[i];
          s := VarToStr(v[2]);
          s := StringReplace(s, ',', '', [rfReplaceAll]);
          v[2] := StrToInt(s);
          Values[i] := v;
        end
        else
        if (s = 'PHONE') then
        begin
          v := Values[i];
          s := VarToStr(v[2]);
          s := StringReplace(s, '-', '', [rfReplaceAll]);
          v[2] := StrToInt(s);
          Values[i] := v;
        end
      end;
    end;
The similar task could be solved in OnGetCellParams event. For example, to parse a string to datetime value:
    procedure TfrmMain.SMIWizardDlgGetCellParams(Sender: TObject;
      Field: TField; var Value: Variant);
    begin
      if Assigned(Field) and
         (Field.DataType in [ftDate, ftDateTime]) then
      begin
        {parse a date string DDMMYYYY}
        Value := Copy(Value, 1, 2) + DateSeparator +
                 Copy(Value, 3, 2) + DateSeparator +
                 Copy(Value, 5, 4);
      end;
    end;
Note that most tasks could be solved without any programming - you may use extended expressions in Mappings and there transform data (for example, to convert a string to date for custom format). But, of course, exists some custom tasks that require additional programming for full control via events as described above. go top

Question: How can I control the position for wizard dialog? Answer: By default wizard will be displayed in center of screen. But you may use OnShow event for wizard component and change the position as you like. For example:
    procedure TForm1.SMIWizardDlg1Show(Sender: TObject);
    begin
      with (Sender as TForm) do
      begin
        Position := poDesigned;
        Left := 5;
        Top := 5
      end;
    end;
Note that in this event you may also change another properties too - for example, to change the BorderStyle property. go top

Question: How can I use field names with spaces? Answer: If some field name in dataset contain the space character, you must quote the field name in mappings. For example:
    yourSMImport.Mappings.Add('ProcessDate="Date of Process"')
As alternative, you may use brackets instead quotes
    yourSMImport.Mappings.Add('ProcessDate=[Date of Process]')
Also note that same rule is correct if your field name have a same name as any pre-defined function in parser. For example:
    {load current date in field}
    yourSMImport.Mappings.Add('ProcessDate="Date"')
go top

|  | Copyright© 1998-2005, Scalabium. All rights reserved. webmaster@scalabium.com | January 10, 2005