XLS/CSV

  • 28 Apr

    XLS as Destination

    Dear Users,

    Version v2019.4.27.0

    On user request we’ve added an option to convert xls files. At the moment the setup is only available from the Remote Setup tool (WebRemote will follow soon).

    The request we got was to convert xls(x) to csv (txt based) without Excel being installed but also other conversions are possible (xls tot xlsx, xlsx to xls, …).

    File Format possibilities:

    • Automatic, Automatically detect the type of the file when opening files.
    • Xls, Excel 97-2000-XP-2003
    • Text, Delimiter separated values. Depending on the delimiter, this can be csv, tab delimited text, etc.
    • Pxl, Pocket Excel 1.0 or 2.0
    • 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.
    • Xlsm, Excel 2007 macro enabled file format.

    Sheet Name = Name of the sheet that needs to be converted

    Encoding = Encoding for the generated file, when writing a Text-delimited file (csv or txt). This parameter has no effect on xls/x files.

    • UTF8
    • UTF7
    • Unicode
    • Default
    • BigEndianUnicode
    • ASCII
    • ANSI

    Delimiter = Delimiter Char to use if Format Text ( #09 = Tab )

    Don’t forget to set the Filename filter to include the file types you want to convert.

    Further options like delete sheet(s), delete column(s),  renaming of cell values, .. are on our todo list.

    Regards,

    Limagito Team

    By Limagito XLS/CSV ,
  • 20 Jan

    How-To Convert XLS Files using Pascal Script, Example 1

    https://limagito.com/converting-xls-files/

    First example: convert a xls(x) file to a tabbed txt file

    1) Add the following ‘Filename Include Filter’ so we are 100% sure we’ll only handle xls(x) files

    2) Add PS (Pascal Script) as Destination

    3) Add the following Pascal Script

    Const
      CsvPath = 'C:\Test\OUT_XLS\';
    Begin
      psExitCode := 0;
      // ... add your code here
      // 2 = Text based file, #09 is tab delimited, 0 is UTF8 text encoded
      If psConvertXlsFile(psFilePath + psFileName, CsvPath + ChangeFileExt(psFileName, '.txt'), 2, #09, 0, 'Run results') Then
      Begin
        psExitCode := 1;
        psLogWrite(1, '', 'Converted ' + psFilePath + psFileName);
      End
      Else
        psLogWrite(1, '', 'Convert error ' + psFilePath + psFileName);
     
      // psConvertXlsFile(psFilePath + psFileName, CsvPath + ChangeFileExt(psFileName, '.csv'), 2, ',', 0, 'Run results');
    End.
  • 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

1 2