unit loaddata;

Winapi.Windows, System.SysUtils, System.Classes, Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Vcl.StdCtrls, Vcl.ComCtrls, Vcl.CheckLst,

SynRegExpr, Vcl.Buttons, Vcl.ExtCtrls, Vcl.ToolWin, Vcl.ExtDlgs, System.Math, System.IOUtils, extra_controls,

dbconnection, dbstructures, gnugettext;

Tloaddataform = class(TExtForm)

btnImport: TButton;

btnCancel: TButton;

lblDatabase: TLabel;

comboDatabase: TComboBox;

lblTable: TLabel;

comboTable: TComboBox;

lblColumns: TLabel;

chklistColumns: TCheckListBox;

ToolBarColMove: TToolBar;

btnColUp: TToolButton;

btnColDown: TToolButton;

grpFilename: TGroupBox;

editFilename: TButtonedEdit;

grpChars: TGroupBox;

lblFieldTerminater: TLabel;

lblFieldEncloser: TLabel;

lblFieldEscaper: TLabel;

editFieldEscaper: TEdit;

editFieldEncloser: TEdit;

editFieldTerminator: TEdit;

chkFieldsEnclosedOptionally: TCheckBox;

grpOptions: TGroupBox;

lblIgnoreLinesCount: TLabel;

updownIgnoreLines: TUpDown;

editIgnoreLines: TEdit;

editLineTerminator: TEdit;

lblLineTerminator: TLabel;

lblIgnoreLines: TLabel;

lblFilename: TLabel;

comboEncoding: TComboBox;

lblEncoding: TLabel;

grpDuplicates: TRadioGroup;

grpParseMethod: TRadioGroup;

grpDestination: TGroupBox;

chkLowPriority: TCheckBox;

chkLocalNumbers: TCheckBox;

chkTruncateTable: TCheckBox;

btnCheckAll: TToolButton;

chkKeepDialogOpen: TCheckBox;

const ProgressBarSteps=100;

procedure FormCreate(Sender: TObject);

procedure editFilenameChange(Sender: TObject);

procedure FormShow(Sender: TObject);

procedure comboDatabaseChange(Sender: TObject);

procedure comboTablePopulate(SelectTableName: String; RefreshDbObjects: Boolean);

procedure comboTableChange(Sender: TObject);

procedure btnImportClick(Sender: TObject);

procedure ServerParse(Sender: TObject);

procedure ClientParse(Sender: TObject);

procedure btnOpenFileClick(Sender: TObject);

procedure btnColMoveClick(Sender: TObject);

procedure grpParseMethodClick(Sender: TObject);

procedure FormClose(Sender: TObject; var Action: TCloseAction);

procedure chklistColumnsClick(Sender: TObject);

procedure btnCheckAllClick(Sender: TObject);

procedure FormResize(Sender: TObject);

private

{ Private declarations }

FFileEncoding: TEncoding;

FTerm, FEncl, FEscp, FLineTerm: String;

FRowCount, FColumnCount: Integer;

FColumns: TTableColumnList;

FConnection: TDBConnection;

public

{ Public declarations }

property FileEncoding: TEncoding read FFileEncoding;

end;

uses Main, apphelpers, csv_detector;

procedure Tloaddataform.FormCreate(Sender: TObject);

HasSizeGrip := True;

// Restore settings

editFilename.Text := AppSettings.ReadString(asCSVImportFilename);

editFieldTerminator.Text := AppSettings.ReadString(asCSVImportSeparator);

editFieldEncloser.Text := AppSettings.ReadString(asCSVImportEncloser);

editLineTerminator.Text := AppSettings.ReadString(asCSVImportTerminator);

chkFieldsEnclosedOptionally.Checked := AppSettings.ReadBool(asCSVImportFieldsEnclosedOptionally);

editFieldEscaper.Text := AppSettings.ReadString(asCSVImportFieldEscaper);

updownIgnoreLines.Position := AppSettings.ReadInt(asCSVImportIgnoreLines);

chkLowPriority.Checked := AppSettings.ReadBool(asCSVImportLowPriority);

chkLocalNumbers.Checked := AppSettings.ReadBool(asCSVImportLocalNumbers);

chkKeepDialogOpen.Checked := AppSettings.ReadBool(asCSVKeepDialogOpen);

