OleDBConn not taking the Latest Excel File

Question

I am downloading the Excel in XLS format which i get from a Bank link and save to a particular path in the System. From there i am importing the XLS to the my Table by using OleDBConnection Automation.

The main two functions are as given below.

SaveExcelFile()
MYURL := 'https://www.commerzbank.hu/portal/media/corporatebanking/auslandsseiten/ungarn-informationen/englisch-/kursinformationen/deviza_noon.xls';

IF ISCLEAR(WinHTTP) THEN
  CREATE(WinHTTP,TRUE,TRUE);

WinHTTP.open('GET',MYURL,FALSE);
WinHTTP.send('');

IF WinHTTP.status <> 200 THEN
  ERROR(Text023,WinHTTP.status,WinHTTP.statusText);

FileName := 'file.xls';
IF ISCLEAR(ADOStream) THEN
  CREATE(ADOStream,TRUE,TRUE);

IF ADOStream.State = 1 THEN
  ADOStream.Close;

ADOStream.Type := 1; // adVarBinary
ADOStream.Open;
ADOStream.Write(WinHTTP.responseBody);

IF ISCLEAR(FileSystem) THEN
  CREATE(FileSystem,TRUE,TRUE);

//FileFolder := FileSystem.GetSpecialFolder(2);
//FilePath := FileFolder.Path;
FilePath := 'C:\Temp';
ADOStream.SaveToFile(FilePath + '\' + FileName,2);
FileName := FilePath + '\' + FileName;
//MESSAGE('%1',FileName);
ADOStream.Close;
ImportExcel(FileName);



ImportExcel(FileName : Text)
DateField :=TRUE;
OleDBConn := OleDBConn.OleDbConnection('Provider=Microsoft.ACE.OLEDB.12.0;Data Source='+ FileName + ';Extended Properties="Excel 12.0;HDR=No;IMEX=1"');
SheetName := 'Deviza';
OleDBConn.Open();

OleDBCmd := OleDBCmd.OleDbCommand('select * from [' + SheetName + '$A4:D25]',OleDBConn);
OleDBCmd.Parameters.Clear();
OleDBReader := OleDBCmd.ExecuteReader();

"UploadCurrencyExch.Rate2".SETCURRENTKEY("UploadCurrencyExch.Rate2"."Entry No.");
IF "UploadCurrencyExch.Rate2".FINDLAST THEN
  "EntryNo." := "UploadCurrencyExch.Rate2"."Entry No.";

WHILE OleDBReader.Read() DO BEGIN
  IF FORMAT(OleDBReader.GetValue(0)) <> 'null' THEN BEGIN
    "UploadCurrencyExch.Rate2".INIT;
    IF DateField = TRUE THEN BEGIN
      EVALUATE(UploadDate,FORMAT(OleDBReader.GetValue(0)));
      DetectDuplicateCurrencyUpload(UploadDate);
      EVALUATE("UploadCurrencyExch.Rate2"."Upload Date",FORMAT(OleDBReader.GetValue(0)));
      //"UploadCurrencyExch.Rate2"."Upload Date" := TODAY;
      "UploadCurrencyExch.Rate2".Status := "UploadCurrencyExch.Rate2".Status::Open;
      "UploadCurrencyExch.Rate2"."Exchange Status" := "UploadCurrencyExch.Rate2"."Exchange Status"::Noon;
      "UploadCurrencyExch.Rate2"."Entry No." := "EntryNo."+1;
      CLEAR("UploadCurrencyExch.Rate2"."Currency Code");
      "EntryNo." := "EntryNo."+1;
    END ELSE BEGIN
      EVALUATE("UploadCurrencyExch.Rate2"."Upload Date",UploadDate);
      //"UploadCurrencyExch.Rate2"."Upload Date" := TODAY;
      IF FORMAT(OleDBReader.GetValue(0)) <> 'null' THEN
        "UploadCurrencyExch.Rate2"."Currency Code" := FORMAT((OleDBReader.GetValue(0)));
      IF FORMAT(OleDBReader.GetValue(1)) <> 'null' THEN
        EVALUATE("UploadCurrencyExch.Rate2"."Buying Rate",FORMAT(OleDBReader.GetValue(1)));
      IF FORMAT(OleDBReader.GetValue(2)) <> 'null' THEN
        EVALUATE("UploadCurrencyExch.Rate2"."Agent Rate",FORMAT(OleDBReader.GetValue(2)));
      IF FORMAT(OleDBReader.GetValue(3)) <> 'null' THEN
        EVALUATE("UploadCurrencyExch.Rate2"."Sales Rate",FORMAT(OleDBReader.GetValue(3)));
      "UploadCurrencyExch.Rate2".Status := "UploadCurrencyExch.Rate2".Status::Open;
      "UploadCurrencyExch.Rate2"."Exchange Status" := "UploadCurrencyExch.Rate2"."Exchange Status"::Noon;
      "UploadCurrencyExch.Rate2"."Entry No." := "EntryNo."+1;
      "EntryNo." := "EntryNo."+1;
    END;
    "UploadCurrencyExch.Rate2".INSERT;
    DateField := FALSE;
   END;
END;
OleDBReader.Close();
OleDBCmd.Parameters.Clear();
OleDBConn.Close();
OleDBConn.ReleaseObjectPool();

Every thing is working fine. But the issue i am having is the Excel Data inserted is not latest. its always taken from the previous imported Excel file.

The thing that confusing me is that the Old Excel fine is not at all available in the System since its replaced by the New one. I have confirmed this by opening the XLS file manually.

The Excel file is not taken from any other place, i have confirmed it by trying to execute the function by keeping the Excel file opened, which will through me an error.

I think the Data is not getting cleared from the Cache. Not sure how to clear it. Please help me guys.

OleDBConn not taking the Latest Excel File

0 Answers0