Exporting / Importing data files

Hi,

I am using Delphi 7 with MS-SQL 2000. I have an Employee table which saves employee's id, name, ..., and employee's picture in an IMAGE field.

The application is working fine, I am able to browse all employees and I can see their pictures using a DBIMAGE component.

I want to create an exported file that must contains all those employees data including their pictures. That file must be importable by my application for future use.

I used to create a text file which contains SQL commands like this:

INSERT INTO employee VALUES (.........)
INSERT INTO employee VALUES (.........)
INSERT INTO employee VALUES (.........)

Then, I just executes each and every line in the import process.

This will not work now because of the picture field.

any suggestion?
Hamid
HamidHossainAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Wim ten BrinkSelf-employed developerCommented:
Do you use ADO? Then you can save and load the ADO recordset to a file. Either as an XML file or in some binary format. When you want to import them again, all you do is create a new recordset, load the file in it, then move record by record back in the database.
If you use the BDE, you could use the TClientDataset in a similar way, but it's less native.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HamidHossainAuthor Commented:
The following worked for me. Thanks Alex since you remind me that I can use SaveToFile

===================================

procedure TfrmExpImp.ButtonClick(Sender: TObject);
var
  I, X: Integer;
  FieldName: String;
  bs: TBlobStream;
begin
  // load the file which contains imported data into ADO
  if OpenDialog1.Execute then begin
    if Length(OpenDialog1.FileName) > 0 then begin
      ADOImport.LoadFromFile(OpenDialog1.FileName);
    end;
  end;

  try
    ADOImport.Open;
    ProgressBar1.Max := ADOImport.RecordCount;
    ADOStudent.TableName := 'student';
    ADOStudent.Open;

    // loop over all records found in the loaded file
    for I := 0 to ADOImport.RecordCount-1 do begin
      Application.ProcessMessages;
     
      ADOStudent.Filtered := False;
      ADOStudent.Filter := 'std_id=' + ADOImport.FieldByName('std_id').AsString;
      ADOStudent.Filtered := True;

      // if record id already exist in our database then use edit. Else use insert and store it's ID
      if ADOStudent.RecordCount = 1 then begin
        ADOStudent.Edit;
      end else begin
        ADOStudent.Insert;
        // this must be with insert only, you will get an error if you use it above with edit
        ADOStudent.FieldByName('std_id').AsString := ADOImport.FieldByName('std_id').AsString;
      end;

      // loop over all fields to transport their values
      for X := 0 to ADOStudent.Fields.Count-1 do begin
        FieldName := ADOStudent.Fields[X].FieldName; // get field name

        // don't stop for std_id since it is already set for insert and it must not be used for edit.
        if FieldName <> 'std_id' then begin
          // do special process if field is std_datastrip
          if FieldName = 'std_datastrip' then begin
            try
              bs := TBlobStream(ADOImport.CreateBlobStream(ADOImport.FieldByName(FieldName), bmRead));
              bs.Position := 0;
              TBlobField(ADOStudent.FieldByName(FieldName)).LoadFromStream(bs);
            finally
              FreeAndNil(bs);
            end;
          end else begin
            // for normal fields, use the normal assignment
            ADOStudent.FieldByName(FieldName).AsString := ADOImport.FieldByName(FieldName).AsString;
          end;
        end;
      end;

      ADOStudent.Post; // post the record into the SQL table

      ADOImport.Next;
      ProgressBar1.Position := ProgressBar1.Position + 1;
    end;

    ProgressBar1.Position := 0;
  finally
    ADOStudent.Close;
    ADOImport.Close;
  end;
end;
0
HamidHossainAuthor Commented:
Now, As my code above shows:

I have ADOImport which will contain the loaded file, and ADOStudent which contains the student table at my MS-SQL.

Usually, I will import a file contains about 400 - 600 students with their pictures. This will take very very long time.

I think the way I used inside the loop is not the best way (I used filter property)

Is there any faster way to import all records and make the loop faster? keep in mind that I am inserting new records and updating already existing ones.

Regards,
Hamid
0
Wim ten BrinkSelf-employed developerCommented:
One trick you could do is to import the data inside a separate thread. That way you don't have to call Application.ProcessMessages all the time. The main thread will stay responsive while the new thread will be processing the data in the background. Once it is done, it could notify the main thread.
And instead of using an ADOTable I would use an ADOQuery since it performs a lot better in many cases, especially with SQL Server. Unlike the TQuery component, you can even update records in a TADOQuery if you use a simple "select * from student" query.

And now you can probably guess how to improve speed a bit. :-) Don't use a table with a filter but use a query with a WHERE clause. Yes, it means opening and closing the query quite a few times but it will perform faster. Or use a locate() instead of a filter to find the student record in your table.
But okay, for the method you selected, a TADOTable should work as well as a TADOQuery with just "select * from students" as query without the WHERE clause. But don't filter the data, since filtering is way too slow. Use a locate!

      if Locate('std_id', VarArrayOf([ADOImport.FieldByName('std_id').AsString]), []) then begin
        ADOStudent.Edit;
      end else begin
        ADOStudent.Insert;
        // this must be with insert only, you will get an error if you use it above with edit
        ADOStudent.FieldByName('std_id').AsString := ADOImport.FieldByName('std_id').AsString;
      end;

That would be faster!
0
Wim ten BrinkSelf-employed developerCommented:
I meant:
  if ADOStudent.Locate('std_id', VarArrayOf([ADOImport.FieldByName('std_id').AsString]), []) then begin
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.