Importing CSV & TXT Files

Hi Adrian,


It's quite an attractive proposition to use FlexCel as the conduit for importing text files (as well as Excel).  However, I'm having some difficulty.  The TXLSFile.Open method has quite a few overloads and they look to be quite powerful.  The one I'm stumbling over is FlexCel.Core.TColumnImportTypeArray Constant.  This seems to be necessary for most overload parameter sets but I cannot find much documentation.  That I can find says it's a constant for internal use.  When I try to just open the csv file by just passing the filename I get an error to say this file type is not recognized.

So my question is, what is the easiest way to import CSV, TSV and TXT files into TXLSFile in general?

Thanks,

Steve

Hi,

2 things here:
1) While Xls.Open will actually open TSV/CSV, it isn't the most powerful way. The thing is, importing text files needs a lot of configuration, and while at the beginning we added this as overloads of Save(), soon it was clear that it didn't made sense, there were way too much parameters in the overload, and that complicated the simpler Xls.Open(xls/xlsx).

So there are 2 separate methods now: xls.Import and xls.Export to access the full functionality. This adds support for importing prn files (that is fixed width), as well as the option to import in an existing sheet (xls.Open will always create a new file, so you can't import say 3 files in 3 different sheets).

So I personally would use Xls.Import,

2) About TColumnImportType array, this is to allow you to further customize the import, for example skipping column 2, or making sure column 3 is entered as text even if it contains numbers.

If you don't need any extra customization, just pass "nil" as parameter:

For example:
 xls.Import( 'dellim.txt', 3, 2, UTF16Char(9), nil, TEncoding.Default, true); 
would import a tab delimited file.
and
 xls.Import('fixedlen.txt', 1, 1, Int32Array.Create(5, $A, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5), nil, TEncoding.Default, true);
would import a fixed-length file, with 5 characters for the first column, 10 for the second and so on.

If you want to customize the importing, just pass an array here. For example:, the following code would import all columns as text, even if they contain numbers:

var
  xls_obj: TXlsFile;
  import_types: TArray<TColumnImportType>;
begin
  xls_obj := TXlsFile.Create;
  SetLength(import_types, 1);
  import_types[0] := TColumnImportType.Text;
  xls_obj.Open(zConsts.Read14 + filename, TFileFormats.Text, ';', 1, 1, import_types);

Note that here I used a variable to define the import type, but you can always use:

xls_obj.Open(zConsts.Read14 + filename, TFileFormats.Text, ';', 1, 1, TArray<TColumnImportType>.Create(TColumnImportType.Text));

If you want to define it inline. I just find it easier with a variable here because text might get big soon.

So the rules are:
If you don't need to customize, pass nil.
If you need to customize, define an array and set it with the import type for each column, say skip first column, second is text, 3rd is general. 

If the file has more columns than the length of the array, then the last value of the array will be used for all the columns at the right of it. This is why the example just defined column 1) as text, because all the others will be as column 1 too.

Regards,
   Adrian.

AWESOME!!


Thanks,

Steve

P.S. This would make a great blog post.

I was just thinking that indeed the docs in csv are indeed too shallow, I would have swear there was a demo at least of this. I'll see to add something to the pdf user guide. And yes, a blog post might be a nice idea, it is just that I have like 3 or 4 posts pending for which I never get the time :)