// Uncheck critical "Truncate table" checkbox, to avoid accidental data removal

chkTruncateTable.Checked := False;

grpDuplicates.ItemIndex := AppSettings.ReadInt(asCSVImportDuplicateHandling);

grpParseMethod.ItemIndex := AppSettings.ReadInt(asCSVImportParseMethod);

end;

procedure Tloaddataform.FormResize(Sender: TObject);

HalfWidth, RightBoxX: Integer;

// Rethink width of side-by-side group boxes

HalfWidth := (ClientWidth - 3 * grpFilename.Left) div 2;

RightBoxX := HalfWidth + 2 * grpFilename.Left;

grpOptions.Width := HalfWidth;

grpDuplicates.Width := HalfWidth;

grpParseMethod.Width := HalfWidth;

grpChars.Width := HalfWidth;

grpDestination.Width := HalfWidth;

// Move right boxes to the right position

grpChars.Left := RightBoxX;

grpDestination.Left := RightBoxX;

end;

procedure Tloaddataform.FormShow(Sender: TObject);

Width := AppSettings.ReadIntDpiAware(asCSVImportWindowWidth, Self);

Height := AppSettings.ReadIntDpiAware(asCSVImportWindowHeight, Self);

FConnection := MainForm.ActiveConnection;

// Disable features supported in MySQL only, if active connection is not MySQL

if not FConnection.Parameters.IsAnyMySQL then begin

grpParseMethod.ItemIndex := 1;

grpDuplicates.ItemIndex := 0;

end;

grpParseMethod.Controls[0].Enabled := FConnection.Parameters.IsAnyMySQL;

grpDuplicates.Controls[1].Enabled := FConnection.Parameters.IsAnyMySQL;

grpDuplicates.Controls[2].Enabled := FConnection.Parameters.IsAnyMySQL;

chkLowPriority.Enabled := FConnection.Parameters.IsAnyMySQL;

// Read databases and tables from active connection

comboDatabase.Items.Clear;

comboDatabase.Items.Assign(FConnection.AllDatabases);

comboDatabase.ItemIndex := comboDatabase.Items.IndexOf(Mainform.ActiveDatabase);

if comboDatabase.ItemIndex = -1 then

comboDatabase.ItemIndex := 0;

comboDatabaseChange(Sender);

editFilename.SetFocus;

end;

procedure Tloaddataform.FormClose(Sender: TObject; var Action: TCloseAction);

// Save settings

AppSettings.WriteIntDpiAware(asCSVImportWindowWidth, Self, Width);

AppSettings.WriteIntDpiAware(asCSVImportWindowHeight, Self, Height);

AppSettings.WriteString(asCSVImportFilename, editFilename.Text);

AppSettings.WriteString(asCSVImportSeparator, editFieldTerminator.Text);

AppSettings.WriteString(asCSVImportEncloser, editFieldEncloser.Text);

AppSettings.WriteString(asCSVImportTerminator, editLineTerminator.Text);

AppSettings.WriteBool(asCSVImportFieldsEnclosedOptionally, chkFieldsEnclosedOptionally.Checked);

AppSettings.WriteString(asCSVImportFieldEscaper, editFieldEscaper.Text);

AppSettings.WriteInt(asCSVImportIgnoreLines, updownIgnoreLines.Position);

AppSettings.WriteBool(asCSVImportLowPriority, chkLowPriority.Checked);

AppSettings.WriteBool(asCSVImportLocalNumbers, chkLocalNumbers.Checked);

AppSettings.WriteBool(asCSVKeepDialogOpen, chkKeepDialogOpen.Checked);

AppSettings.WriteInt(asCSVImportDuplicateHandling, grpDuplicates.ItemIndex);

AppSettings.WriteInt(asCSVImportParseMethod, grpParseMethod.ItemIndex);

end;

procedure Tloaddataform.grpParseMethodClick(Sender: TObject);

ServerWillParse: Boolean;

FileCharset: String;

v, i: Integer;

ServerWillParse := grpParseMethod.ItemIndex = 0;

comboEncoding.Enabled := ServerWillParse;

editFieldEscaper.Enabled := ServerWillParse;

chkFieldsEnclosedOptionally.Enabled := ServerWillParse;

