Delphi and binary character set (for MySQL)

Question

I need import big xls-file into MySQL base. I try to use "LOAD DATA INFILE" construction. Data in xls contains cyrillic characters and so I have some problems with it. I create dump file manually, but "LOAD DATA INFILE" don't load cyrillic characters. MySQL instruction "SELECT ... INTO OUTFILE" work perfect, but I can't use it because of I need import from "SELECT ... INTO OUTFILE" save file with binary character set, how I can do so with Delphi 2009?

My code:

procedure TfmImportGoods.btnImportClick(Sender: TObject);
var
  sToFile: TStringStream;
  sTemp: AnsiString;

  function FixString(s: String): String;
  begin
    Result := s;
    Result := SysUtils.StringReplace(Result, #10, '', [rfReplaceAll]);
    Result := SysUtils.StringReplace(Result, #13, '', [rfReplaceAll]);
  end;

begin
  fmMain.MyScript.SQL.Text := 'TRUNCATE TABLE goods';
  fmMain.MyScript.Execute;

  adoImport.Close;
  adoImport.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+
    fmMain.dlgOpenGoods.FileName+
    ';Mode=ReadWrite|Share Deny None;Extended Properties=Excel 8.0;Persist Security Info=False';

  adoImport.SQL.Text := 'SELECT * FROM ['+lbTables.Items[0]+']';
  adoImport.Open;

  sTemp := '';
  try
    while not adoImport.Eof do
    begin
      sTemp := sTemp +
        #9+adoImport.FieldByName('Код').AsString+#9+
        adoImport.FieldByName('Производитель').AsString+#9+
        adoImport.FieldByName('Модель').AsString+#9+
        adoImport.FieldByName('Артикул').AsString+#9+
        FixString(adoImport.FieldByName('Описание').AsString)+#9+
        adoImport.FieldByName('Кол-во').AsString+#9+
        //TODO: проверить
        {SysUtils.StringReplace(
          adoImport.FieldByName('Розн#цена').AsString), ',', '.', [rfReplaceAll]),}
        adoImport.FieldByName('Розн#цена').AsString+#9+
        adoImport.FieldByName('Информация').AsString+#9+
        adoImport.FieldByName('Гарантия').AsString+#10;
      adoImport.Next;
    end;

    sToFile := TStringStream.Create(sTemp, 1252);
    sToFile.SaveToFile(ExtractFilePath(Application.ExeName)+'import.txt');

    fmMain.MyScript.SQL.Text := 'LOAD DATA INFILE '''+
     fmMain.ScreenSpecs(ExtractFilePath(Application.ExeName)+'import.txt')+''' INTO TABLE goods';
    fmMain.MyScript.Execute;

    ShowMessage('Новый список товаров был успешно импортирован!');
  finally
    sToFile.Free;
  end;
end;

I know what file MySQL creates, and need to create such file by Delphi. In MySQL help this encoding is called 'binary character set', so how can I save file in this encoding by Delphi?

Johan Johan · Accepted Answer · 2011-06-24T09:31:03

load data infile will load your Cyrillic chars just fine.

Make sure the csv file you're reading from is using unicode and displays the chars correctly.
Make sure the database fields you're reading into have the proper encoding on them. I suggest UTF8 with a Cyrillic collation.

Next make sure that your database connection (!) uses UTF8, otherwise those chars get mutated in transport.

Do that and load data infile should work.

Here's a howto reading Cyrillic data into MySQL: Howto import a UTF8 textbook (book) in MySQL table / A simple step by step guide through on howto import books in MySQL

Delphi and binary character set (for MySQL)

1 Answers