Import error with CSV files

Hi Adrian,


when I import the csv file included in  http://www.synbiosys.alterra.nl/downloads/BD_Corela_header.7z some of numbers are not important correctly. In the included Excel file these numbers are marked with yellow color.
For example '-0,865944' is translated to '-865944'. The decimal separator is ignored.

My code to import to csv file is as follows:

 SetLength(columnImportTypes, 1000);
 for col := 0 to 999 do
 columnImportTypes[0] := TColumnImportType.Text;
 xls.Open(files, TFileFormats.Text, ';', 1, 1, columnImportTypes, nil, TEncoding.ANSI, True);

Is there something I have overlooked?

Stephan

BTW, it was a pleasure to talk to in Dusseldorf at the TMS workshop.
 



Hi,


This seems like a problem with Format settings: Your app is configured to use "." as decimal separator, but the csv uses "," as decimal separator. So in the number -0,865944, the "," is interpreted as thousands separator, which can actually be ignored.

To fix it you can either set your full app to use a locale that uses "," as decimal separator, or just set FlexCel to work that way.

To set the whole app to use a say French locale, you would write this line somewhere in the startup of your app:

    FormatSettings := TFormatSettings.Create('fr-FR');


To only set FlexCel to use a French locale (but globally for all FlexCel options), you would also set in your app startup:


TFlexCelFormatSettings.SetGlobalFormat('fr-FR', TFormatSettings.Create('fr-FR'));


But normally, what you would want to do is to change the settings only for the current thread while you are loading this file. So if a different different thread is reading a different CSV file with a different locale, changing this locale won't affect the other thread.
For that you could use this code:



uses
  System.SysUtils,
  VCL.FlexCel.Core,
  FlexCel.XlsAdapter;


var
  columnImportTypes: TArray<TColumnImportType>;
  col: integer;
  xls: TXlsFile;
  OldFmt: TFlexCelFormatSettings;
begin
  xls:= TXlsFile.Create(true);
  try
    OldFmt := TFlexCelFormatSettings.PushThreadFormat('fr-FR', TFormatSettings.Create('fr-FR'));
    try
      SetLength(columnImportTypes, 1000);
      for col := 0 to 999 do
        columnImportTypes[0] := TColumnImportType.Text;
      xls.Open('C:\Users\adrian\Documents\BD_Corela_header\BD_Corela_header.csv', TFileFormats.Text, ';', 1, 1, columnImportTypes, nil, TEncoding.ANSI, True);
    finally
      TFlexCelFormatSettings.PopThreadFormat(OldFmt);
    end;


    xls.Save('r:\test.xlsx');
  finally
    xls.Free;
  end;


As always, if you haven't read it, I recommend that you read this tip:

http://www.tmssoftware.biz/flexcel/doc/vcl/tips/understanding-csv-files.html

The code example shows how to set the locale too, as in the code I attached here. There is also this tip about locales: http://www.tmssoftware.biz/flexcel/doc/vcl/tips/how-to-change-the-flexcel-locale.html

But basically they tell you what I've said here.

Ps1: There seems to be an error in the code you sent:


for col := 0 to 999 do
	 columnImportTypes[0] := TColumnImportType.Text;


This is setting a thousand times the column 0 to be text. But the others are not (and that's why you actually got those numbers imported wrong, and not as text).

Ps2: It was also a pleasure to see you all in the TMS day :) We should be doing this more frequently!

Hi Adrian,


thanks for this course in dealing with local settings. However, the problem is just a stupid mistake in my code. Thanks for pointing this out to me. Now the results is as expected.