comboEncoding.Clear;

if comboEncoding.Enabled then begin

// Populate charset combo

v := FConnection.ServerVersionInt;

if ((v >= 50038) and (v < 50100)) or (v >= 50117) then begin

FileCharset := MainForm.GetCharsetByEncoding(FFileEncoding);

if FileCharset.IsEmpty then

FileCharset := 'utf8';

comboEncoding.Items := FConnection.CharsetList;

// Preselect file encoding, or utf8 as a fallback

for i:=0 to comboEncoding.Items.Count-1 do begin

if ExecRegExpr('^'+QuoteRegExprMetaChars(FileCharset)+'\b', comboEncoding.Items[i]) then begin

comboEncoding.ItemIndex := i;

Break;

end;

end;

end else begin

comboEncoding.Items.Add(_(SUnsupported));

comboEncoding.ItemIndex := 0;

end;

end else begin

comboEncoding.Items.Add(Mainform.GetEncodingName(FFileEncoding));

comboEncoding.ItemIndex := 0;

end;

end;

procedure Tloaddataform.comboDatabaseChange(Sender: TObject);

comboTablePopulate('', False);

grpParseMethod.OnClick(Sender);

comboTableChange(Sender);

end;

procedure Tloaddataform.comboTablePopulate(SelectTableName: String; RefreshDbObjects: Boolean);

count, i: Integer;

DBObjects: TDBObjectList;

seldb, seltable: String;

// read tables from db

comboTable.Items.Clear;

seldb := Mainform.ActiveDatabase;

seltable := Mainform.ActiveDbObj.Name;

DBObjects := FConnection.GetDBObjects(comboDatabase.Text, RefreshDbObjects);

for i:=0 to DBObjects.Count-1 do begin

if DBObjects[i].NodeType in [lntTable, lntView] then

comboTable.Items.Add(DBObjects[i].Name);

count := comboTable.Items.Count-1;

if SelectTableName.IsEmpty and (comboDatabase.Text = seldb) and (comboTable.Items[count] = seltable) then

comboTable.ItemIndex := count

else if (not SelectTableName.IsEmpty) and (SelectTableName = comboTable.Items[count]) then

comboTable.ItemIndex := count;

end;

if (comboTable.ItemIndex = -1) and (comboTable.Items.Count >= 1) then

comboTable.ItemIndex := 0; // First real table

comboTable.Items.Add('<'+_('New table')+'>');

end;

procedure Tloaddataform.comboTableChange(Sender: TObject);

Col: TTableColumn;

DBObjects: TDBObjectList;

Obj: TDBObject;

// fill columns, or show csv detector:

chklistColumns.Items.Clear;

if comboTable.ItemIndex = comboTable.Items.Count-1 then begin

frmCsvDetector := TfrmCsvDetector.Create(Self);

case frmCsvDetector.ShowModal of

mrOk: begin

// table got created and combo is refreshed

end;

else begin

comboTable.ItemIndex := 0;

end;

end;

frmCsvDetector.Free;

frmCsvDetector := nil; // check for Assigned() must be false in SetupSynEditors

end;

if (comboDatabase.Text <> '') and (comboTable.Text <> '') then begin

if not Assigned(FColumns) then

FColumns := TTableColumnList.Create;

DBObjects := FConnection.GetDBObjects(comboDatabase.Text);

for Obj in DBObjects do begin

if (Obj.Database=comboDatabase.Text) and (Obj.Name=comboTable.Text) then begin

case Obj.NodeType of

lntTable, lntView: FColumns := Obj.TableColumns;

end;

end;

end;

for Col in FColumns do

chklistColumns.Items.Add(Col.Name);

end;

// select all:

chklistColumns.CheckAll(cbChecked);

chklistColumns.OnClick(Sender);

// Ensure valid state of Import-Button

editFilenameChange(Sender);

end;

procedure Tloaddataform.btnImportClick(Sender: TObject);

StartTickCount: Cardinal;

i: Integer;

Screen.Cursor := crHourglass;

StartTickCount := GetTickCount;

MainForm.EnableProgress(ProgressBarSteps);

// Truncate table before importing

if chkTruncateTable.Checked then try

FConnection.Query('TRUNCATE TABLE ' + FConnection.QuotedDbAndTableName(comboDatabase.Text, comboTable.Text));

