Импорт файлов (Page 1 of 2)

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

   i,c: integer;

   sid_sked: string; //опись
   sDocNum: string; //номер дела
   sDocName: string; //название
   sid_year: string; //год формирования
   sBeginDate: string; //дата начала
   sEndDate: string; //дата окончания
   sid_period: string; //срок хранения
   sPages: string; //количество страниц
   sid_room: string; //комната
   sid_scaff: string; //стеллаж
   sid_rack: string; //полка
   sNote: string; //примечание

   YY, MM, DD: string;
begin
     OpenDialog := TOpenDialog.Create(frmChoice);
     OpenDialog.InitialDir := 'd:\Archive 1.01\ImportCSV';
     if OpenDialog.Execute then
     begin
          sl := TStringList.Create;
          sl.LoadFromFile (OpenDialog.FileName); ///открытие файла

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

               //0 sid_sked
               if arrStr[0] <> '' then
               begin
                    sid_sked := SQLExecute ('SELECT id FROM sked WHERE sked LIKE "' + arrStr[0] +'"');
                    if sid_sked = '' then
                    begin
                         SQLExecute ('INSERT INTO sked (sked) VALUES ("'+ arrStr[0] +'");');
                         sid_sked := IntToStr(Last_Insert_id('sked'));
                         if sid_sked = '-1' then sid_sked := 'NULL';
                    end;
               end else sid_sked := 'NULL';

               //1 sDocNum
               if arrStr[1]<>'' then sDocNum := '"'+arrStr[1]+'"' else sDocNum := 'NULL';

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

               //3 sid_year
               if arrStr[3] <> '' then
               begin
                    sid_year := SQLExecute ('SELECT id FROM year WHERE year LIKE "' + arrStr[3] +'"');
                    if sid_year = '' then
                    begin
                         SQLExecute ('INSERT INTO year (year) VALUES ("'+ arrStr[3] +'");');
                         sid_year := IntToStr(Last_Insert_id('year'));
                         if sid_year = '-1' then sid_year := 'NULL';
                    end;
               end else sid_year := 'NULL';


               //4 sBeginDate
               if arrStr[4]<>'' then
                  sBeginDate := '"'+ FormatDateTime('yyyy-MM-DD 00:00:00.000', StrToDate(arrStr[4])) + '"'
                  else sBeginDate := 'NULL';


               //5 sEndDate
               if arrStr[5]<>'' then
                  sEndDate := '"'+ FormatDateTime('yyyy-MM-DD 00:00:00.000', StrToDate(arrStr[5])) + '"'
                  else sEndDate := 'NULL';


               //6 sid_period
               if arrStr[6] <> '' then
               begin
                    sid_period := SQLExecute ('SELECT id FROM period WHERE period LIKE "' + arrStr[6] +'"');
                    if sid_period = '' then
                    begin
                         SQLExecute ('INSERT INTO period (period) VALUES ("'+ arrStr[6] +'");');
                         sid_period := IntToStr(Last_Insert_id('period'));
                         if sid_period = '-1' then sid_period := 'NULL';
                    end;
               end else sid_period := 'NULL';

               //7 sPages
               if arrStr[7]<>'' then sPages := '"'+arrStr[7]+'"' else sPages := 'NULL';

               //8 sid_room
               if arrStr[8] <> '' then
               begin
                    sid_room := SQLExecute ('SELECT id FROM room WHERE room LIKE "' + arrStr[8] +'"');
                    if sid_period = '' then
                    begin
                         SQLExecute ('INSERT INTO room (room) VALUES ("'+ arrStr[8] +'");');
                         sid_room := IntToStr(Last_Insert_id('room'));
                         if sid_room = '-1' then sid_room := 'NULL';
                    end;
               end else sid_room := 'NULL';

               //9 sid_scaff
               if arrStr[9] <> '' then
               begin
                    sid_scaff := SQLExecute ('SELECT id FROM scaff WHERE scaff LIKE "' + arrStr[9] +'"');
                    if sid_period = '' then
                    begin
                         SQLExecute ('INSERT INTO scaff (scaff) VALUES ("'+ arrStr[9] +'");');
                         sid_scaff := IntToStr(Last_Insert_id('scaff'));
                         if sid_scaff = '-1' then sid_scaff := 'NULL';
                    end;
               end else sid_scaff := 'NULL';

               //10 sid_rack
               if arrStr[10] <> '' then
               begin
                    sid_rack := SQLExecute ('SELECT id FROM rack WHERE rack LIKE "' + arrStr[10] +'"');
                    if sid_rack = '' then
                    begin
                         SQLExecute ('INSERT INTO rack (rack) VALUES ("'+ arrStr[10] +'");');
                         sid_rack := IntToStr(Last_Insert_id('rack'));
                         if sid_rack = '-1' then sid_rack := 'NULL';
                    end;
               end else sid_rack := 'NULL';

               //11 sNote
               if arrStr[11]<>'' then sNote := '"'+arrStr[11]+'"' else sNote := 'NULL';

               SQLExecute ('INSERT INTO one (id_sked, [docNum], [docName], id_year, [beginDate], [endDate], id_period, [pages], id_room, id_scaff, id_rack, [note]) VALUES ('+
               sid_sked +','+
               sDocNum +','+
               sDocName +','+
               sid_year +','+
               sBeginDate +','+
               sEndDate +','+
               sid_period +','+
               sPages +','+
               sid_room +','+
               sid_scaff +','+
               sid_rack +','+
               sNote +
               ');');

               frmChoice.Label8.Caption := IntToStr(i) + ' з ' + IntToStr(c);
               Application.ProcessMessages;
          end;

          sl.Free;
          UpdateDatabase('sked');
          UpdateDatabase('year');
          UpdateDatabase('period');
          UpdateDatabase('room');
          UpdateDatabase('scaff');
          UpdateDatabase('rack');
          frmChoice.TableGrid1.dbUpdate;
     end;
     OpenDialog.Free;
