Topic: Импорт
Друзья может кто подскажет как изменить данный скрипт (не хватает понимания его работы) чтобы он не проверял на дубликат?
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, ',', ',');
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, ',', ',');
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;