Topic: AutoImport CSV

Hello...

I want to automatically import the data contained in a CSV file.

This is the code I use, but I want it to be a predefined file that is in a folder inside the program root, not using a dialog window.

procedure frmMain_Button4_OnClick (Sender: TObject; var Cancel: boolean);
var
   userVar, modeVar : String;
   DateVar, TimeVar : Extended;
   OpenDialog: TOpenDialog;
   sl: TStringList;
   arrStr: array of string;

   i,c: integer;
begin
     OpenDialog := TOpenDialog.Create(frmMain);
     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], ';');

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

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

               // 2do Apellido
               if arrStr[2] <> '' then arrStr[2] := ''''+ ReplaceStr(arrStr[2], '''', '''''') + ''''
                   else arrStr[2] := 'NULL';

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

               modeVar := 'Importado';
               userVar := inttostr(frmLogin.cbLogin.dbItemID);
               DateVar := Now;
               TimeVar := Time;

               SQLExecute ('INSERT INTO student (firstname, lastname, surname, ci, pestado, pparticular, mestado, mparticular, ujc, cdr, pcc, feu, fmc) VALUES ('+ arrStr[0] +','+ arrStr[1] +','+ arrStr[2] +','+ arrStr[3]+', 0, 0, 0, 0, 0, 0, 0, 0, 0);');

               SQLExecute('INSERT INTO registro'+
               '(dateadded, timeadded, firstname, lastname, surname, ci, mode, id_users) '+
               'VALUES("'+DateToStr(DateVar)+'","'+TimeToStr(TimeVar)+'",'+ arrStr[0] +','+ arrStr[1] +','+ arrStr[2] +','+ arrStr[3] +',"'+modeVar+'","'+userVar+'")');

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


          UpdateDatabase('student');
          UpdateDatabase('registro');
          sl.Free;
          frmMain.TableGrid1.dbUpdate;
          frmMain.TableGrid2.dbUpdate;
          frmStaticsUser.TableGrid2.dbUpdate;
     end;
     OpenDialog.Free;

end; 

Re: AutoImport CSV

Just remove all references to the dialog and replace LoadFromFile with hard coded file name. See below:

procedure frmMain_Button4_OnClick (Sender: TObject; var Cancel: boolean);
var
   userVar, modeVar : String;
   DateVar, TimeVar : Extended;
   sl: TStringList;
   arrStr: array of string;

   i,c: integer;
begin
          sl := TStringList.Create;
          sl.LoadFromFile (ExtractFilePath(Application.Exename) + 'YourFile.csv');


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

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

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

               // 2do Apellido
               if arrStr[2] <> '' then arrStr[2] := ''''+ ReplaceStr(arrStr[2], '''', '''''') + ''''
                   else arrStr[2] := 'NULL';

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

               modeVar := 'Importado';
               userVar := inttostr(frmLogin.cbLogin.dbItemID);
               DateVar := Now;
               TimeVar := Time;

               SQLExecute ('INSERT INTO student (firstname, lastname, surname, ci, pestado, pparticular, mestado, mparticular, ujc, cdr, pcc, feu, fmc) VALUES ('+ arrStr[0] +','+ arrStr[1] +','+ arrStr[2] +','+ arrStr[3]+', 0, 0, 0, 0, 0, 0, 0, 0, 0);');

               SQLExecute('INSERT INTO registro'+
               '(dateadded, timeadded, firstname, lastname, surname, ci, mode, id_users) '+
               'VALUES("'+DateToStr(DateVar)+'","'+TimeToStr(TimeVar)+'",'+ arrStr[0] +','+ arrStr[1] +','+ arrStr[2] +','+ arrStr[3] +',"'+modeVar+'","'+userVar+'")');

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


          UpdateDatabase('student');
          UpdateDatabase('registro');
          sl.Free;
          frmMain.TableGrid1.dbUpdate;
          frmMain.TableGrid2.dbUpdate;
          frmStaticsUser.TableGrid2.dbUpdate;

end; 

Re: AutoImport CSV

Hello..
Thanks, very useful correction
Wen.

4 (edited by Kn0xx 2018-04-20 08:53:41)

Re: AutoImport CSV

Hi,

beginner here,

I'm trying to import a CSV with Date in format dd/mm/yyyy, but MVD wont recognize that format using datepicker search ( database uses yyyy-mm-dd),

How can I convert arrstr[0] ( its my date column ) into database, in a format that MVD understands ?

Any help ?

Ty

Re: AutoImport CSV

Hello.


If this date format dd/mm/yyyy correct for your regional setting of PC, you can use this script to convert date to database (arrStr[2])

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';

               // Date
               if ValidDate(arrStr[2]) then
               begin
                   arrStr[2] := ''''+FormatDateTime('yyyy-MM-DD 00:00:00.000', StrToDate(arrStr[2]))+'''';
               end else arrStr[2] := 'NULL';


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


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



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

6 (edited by Kn0xx 2018-04-20 23:06:59)

Re: AutoImport CSV

Ty !

Edit: I've figure it out.

Searched another example from an old versions.