begin
     frmChoice.bSearch1.Click;  ////!!!!!Нажатие на кнопку ПОИСК (при этом видимые записи таблицы обновляются)
end;

end;

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

               //13 sid_period
               if arrStr[13] <> '' then
               begin
                    sid_period := SQLExecute ('SELECT id FROM period WHERE period LIKE "' + arrStr[13] +'"');
                    if sid_period = '' then
                    begin
                         SQLExecute ('INSERT INTO period (period) VALUES ("'+ arrStr[13] +'");');
                         sid_period := IntToStr(Last_Insert_id('period'));
                         if sid_period = '-1' then sid_period := 'NULL';
                    end;
               end else sid_period := 'NULL';
/////ЗДЕСЬ
               //15 sid_room
               if arrStr[15] <> '' then
               begin
                    sid_room := SQLExecute ('SELECT id FROM room WHERE room LIKE "' + arrStr[15] +'"');
                    if sid_period = '' then
                    begin
                         SQLExecute ('INSERT INTO room (room) VALUES ("'+ arrStr[15] +'");');
                         sid_room := IntToStr(Last_Insert_id('room'));
                         if sid_room = '-1' then sid_room := 'NULL';
                    end;
               end else sid_room := 'NULL';


///И ЗДЕСЬ (вместо ?????????):
               SQLExecute ('INSERT INTO two (id_dept, [actNum], [actDate], [docNum], [docName], [docDate], id_type, [indexNum], [indexName], [folderNum], [internalNum], [pages], id_year, id_period, ?????????, id_room) VALUES ('+
               sid_dept +','+
               sActNum +','+
               sActDate +','+
               sDocNum +','+
               sDocName +','+
               sDocDate +','+
               sid_type +','+
               sIndexNum +','+
               sIndexName +','+
               sFolderNum +','+
               sInternalNum +','+
               sPages +','+
               sid_year +','+
???????????????????????
               sid_period +','+
               sid_room +
               ');');