FConnection.ShowWarnings;

except

try

FConnection.Query('DELETE FROM ' + FConnection.QuotedDbAndTableName(comboDatabase.Text, comboTable.Text));

FConnection.ShowWarnings;

except

on E:EDbError do

ErrorDialog(_('Cannot truncate table'), E.Message);

end;

end;

FColumnCount := 0;

for i:=0 to chkListColumns.Items.Count-1 do begin

if chkListColumns.Checked[i] then

Inc(FColumnCount);

end;

FTerm := FConnection.UnescapeString(editFieldTerminator.Text);

FEncl := FConnection.UnescapeString(editFieldEncloser.Text);

FLineTerm := FConnection.UnescapeString(editLineTerminator.Text);

FEscp := FConnection.UnescapeString(editFieldEscaper.Text);

if chkKeepDialogOpen.Checked then

ModalResult := mrNone;

try

case grpParseMethod.ItemIndex of

0: ServerParse(Sender);

1: ClientParse(Sender);

end;

MainForm.LogSQL(FormatNumber(FRowCount)+' rows imported in '+FormatNumber((GetTickcount-StartTickCount)/1000, 3)+' seconds.');

// Hint user if zero rows were detected in file

if FRowCount = 0 then begin

ErrorDialog(_('No rows were imported'),

_('This can have several causes:')+CRLF+

_(' - File is empty')+CRLF+

_(' - Wrong file encoding was selected or detected')+CRLF+

_(' - Field and/or line terminator do not fit to the file contents')

);

ModalResult := mrNone;

end;

except

on E:EDbError do begin

ModalResult := mrNone;

MainForm.SetProgressState(pbsError);

ErrorDialog(E.Message);

end;

on E:EStreamError do begin

// all file stream errors, eg. EFOpenError and EReadError

// http://docwiki.embarcadero.com/Libraries/Sydney/en/System.Classes.EStreamError

ModalResult := mrNone;

MainForm.SetProgressState(pbsError);

ErrorDialog(E.Message + sLineBreak + sLineBreak + editFilename.Text);

end;

end;

if ModalResult = mrNone then

btnCancel.Caption := _('Close');

Mainform.ShowStatusMsg;

MainForm.DisableProgress;

Screen.Cursor := crDefault;

end;

procedure Tloaddataform.ServerParse(Sender: TObject);

SQL, SetColVars, SelectedCharset: String;

i: Integer;

Filename: String;

SQL := 'LOAD DATA ';

if chkLowPriority.Checked and chkLowPriority.Enabled then

SQL := SQL + 'LOW_PRIORITY ';

// Issue #1387: Use 8.3 filename, to prevent "file not found" error from MySQL library

// Todo: test on Wine

Filename := ExtractShortPathName(editFilename.Text);

if not Filename.IsEmpty then

MainForm.LogSQL('Converting filename to 8.3 format: '+editFilename.Text+' => '+Filename, lcInfo)

else

Filename := editFilename.Text;

SQL := SQL + 'LOCAL INFILE ' + FConnection.EscapeString(Filename) + ' ';

case grpDuplicates.ItemIndex of

1: SQL := SQL + 'IGNORE ';

2: SQL := SQL + 'REPLACE ';

end;

SQL := SQL + 'INTO TABLE ' + FConnection.QuotedDbAndTableName(comboDatabase.Text, comboTable.Text) + ' ';

SelectedCharset := RegExprGetMatch('^(\w+)\b', comboEncoding.Text, 1);

if not SelectedCharset.IsEmpty then begin

SQL := SQL + 'CHARACTER SET '+SelectedCharset+' ';

end;

// Fields:

if (FTerm <> '') or (FEncl <> '') or (FEscp <> '') then

SQL := SQL + 'FIELDS ';

if editFieldTerminator.Text <> '' then

SQL := SQL + 'TERMINATED BY ' + FConnection.EscapeString(FTerm) + ' ';

if FEncl <> '' then begin

if chkFieldsEnclosedOptionally.Checked then

SQL := SQL + 'OPTIONALLY ';

SQL := SQL + 'ENCLOSED BY ' + FConnection.EscapeString(FEncl) + ' ';

end;

if FEscp <> '' then

