String with a prefix "000".The string is truncated

[Philippe_Regnier]

Hello,

I have a csv file (separator ";"). I insert the value "0001234".

I can not read the string "0001234", the string is truncated, and the result is "1234".

How to read "0001234"?

Here is a sample Delphi source code:

Uses FlexCel.XlsAdapter, VCL.FlexCel.Core;

procedure TForm1.Button1Click(Sender: TObject);

var

  ExcelFile : TExcelFile;

  CellValue : TCellValue;

begin

  ExcelFile := TXlsFile.Create(true);

  ExcelFile.Open(Edit1.Text, TFileFormats.Text, ';', 1, 1, nil);

  CellValue := ExcelFile.GetCellValue(1, 1);

  Showmessage( varToStr( ExcelFile.GetCellValue(1, 1) ) );

  Showmessage( ExcelFile.GetStringFromCell(1, 1) );

  Showmessage( CellValue.ToString );

end;

[adrian]

Hi,

By default, FlexCel tries to convert the strings to numbers (or dates) when importing the file. This is normally what you want: If you have a field with the string "1" you probably want to import this as the number 1, not the string "1".

Now, if you want to import some columns as strings and not convert them, you can do it by setting the columnImportFormat. From the help in xls.Open(...):

 Imagine you have a file with 20 columns, and column 2 has numbers you want to be imported as text (like phone numbers), and you don't want to import column 10. You can use the following code to do it:
 var
   ColTypes: TColumnImportTypeArray;
   Begin
      SetLength(ColTypes, 10); //You just need to define 10 items, all other columns after 10 will be imported with default formatting.
   ColTypes[1] := TColumnImportType.Text; //Import whatever is in column 2 as text.
   ColTypes[9] := TColumnImportType.Skip; //don't import column 10.

   xls.Open('csv.csv', TFileFormats.Text, ',', 1,1,ColTypes);

In your case, if you want to import say column 1 as text even if it is a number, you can do:

procedure TForm1.Button1Click(Sender: TObject);

var

  ExcelFile : TExcelFile;

  CellValue : TCellValue;

  ColTypes: TColumnImportTypeArray;

begin

  ExcelFile := TXlsFile.Create(true);

  SetLength(ColTypes, 1);

  ColTypes[0] := TColumnImportType.Text;

  ExcelFile.Open(Edit1.Text, TFileFormats.Text, ';', 1, 1, ColTypes);

If you want to import all columns, just make a big enoug ColumnImportType array and fill it with TColumnImportType.Text.

Ps: A little unrelated, but while this works:

Showmessage( varToStr( ExcelFile.GetCellValue(1, 1) ) );

It is better to do this:

 Showmessage( CellValue.ToString );

A TCellValue will convert to a variant automatically, but internally we don't use variants at all, so you are doing extra conversions in the first call (converting the TCellValue to a variant then the variant to a string). In the second call, we just convert the TCellValue to a string directly.

[Philippe_Regnier]

Thank you for your explanation.