open csv / text file

FLX TMS FlexCel for VCL & FMX
1

Hi,

While trying to xf.open(filename)  file of text / csv format, the error appears - 
"The file is not on any on the formats supported by FlexCel (Excel 5 or newer, pxl, text or xlsx)"
What is a root cause, and how to resolve?
2

Hi,


To open a csv/ text file, you need to specify that it is a text file, and the delimiter (is it ",", ";"  or maybe tab delimited?), and if possible the encoding of the file (since it might not be possible to guess it)

Try with:

there are more options available in xls.Open (as said, you might want to specify the encoding). There are even more options if you use xls.Import, which will also let you open text files with fixed length columns instead of delimited (prn file in Excel)

A final note. If the extension of your files is ".csv" or ".txt",then you can use 

instead of TFileFormats.Text.  When using automatic, it will open also xls/x files with the same command. But the file needs the correct extension for the text files. (xls files have a signature, so we always know the right file format, even if you try to open an xlsx file renamed as xls)
3

Adrian,

Thanks for prompt reply.
Actually. I saw this extended format. but any time I rtied to use it, a compilation error was raised, similar to "No overloaded procedure with such parameters exists"... smth like this. Can supply you with more details tomorrow arriving at workplace.
4

Sometimes it can be difficult to guess what to pass in TColumnImportType parameter, maybe this was your problem? I passed nil here but you can pass a TColumnImportType object too. By the way, sorry but I just realized I posted the syntax for FlexCel.NET and this was about FlexCel VCL. The correct syntax for delphi should be:

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

Try it like this, it should work (it does here)
5
With "TFileFormats.Automatic" error still the same.
Last example (with TFileFormats.Text) caused "List index out of bounds (0)"
6
About tfileformats.automatic I've just checked and indeed this is only for saving, for opening you need to explicitly specify text. Sorry for the mistake. The reasoning on not opening any file automatically as csv is that any file, say a png or an avi file, could be considered a csv file, and it would open, but it would contain garbage. That's why you need to specifically say that you are trying to open a text file. We could look at the extension as we do on saving, but really you should be specifying csv specifically with the separator and encoding anyway.

About the index of out bounds error, can you send me the file you are trying to open to adrian@tmssoftware.com ?   The index of out bounds might be causes by some issue in the file itself. CSV importing has been tested a lot to be the same as the CSV importing in Excel, but there can be always cases that we don't recognize. Sadly CSV isn't a standard documented anywhere, so there are many different implementations. We tried to do one that reproduces what Excel itself imports or exports, reverse engineering what Excel does. But as said, there might be cases we don't understand.

I've tried the following code here:
  sw: TStreamWriter;
begin
  sw := TStreamWriter.Create('r:\test.csv');
  sw.writeline('1,2,5,10,hello');
  sw.writeline('"hello, world", 4, 3, 2, 1');
  sw.Free;
  xls := TXlsFile.Create;
  xls.Open('r:\test.csv', TFileFormats.Text, ',', 1, 1, nil);
  ShowMessage(xls.GetCellValue(2, 1).ToString);
  xls.Free;

And it works as expected. As said, there could be cases where some stuff isn't recongnized, but at least we should show a nicer exception message.
7

Adrian,

I sent a sample of CSV to your mail. Probably you right, and the main problem in the file format. 
8

Hi,

Thanks for the file. 
The bad news is that this indeed a bug, and the bug is actually in Delphi's TStreamReader, so we can't really fix it. We are actually aware of the bug, that while really rare might happen, and so we use our own streamreader for reading xlsx files, but for csv we can't use an internal streamreader because some internal reasons, even when I'll review if it is possible. We were also waiting to see if the bug is fixed, but I tried it with XE5 and the bug is still there.

The good news is that in this case, you can still read the file, since we were reading it wrong anyway. The file is in a cyrillic character set, so if you try to open it with the default encoding (utf8) you'll get garbage anyway (and that's what I get if I open the file with my Excel version here. Probably a russian Excel will open this file fine, but not any other localized Excel).

Try with the following code, it should open the file and also get the right text, not garbage:

var
  enc: TEncoding;
begin
  xls := TXlsFile.Create;
  try
    enc := TEncoding.GetEncoding(1251);
    try
      xls.Open('e:\downloads\pay90_0.csv', TFileFormats.Text, ';', 1, 1, nil, nil, enc, false);
      ShowMessage(xls.GetCellValue(2, 1).ToString);
    finally
      enc.Free;
    end;
  finally
    xls.Free;
  end;

As said, this will actually mask the bug not fix it: The bug in streamreader happens in very little corner cases, which your file happened to trigger, if trying to read it as UTF8. When trying to read it as cyrillic, it won't.

Another thing, I assumed code 1251: http://en.wikipedia.org/wiki/Windows-1251 but I don't really know which encoding this file really is. While probably it is 1251, it might be KOI8R or something else. You need to know the codepage for the computer where this file was generated in order to do a correct import.
9
Adrian,
Thanks for your kind support, the case is closed.