SQL := SQL + 'ESCAPED BY ' + FConnection.EscapeString(FEscp) + ' ';

// Lines:

if FLineTerm <> '' then

SQL := SQL + 'LINES TERMINATED BY ' + FConnection.EscapeString(FLineTerm) + ' ';

if updownIgnoreLines.Position > 0 then

SQL := SQL + 'IGNORE ' + inttostr(updownIgnoreLines.Position) + ' LINES ';

// Column listing

SQL := SQL + '(';

SetColVars := '';

for i:=0 to chklistColumns.Items.Count-1 do begin

if chklistColumns.Checked[i] then begin

if chkLocalNumbers.Checked and (FColumns[i].DataType.Category in [dtcInteger, dtcReal]) then begin

SQL := SQL + '@ColVar' + IntToStr(i) + ', ';

SetColVars := SetColVars + FConnection.QuoteIdent(chklistColumns.Items[i]) +

' = REPLACE(REPLACE(@ColVar' + IntToStr(i) + ', '+FConnection.EscapeString(FormatSettings.ThousandSeparator)+', ''''), '+FConnection.EscapeString(FormatSettings.DecimalSeparator)+', ''.''), ';

end else

SQL := SQL + FConnection.QuoteIdent(chklistColumns.Items[i]) + ', ';

end;

end;

SetLength(SQL, Length(SQL)-2);

SQL := SQL + ')';

if SetColVars <> '' then begin

SetLength(SetColVars, Length(SetColVars)-2);

SQL := SQL + ' SET ' + SetColVars;

end;

FConnection.Query(SQL);

FRowCount := Max(FConnection.RowsAffected, 0);

FConnection.ShowWarnings;

end;

procedure Tloaddataform.ClientParse(Sender: TObject);

P, ContentLen, ProgressCharsPerStep, ProgressChars: Integer;

IgnoreLines, ValueCount, PacketSize: Integer;

LineNum: Int64;

RowCountInChunk: Int64;

EnclLen, TermLen, LineTermLen: Integer;

Contents: String;

EnclTest, TermTest, LineTermTest: String;

Value, SQL: String;

IsEncl, IsTerm, IsLineTerm, IsEof: Boolean;

InEncl: Boolean;

OutStream: TMemoryStream;

procedure NextChar;

begin

Inc(P);

Inc(ProgressChars);

if ProgressChars >= ProgressCharsPerStep then begin

Mainform.ProgressStep;

Mainform.ShowStatusMsg(f_('Importing textfile, row %s, %d%%', [FormatNumber(FRowCount-IgnoreLines), Mainform.ProgressBarStatus.Position]));

ProgressChars := 0;

end;

end;

function TestLeftChars(var Portion: String; CompareTo: String; Len: Integer): Boolean;

var i: Integer;

begin

if Len > 0 then begin

for i:=1 to Len-1 do

Portion[i] := Portion[i+1];

Portion[Len] := Contents[P];

Result := Portion = CompareTo;

end else

Result := False;

end;

procedure AddValue;

var

i: Integer;

LowPrio: String;

ColumnIndex: Integer;

ValuesCounted: Integer;

begin

Inc(ValueCount);

if ValueCount <= FColumnCount then begin

if Copy(Value, 1, EnclLen) = FEncl then begin

Delete(Value, 1, EnclLen);

Delete(Value, Length(Value)-EnclLen+1, EnclLen);

end;

if SQL = '' then begin

LowPrio := '';

if chkLowPriority.Checked and chkLowPriority.Enabled then

LowPrio := 'LOW_PRIORITY ';

case grpDuplicates.ItemIndex of

0: SQL := 'INSERT '+LowPrio;

1: SQL := 'INSERT '+LowPrio+'IGNORE ';

2: SQL := 'REPLACE '+LowPrio;

end;

SQL := SQL + 'INTO '+FConnection.QuotedDbAndTableName(comboDatabase.Text, comboTable.Text)+' (';

for i:=0 to chkListColumns.Items.Count-1 do begin

if chkListColumns.Checked[i] then

SQL := SQL + FConnection.QuoteIdent(chkListColumns.Items[i]) + ', ';

end;

SetLength(SQL, Length(SQL)-2);

SQL := SQL + ') VALUES (';

end;

// Bugfix for #327: Determine column to retrieve the type from by counting the number of columns before the current one INCLUDING the omitted ones

ColumnIndex := 0;

ValuesCounted := 0;

for i:=0 to chkListColumns.Items.Count-1 do

begin

if chkListColumns.Checked[i] then // column was already counted

Inc(ValuesCounted); // increase number of counted columns

if ValuesCounted = ValueCount then // did we count all included columns up to the current column?

Break;

Inc(ColumnIndex); // if all columns (until the current column) are checked, ColumnIndex is ValueCount-1, like before this patch

end;

if Value <> 'NULL' then begin

if chkLocalNumbers.Checked and (FColumns[ColumnIndex].DataType.Category in [dtcInteger, dtcReal]) then

Value := UnformatNumber(Value)

else

Value := FConnection.EscapeString(Value, FColumns[ColumnIndex].DataType);

end;

SQL := SQL + Value + ', ';

end;

Value := '';

end;

procedure AddRow;

var

SA: AnsiString;

ChunkSize: Int64;

i: Integer;

begin

if SQL = '' then

Exit;

Inc(LineNum);

for i:=ValueCount to FColumnCount do begin

Value := 'NULL';

AddValue;

end;

ValueCount := 0;

if LineNum > IgnoreLines then begin

Delete(SQL, Length(SQL)-1, 2);

StreamWrite(OutStream, SQL + ')');

SQL := '';

Inc(RowCountInChunk);

if (OutStream.Size < PacketSize) and (P < ContentLen) and (RowCountInChunk < FConnection.MaxRowsPerInsert) then begin

SQL := SQL + ', (';

end else begin

OutStream.Position := 0;

ChunkSize := OutStream.Size;

SetLength(SA, ChunkSize div SizeOf(AnsiChar));

OutStream.Read(PAnsiChar(SA)^, ChunkSize);

OutStream.Size := 0;

FConnection.Query(UTF8ToString(SA), False, lcScript);

Inc(FRowCount, Max(FConnection.RowsAffected, 0));

FConnection.ShowWarnings;

SQL := '';

RowCountInChunk := 0;

end;

end else

SQL := '';

end;

TermLen := Length(FTerm);

EnclLen := Length(FEncl);

LineTermLen := Length(FLineTerm);

SetLength(TermTest, TermLen);

SetLength(EnclTest, EnclLen);

SetLength(LineTermTest, LineTermLen);

InEncl := False;

SQL := '';

Value := '';

OutStream := TMemoryStream.Create;

// Turns SQL errors into warnings, e.g. when providing an empty string for an integer column

if FConnection.Parameters.IsAnyMySQL then begin

FConnection.Query('/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='''' */');

