How Read Text File Which Was Converted From .SQL File?

Question

I am using Delphi 2005.

I have copied a SP.SQL file (which is a output file of SQL Server i.e. exported all stored procedure into one single .sql file) to SP.TXT file in Delphi using below statement:

sTempFileName := OpenDialog1.FileName;// file name with .sql as an extension
sTempFileName := StringReplace(sTempFileName, '.sql', '.txt',[rfReplaceAll, rfIgnoreCase]);
CopyFile(PChar(OpenDialog1.FileName), PChar(sTempFileName), False);

The file is converted to .txt format, but when I read it reads with below format

'ÿþS'#0'E'#0'T'#0' '#0'Q'#0'U'#0'O'#0'T'#0'E'#0'D'#0'_'#0'I'#0'D'#0'E'#0'N'#0'T'#0'I'#0'F'#0'I'#0'E'#0'R'#0' '#0'O'#0'F'#0'F'#0' '#0#0

I would like to read in plain text format. I tried using UTF8Encode(sText) function (not sure this is the right function to use) but same result.

Am I missing anything?

The file is saved in unicode format, you can instruct SQL management studio to save file in ANSI format... — whosrdaddy, Jun 16 '14 at 10:38
http://stackoverflow.com/questions/15158621/how-to-handle-utf-8-and-ansi-conversion-before-delphi-2009 — bummi, Jun 16 '14 at 10:42

score 2 · Accepted Answer · answered Jun 16 '14 at 10:46

2

This has nothing to do with Delphi. By default SQL Server management studio saves .SQL files in unicode format. You can instruct the application to save with a different encoding.

In SQL server 2012, look under File and then Advanced Save Options:

enter image description here

Choose whatever (non-unicode) encoding you like. The reason that Delphi 2005 can't read the file properly is that doesn't support unicode nativly.

answered Jun 16 '14 at 10:46

whosrdaddy

11,720
4
50
99

@ whosrdaddy : Yes, that worked perfectly. But if such file we thought of reading in Delphi, then how do we do it? Thank You. – Vishal Tiwari Jun 16 '14 at 11:37
Check Bummi's answer :) – whosrdaddy Jun 16 '14 at 12:32

score 1 · Answer 2 · answered Jun 16 '14 at 12:11

For the encoding you are using Big Endian (UTF-16BE) the following function could by used for nonunicode delphi versions, you might enhance it for additional encodings:

Function GetFileAsString(const fn:String):String;
 var
 ss:AnsiString;
 ws: WideString;
 fs:TFileStream;
 len:Cardinal;
begin
  fs := TFileStream.Create(fn,fmopenRead or fmShareDenyNone);
  try
  SetLength(ss,2);
  fs.Read(ss[1],2);
  if ss=#$FF#$FE then // test encoding
    begin
      Setlength(ws,(fs.Size) div 2 - 1);
      len :=  fs.Size-2;
      fs.Read(ws[1],len);
      Result := UTF8Decode(ws);
    end;
  finally
   fs.Free;
  end;
end;

Thank You. It would help me alot. – Vishal Tiwari Jun 17 '14 at 06:44 — Vishal Tiwari, Jun 17 '14 at 06:44

How Read Text File Which Was Converted From .SQL File?

2 Answers2