External table is not in the expected format

[FinkenF]

Hi,

I'm running on 5.10.25

I have created an Excel2007 xlsx file with XLSReadWriteII5 like so:
XLS := TXLSReadWriteII5.Create(nil);
try
// somestuff
for i := 0 to Pred(FileContents.Count) do
begin
for j := 0 to Pred(LineContents.Count) do
XLS[0].AsString[j,i] := LineContents[j];
end;
XLS.Calculate;
XLS[0].CalcDimensions;
XLS.Filename := Result;
XLS.Write;
finally
FreeAndNil(XLS);
end;

If I try to read the generated file like this:
SampleDataset := TADODataSet.Create(nil);
SampleDataset.ConnectionString := GetConnectionString(Definition.SampleFile);
SampleDataset.CommandType := cmdTableDirect;
SampleDataset.CommandText := Definition.SampleSheet + '$';
SampleDataset.Open;

if will fail with "External table is not in the expected format"

If I add the following code to the file generation code it works:
ExcelObject := CreateOleObject('Excel.Application');
try
ExcelObject.Visible := False;
ExcelObject.WorkBooks.Open(Result);
ExcelObject.DisplayAlerts := False;
ExcelObject.WorkBooks[1].SaveAS(Result);
ExcelObject.WorkBooks.Close;
finally
ExcelObject.Quit;
end;

I obviously do not want to start Excel to post process the file. What am I doing wrong, or is this an issue in the XLSReadWriteII5 component?


Regards,

Frank

[larsa]

Hello

If you create an excel file with just one numeric cell, will you still receive the same error?

[FinkenF]

Hi Lars,

That also will give an error.

Here is the code:
function TInterfaceDefinition.CSV_To_XLS(const AFileName: String; const ADelimiter: Char): String;
var
FileContents: TStringList;
i, j: Integer;
LineContents: TStringList;
XLS: TXLSReadWriteII5;
begin
Result := GetWindowsTempDir + GiveGuid(True)+'.xlsx';

if (FileExists(Result)) then
DelFile(Result);

XLS := TXLSReadWriteII5.Create(nil);
try
FileContents := TStringList.Create;
LineContents := TStringList.Create;
try
FileContents.LoadFromFile(AFileName);
for i := 0 to Pred(FileContents.Count) do
begin
LineContents.Clear;
LineContents.Delimiter := ADelimiter;
LineContents.StrictDelimiter := True;
LineContents.DelimitedText := FileContents;
for j := 0 to Pred(LineContents.Count) do
XLS[0].AsString[j,i] := LineContents[j];
end;
finally
FreeAndNil(LineContents);
FreeAndNil(FileContents);
end;
XLS.Calculate;
XLS[0].CalcDimensions;
XLS.Filename := Result;
XLS.Version := Xc12Utils5.xvExcel2007;
XLS.Write;
finally
FreeAndNil(XLS);
end;
end;

The CSV file looks like
NumValue
123

This will give the External table is not in the expected format error.

If I add the following at the end of the procedure it works
ExcelObject := CreateOleObject('Excel.Application'); // is variant
try
ExcelObject.Visible := False;
ExcelObject.WorkBooks.Open(Result);
ExcelObject.DisplayAlerts := False;
ExcelObject.WorkBooks[1].SaveAS(Result);
ExcelObject.WorkBooks.Close;
finally
ExcelObject.Quit;
end;

[larsa]

Hello

Can you read a file created by Excel?

[FinkenF]

Hi Lars,

Yes, that works.

[FinkenF]

Any news on this issue?

[larsa]

Hello

Sorry, I know nothing about ADO. What are the values of ConnectionString and CommandText?

[FinkenF]

Just a plain and simple ADO connection


var
SampleDataset: TADODataset;
begin
SampleDataset := TADODataSet.Create(nil);
SampleDataset.ConnectionString := Format('Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%s;Extended Properties="Excel 12.0;HDR=No;IMEX=1;ImportMixedTypes=Text;FirstRowHasNames=0;MaxScanRows=0";Persist Security Info=False;', [Definition.SampleFile]);
SampleDataset.CommandType := cmdTableDirect;
SampleDataset.CommandText := 'Sheet1$';
SampleDataset.Open;

[FinkenF]

Hi,

any news on this issue?

[larsa]

Hello

Yes, this is fixed in update 5.20.00