SMImport suite: FAQ

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

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

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

Answer:
Yes, you can change it using the Statistic.UpdateStep property:

yourImportComp.Statistc.UpdateStep := 100

go top

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

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

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

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

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

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

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

Answer:
Do you want to show/hide some formats in wizard? If yes, then:

1. to exclude Paradox and DBase from list of formats which will see the end-user:
   SMIWizardDlg1.Formats := SMIWizardDlg1.Formats - [teParadox, teDBase]
2. to include Excel into list of formats:
   SMIWizardDlg1.Formats := SMIWizardDlg1.Formats + [teXLS]

go top

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

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

Answer:
You can use a few different methods:

1. just skip such field from Mappings property of import component and define any desired value in OnNewRecord event of your TDataset.
2. 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

Answer:
Idea is a simple:

1. drop a TSMImportFromAccess component on form
2. 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
3. define SourceFileName property - this is a full file name to your mdb-file with MS Access database
4. define TableName property - this is a table inside your mdb-file which you want to load into Dataset
5. 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
   ...
6. 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

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

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

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

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-2024, Scalabium Software. All rights reserved.
webmaster@scalabium.com