Импорт

Procedure ImportToSl (FileName: string);  // Import procedure using the passed csv filename to this procedure
 var
iField, cField: integer;
iLine, cLine: integer;
sl: TStringList;
arrValues: array of string;
arrFields: array of string;
sValue, sValues,TableName,Fields,Fields_No_Id,Where_Clause,idValue: string;
begin
    sl := TStringList.Create;
    sl.LoadFromFile(FileName);   // Load the contents of the csv file into the stringlist
    cLine := sl.Count-1;
    SQLExecute('PRAGMA foreign_keys=OFF'); // Turn foreign keys off during import process
    for iLine := 0 to cLine do   // Spin through each line of the stringlist
      begin
        ProgressBar.Position := ProgressBar.Position + 10;
        sValues := '';
        arrValues := SplitString(sl[iLine], ',');
       If arrValues[0] = 'TableName' then
         begin                             // Check to see if the line is a header line (each element contains the DB field name)
          cField := Length(arrValues)-1;
          Fields := '';
          idValue := '';
          for iField := 1 to cField do   // Go through each field of the header line
            begin
               Fields := Fields + arrValues[iField] + ',';  // Concatenate together with a comma separation
               Continue;
            end;
            Delete(Fields, Length(Fields), 1);     // Remove trailing comma
            Fields_No_Id := Fields;
            Delete(Fields_No_Id,1,3);              // Setup Field list without id in header for importing records without an id
            arrfields := SplitString(Fields, ','); // Load the header field list into array. This will be used in the SQL Where Clause for checking duplicates
          continue;
         end;

       If arrValues[0] <> 'TableName' then
         Begin                               // The line is not a header line, but a data line
            TableName := arrValues[0];
            cField := Length(arrValues)-1;
            for iField := 1 to cField do
              begin
                sValue := arrValues[iField];
                sValue := ReplaceStr(sValue, '&comma;', ',');
                sValue := ReplaceStr(sValue, '|', #13#10);
                sValues := sValues + sValue+',';            // Concatenate together with a comma separation. Used for SQL INSERT
              end;
            Delete(sValues, Length(sValues), 1);     // Remove trailing comma
         end;

       Where_Clause := ' Where ';
       For iField := 1 to cField do   // Go through each field array and value array and prepare where clause for duplicate checking
            Begin
                sValue := arrValues[iField];
                sValue := ReplaceStr(sValue, '&comma;', ',');
                sValue := ReplaceStr(sValue, '|', #13#10);
                If iField = 1 then
                    Begin
                      idValue := sValue;    // Trap id value in csv line
                      continue;             // Go to next field. Do not include in WHERE Clause.
                    end;
                  Where_Clause := Where_Clause + arrFields[iField-1] + ' = ' + sValue + ' and '; // Concatenate fields and values for Where Clause with an " and " in between. Used for SQL SELECT for duplicate checking.
            End;

        Delete(Where_Clause, Length(Where_Clause)-4,5); // Remove trailing " and "  from Where Clause

        If idValue = '''''' then       // Check for empty id in csv line. id field contains ''
          Begin
             Delete(sValues,1,3);    // Remove 'id,' from field names list because id is empty in csv line
             If SqlExecute('Select id From ' + TableName + Where_Clause) <> 0 then Dups := Dups + 1   // check for duplicates in all fields except id. If dupicates then increment dup counter
               else SQLExecute('INSERT INTO '+TableName+' ('+Fields_No_Id+') VALUES ('+sValues+')'); // If not duplicate then insert record with new id
          end;

        If idValue <> '''''' then     // If id is not empty then check for duplicate id in table. If not exist then insert record.
           Begin
             If SqlExecute('Select id From ' + TableName + ' Where id = ' + idValue) <> 0 then Dups := Dups + 1  // Check for duplicates. If duplicate then increment dup count.
               else SQLExecute('INSERT INTO '+TableName+' ('+Fields+') VALUES ('+sValues+')');   // If not duplicate then insert record
           end;
       end;
      sl.Free;
      SQLExecute('PRAGMA foreign_keys=ON');  // Turn foreign keys back on
end;