end;

MainForm.ShowStatusMsg(f_('Reading textfile (%s) ...', [FormatByteNumber(_GetFileSize(editFilename.Text))]));

Contents := ReadTextfile(editFilename.Text, FFileEncoding);

ContentLen := Length(Contents);

MainForm.ShowStatusMsg;

P := 0;

ProgressCharsPerStep := ContentLen div ProgressBarSteps;

ProgressChars := 0;

FRowCount := 0;

LineNum := 0;

RowCountInChunk := 0;

IgnoreLines := UpDownIgnoreLines.Position;

ValueCount := 0;

PacketSize := SIZE_MB div 2;

NextChar;

// TODO: read chunks!

while P <= ContentLen do begin

// Check characters left-side from current position

IsEncl := TestLeftChars(EnclTest, FEncl, EnclLen);

IsTerm := TestLeftChars(TermTest, FTerm, TermLen);

IsLineTerm := TestLeftChars(LineTermTest, FLineTerm, LineTermLen) and (ValueCount >= FColumnCount-1);

IsEof := P = ContentLen;

Value := Value + Contents[P];

if IsEncl then

InEncl := not InEncl;

if IsEof or (not InEncl) then begin

if IsLineTerm then begin

SetLength(Value, Length(Value)-LineTermLen);

AddValue;

end else if IsEof then begin

AddValue;

end else if IsTerm then begin

SetLength(Value, Length(Value)-TermLen);

AddValue;

end;

end;

if IsLineTerm and (not InEncl) then

AddRow;

NextChar;

end;

// Will check if SQL is empty and not run any query in that case:

AddRow;

Contents := '';

