Pascal Script

  • 19 Jan

    How-To Convert XLS Files using Pascal Script

    Dear Users,

    We got a request to add an option to convert xls(x) to csv and txt. It should work without Excel being installed. All should be native code.

    This options was added as a Pascal Script function in version v2019.1.19.0.

    – Use psConvertXlsFile to convert xls(s) files to xls(x), csv, txt, …

    Function psConvertXlsFile(SrcFile, DstFile: String; Format: Integer; Delimiter: Char; Encoding: Integer; Sheet: String): Boolean;

    SrcFile = Source File (FilePath + filename)
    DstFile = Destination File (FilePath + Filename)
    Format = Destination File format

    • 0 = Automatic Automatically detect the type of the file when opening files.
    • 1 = Xls Excel 97-2000-XP-2003
    • 2 = Text Delimiter separated values. Depending on the delimiter, this can be csv, tab delimited text, etc.
    • 3 = Pxl Pocket Excel 1.0 or 2.0
    • 4 = Xlsx Excel 2007 standard file format. Note that this is *not* a macro enabled file format. If you want to save a file with macros, you need to use Xlsm instead.
    • 5 = Xlsm Excel 2007 macro enabled file format.

    Delimiter = Delimiter Char to use if Format Text ( #09 = Tab )
    Encoding = Encoding for the generated file, when writing a Text-delimited file (csv or txt). This parameter has no effect on xls/x files.

    • 0 = UTF8
    • 1 = UTF7
    • 2 = Unicode
    • 3 = Default
    • 4 = BigEndianUnicode
    • 5 = ASCII
    • 6 = ANSI

    Sheet = Name of the sheet that needs to be converted

    Pascal Script Examples:

    i.e. Convert the sheet Results of a xls(x) file to a tab delimited txt file. Destination will be a UTF8 encoded text file.

    Const
      CsvPath = 'C:\Test\OUT_XLS\';
    Begin
      psExitCode := 0;
      // ... add your code here
      If psConvertXlsFile(psFilePath + psFileName, CsvPath + ChangeFileExt(psFileName, '.txt'), 2, #09, 0, 'Results') Then psExitCode := 1;
    End.

    i.e. Convert the sheet Results of a xls(x) file to a csv delimited txt file. Destination will be a UTF8 encoded text file.
    Const

    Const
      CsvPath = 'C:\Test\OUT_XLS\';
    Begin
      psExitCode := 0;
      // ... add your code here
      If psConvertXlsFile(psFilePath + psFileName, CsvPath + ChangeFileExt(psFileName, '.csv'), 2, ',', 0, 'Results') Then psExitCode := 1;
    End.

    Don’t hesitate to contact us if you need any information about this new function.

    Regards,
    Limagito Team

  • 24 Nov

    Pascal Script, User Request Example No 1

    Dear Users,

    We often get user specific questions. Not every request goes into our file transfer tool as an option. In such cases (and if it is possible) we create a custom Pascal Script for the end user.

    Example No1:

    People are asking a lot to LimagitoX. Today, a user asked me if I can make a LimagitoX rule with a filter on the number of lines in a file.

    Example:  Transfer the file if it contains more than 300 records.  The file type is *.txt

    The rule will be:

    Transfer files with less than 300 lines in weekday.

    Transfer files with more than 300 lines on Sunday.

    No transfer on Saturday.

    Do you think you can add such a kind of filter?

     

    For this request we created the ‘On Destination’ Pascal Script below. IMPORTANT: Be sure you set the file filter to only allow *.txt files

    Var
      tmpList: TStringList;
      tmpNow: TDateTime;
    Begin
      psExitCode:= 0;
      // ... add your code here
      tmpList := TStringList.Create;
      Try
        Try
          tmpList.LoadFromFile(psFilePath + psFileName);
          tmpNow := Now;
          // Transfer files with less than 300 lines in weekday ( 1 .. 5 ).
          If (tmpList.Count < 300) And (DayOfTheWeek(tmpNow) < 6) Then Begin psLogWrite(1, '', 'Line Count of ' + psFilePath + psFileName + ' = ' + IntToStr(tmpList.Count)); psLogWrite(1, '', 'Day Of The Week is ' + IntToStr(DayOfTheWeek(tmpNow))); psExitCode := 1; End; // Transfer files with more than 300 lines on Sunday ( 7 ). If (tmpList.Count > 300) And (DayOfTheWeek(tmpNow) = 7) Then
          Begin
            psLogWrite(1, '', 'Line Count of ' + psFilePath + psFileName + ' = ' + IntToStr(tmpList.Count));
            psLogWrite(1, '', 'Day Of The Week is ' + IntToStr(DayOfTheWeek(tmpNow)));
            psExitCode := 1;
          End;
        Except
          psLogWrite(1, '', 'Exception error on loading ' + psFilePath + psFileName);
        End;
      Finally
        tmpList.free;
      End;
    End.
    By Limagito Pascal Script ,
  • 20 Mar

    Pascal Script, generate a file

    > Customer question: “I want to generate a file which is called “complete” in target folder after all files are moved to target folder”.

    In this case you’ll need to add 2 Pascal Scripts.
    First one ‘One Rule Begin’ script:

    Begin
      psExitCode:= 1;
      // … add your code here
      psResetCounter;
    End.
    Second one ‘On Rule End’ Script (you need to adjust the ctFilePath value in the script):

    Var
      tmpFile: TStringList;
    Const
      ctFilePath = 'C:\Test\Out\OutPS\';
      ctFileName = 'complete.txt';
      ctFileContent = 'dummy file';
    Begin
      psExitCode:= 1;
      // ... add your code here
      If psCounter > 0 Then
      Begin
        tmpFile := TStringList.Create;
        Try
          Try
            tmpFile.Add(ctFileContent);
            tmpFile.SaveToFile(ctFilePath + ctFileName);
            psLogWrite(1, '', 'Save complete file succes');
          Except
            psLogWrite(1, '', 'Save complete file error');
          End;
        Finally
          tmpFile.Free
        End;
      End;
    End.
    By Limagito Pascal Script ,
1 5 6 7 8 9