• Registered: 2014-02-14
  • Posts: 10,294

Topic: [Script] Import CSV file into the database

Import CSV file into the database


procedure Form1_Button2_OnClick (Sender: string; var Cancel: boolean);
var
   OpenDialog: TOpenDialog;
   sl: TStringList;
   arrStr: array of string;

   i,c: integer;
begin
     OpenDialog := TOpenDialog.Create(Form1);
     OpenDialog.InitialDir := ExtractFileDir(Application.Exename);

     if OpenDialog.Execute then
     begin
          sl := TStringList.Create;
          sl.LoadFromFile (OpenDialog.FileName);


          c := sl.Count - 1;
          for i := 0 to c do
          begin
               arrStr := SplitString(sl[i], ';');

               // LastName
               if arrStr[0] <> '' then arrStr[0] := ''''+ ReplaceStr(arrStr[0], '''', '''''') + ''''
                   else arrStr[0] := 'NULL';

               // FirstName
               if arrStr[1] <> '' then arrStr[1] := ''''+ ReplaceStr(arrStr[1], '''', '''''') + ''''
                   else arrStr[1] := 'NULL';

               SQLExecute ('INSERT INTO base (lastname, firstname) VALUES ('+ arrStr[0] +','+ arrStr[1]+');');


               Form1.Label1.Caption := IntToStr(i+1) + ' of ' + IntToStr(c+1);
               Application.ProcessMessages;
          end;



          sl.Free;
          Form1.TableGrid1.dbUpdate;
     end;
     OpenDialog.Free;
end;

begin
end.


Download project:
http://myvisualdatabase.com/forum/misc. … download=1

Dmitry.
  • Registered: 2014-06-25
  • Posts: 874

Re: [Script] Import CSV file into the database

How to check duplicates during import to avoid double entries. I want to try with 2 columns at first.

  • Registered: 2014-02-14
  • Posts: 10,294

Re: [Script] Import CSV file into the database

You can do sql query before insert data, to check duplicate, if duplicate is found, skip cycle (if q > 0 then Continue;)


procedure Form1_Button2_OnClick (Sender: string; var Cancel: boolean);
var
   OpenDialog: TOpenDialog;
   sl: TStringList;
   arrStr: array of string;

   i,c: integer;
   q: integer;
begin
     OpenDialog := TOpenDialog.Create(Form1);
     OpenDialog.InitialDir := ExtractFileDir(Application.Exename);

     if OpenDialog.Execute then
     begin
          sl := TStringList.Create;
          sl.LoadFromFile (OpenDialog.FileName);


          c := sl.Count - 1;
          for i := 0 to c do
          begin
               arrStr := SplitString(sl[i], ';');

               // LastName
               if arrStr[0] <> '' then arrStr[0] := ''''+ ReplaceStr(arrStr[0], '''', '''''') + ''''
                   else arrStr[0] := 'NULL';

               // FirstName
               if arrStr[1] <> '' then arrStr[1] := ''''+ ReplaceStr(arrStr[1], '''', '''''') + ''''
                   else arrStr[1] := 'NULL';

              q := SQLExecute('SELECT COUNT(*) FROM base WHERE lastname=' + arrStr[0] + ' AND  firstname='+ arrStr[1] );
              if q > 0 then Continue;

               SQLExecute ('INSERT INTO base (lastname, firstname) VALUES ('+ arrStr[0] +','+ arrStr[1]+');');


               Form1.Label1.Caption := IntToStr(i+1) + ' of ' + IntToStr(c+1);
               Application.ProcessMessages;
          end;



          sl.Free;
          Form1.TableGrid1.dbUpdate;
     end;
     OpenDialog.Free;
end;

begin
end.
Dmitry.
  • Registered: 2014-06-25
  • Posts: 874

Re: [Script] Import CSV file into the database

Great, this is simple control but how do you manage the error when at least one of the entry already exists, I would like to know what is groing wrong.

For instance, I want to detect duplicates on whichever column and also wrong csv format (people forget sometimes to remove the headers) to display appropriate error for easy remediation.

  • Registered: 2014-02-14
  • Posts: 10,294

Re: [Script] Import CSV file into the database

You can show error message or something else when record already exists.

if q > 0 then 
begin
   ShowMessage('Record exists: '+ arrStr[0] +','+ arrStr[1]);
   Continue;
end;

Also you can check, if people forget to remove the headers.

// check first line in CSV
if i=0 then 
begin
   if arrStr[0]='column name1' and arrStr[0]='column name2'  then  Continue; // ignore line with headers
end;



result:

procedure Form1_Button2_OnClick (Sender: string; var Cancel: boolean);
var
   OpenDialog: TOpenDialog;
   sl: TStringList;
   arrStr: array of string;

   i,c: integer;
   q: integer;
begin
     OpenDialog := TOpenDialog.Create(Form1);
     OpenDialog.InitialDir := ExtractFileDir(Application.Exename);

     if OpenDialog.Execute then
     begin
          sl := TStringList.Create;
          sl.LoadFromFile (OpenDialog.FileName);


          c := sl.Count - 1;
          for i := 0 to c do
          begin
               arrStr := SplitString(sl[i], ';');

               // check first line in CSV
               if i=0 then 
               begin
                  if arrStr[0]='column name1' and arrStr[1]='column name2'  then  Continue; // ignore line with headers
               end;

               // LastName
               if arrStr[0] <> '' then arrStr[0] := ''''+ ReplaceStr(arrStr[0], '''', '''''') + ''''
                   else arrStr[0] := 'NULL';

               // FirstName
               if arrStr[1] <> '' then arrStr[1] := ''''+ ReplaceStr(arrStr[1], '''', '''''') + ''''
                   else arrStr[1] := 'NULL';

              q := SQLExecute('SELECT COUNT(*) FROM base WHERE lastname=' + arrStr[0] + ' AND  firstname='+ arrStr[1] );
              if q > 0 then 
              begin
                 ShowMessage('Record exists: '+ arrStr[0] +','+ arrStr[1]);
                 Continue;
              end;

               SQLExecute ('INSERT INTO base (lastname, firstname) VALUES ('+ arrStr[0] +','+ arrStr[1]+');');


               Form1.Label1.Caption := IntToStr(i+1) + ' of ' + IntToStr(c+1);
               Application.ProcessMessages;
          end;



          sl.Free;
          Form1.TableGrid1.dbUpdate;
     end;
     OpenDialog.Free;
end;

begin
end.
Dmitry.
  • Registered: 2014-06-25
  • Posts: 874

Re: [Script] Import CSV file into the database

Hi team, I am back to MVDB after some years and I really need help to implement an import/export CSV data button in a form.

Using MVDB 6.5, there is a native function to import / export data that are available straight from the menu Options. How can we pop the same windows using a button within a form instead of writing a lot of scripts or showing the menu options?

What is the function behind the menus Options --> Export Data and Options --> Import Data?

That would help tremendously!!

  • Registered: 2014-06-25
  • Posts: 874

Re: [Script] Import CSV file into the database

Never mind, I searched a little bit more and found it here:

http://myvisualdatabase.com/forum/viewtopic.php?id=5315

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,786

Re: [Script] Import CSV file into the database

Hi,
Nice to have you back.
Please see attached as a way of doing it.
Regards,
Derek.

Post's attachments

Attachment icon exportimport buttons.zip 335.38 kb, 417 downloads since 2022-07-29 

  • Registered: 2014-06-25
  • Posts: 874

Re: [Script] Import CSV file into the database

Hi Derek, thanks, that is exactly the solution I used after digging in the forum with the link above. It is actually the easiest way of doing it.