FreeAndNil(OutStream);

if FConnection.Parameters.IsAnyMySQL then begin

FConnection.Query('/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '''') */');

end;

end;

procedure Tloaddataform.btnOpenFileClick(Sender: TObject);

Dialog: TExtFileOpenDialog;

TestStream: TFileStream;

AppSettings.ResetPath;

Dialog := TExtFileOpenDialog.Create(Self);

Dialog.DefaultFolder := ExtractFilePath(editFilename.Text);

Dialog.FileName := ExtractFileName(editFilename.Text);

Dialog.AddFileType('*.csv', _('CSV files'));

Dialog.AddFileType('*.txt', _('Text files'));

Dialog.AddFileType('*.*', _('All files'));

Dialog.DefaultExtension := 'csv';

Dialog.Encodings.Assign(Mainform.FileEncodings);

Dialog.EncodingIndex := AppSettings.ReadInt(asFileDialogEncoding, Self.Name);

if Dialog.Execute then begin

editfilename.Text := Dialog.FileName;

FFileEncoding := Mainform.GetEncodingByName(Dialog.Encodings[Dialog.EncodingIndex]);

if FFileEncoding = nil then begin

MessageDialog(_('Auto detecting the encoding of a file is highly discouraged. You may experience data loss if the detection fails.') +

SLineBreak + SLineBreak +

_('To avoid this message select the correct encoding before pressing Open.'),

mtWarning, [mbOK]);

TestStream := TFileStream.Create(Dialog.Filename, fmOpenRead or fmShareDenyNone);

FFileEncoding := DetectEncoding(TestStream);

TestStream.Free;

end;

grpParseMethod.OnClick(Sender);

AppSettings.WriteInt(asFileDialogEncoding, Dialog.EncodingIndex, Self.Name);

end;

Dialog.Free;

end;

procedure Tloaddataform.chklistColumnsClick(Sender: TObject);

i, CheckedNum: Integer;

btnColDown.Enabled := (chklistColumns.ItemIndex > -1)

and (chklistColumns.ItemIndex < chklistColumns.Count-1);

btnColUp.Enabled := (chklistColumns.ItemIndex > -1)

and (chklistColumns.ItemIndex > 0);

// Toggle icon when none is selected

CheckedNum := 0;

for i:=0 to chklistColumns.Items.Count-1 do begin

if chklistColumns.Checked[i] then

Inc(CheckedNum);

end;

if CheckedNum < chklistColumns.Items.Count then

btnCheckAll.ImageIndex := 127

else

btnCheckAll.ImageIndex := 128;

end;

procedure Tloaddataform.btnCheckAllClick(Sender: TObject);

i, CheckedNum: Integer;

CheckedNum := 0;

for i:=0 to chklistColumns.Items.Count-1 do begin

if chklistColumns.Checked[i] then

Inc(CheckedNum);

end;

if CheckedNum < chklistColumns.Items.Count then

chklistColumns.CheckAll(cbChecked)

else

chklistColumns.CheckAll(cbUnchecked);

chklistColumns.OnClick(Sender);

end;

procedure Tloaddataform.btnColMoveClick(Sender: TObject);

CheckedSelected, CheckedTarget: Boolean;

TargetIndex: Integer;

// Move column name and its checkstate up or down

if Sender = btnColUp then

TargetIndex := chklistColumns.ItemIndex-1

else

TargetIndex := chklistColumns.ItemIndex+1;

if (TargetIndex > -1) and (TargetIndex < chklistColumns.Count) then begin

CheckedSelected := chklistColumns.Checked[chklistColumns.ItemIndex];

CheckedTarget := chklistColumns.Checked[TargetIndex];

chklistColumns.Items.Exchange(chklistColumns.ItemIndex, TargetIndex);

chklistColumns.Checked[chklistColumns.ItemIndex] := CheckedTarget;

chklistColumns.Checked[TargetIndex] := CheckedSelected;

chklistColumns.ItemIndex := TargetIndex;

end;

chklistColumns.OnClick(Sender);

end;

procedure Tloaddataform.editFilenameChange(Sender: TObject);

btnImport.Enabled := (editFilename.Text <> '')

and (chklistColumns.Items.Count > 0)

and (FileExists(editFilename.Text));

end;

end.