XLS/CSV

  • 08 Sep

    How to use a wildcard in the excel sheet name

    Q: How to use a wildcard in the excel sheet name. I am using the below to split and convert the tabs out of a spreadsheet but every now and again the client gets the sheet name slightly wrong.

    wildcard in the excel sheet name

    Can you use a wildcard in the sheet name option?

    Example:

    2 Sticker*

    A: Added on request and available in v2023.8.12.0

    Example where we used: Page*

    wildcard in the excel sheet name

    If you need any info about this ‘wildcard in the excel sheet name’ request, please let us know.

    #excel #filetransfer #filemanagement

    Best regards,

    Limagito Team

    By Limagito-Team XLS/CSV , , ,
  • 04 Aug

    How to convert xls to tab delimited txt files

    Q: We need to convert xls to tab delimited txt files. The resulting tab delimited txt files should be merged without the header data of the xls file. There will be always three files that need to be merged.

    Example:

    • Accounts_CO-en-us_08042023_009004.xlsx
    • Accounts_NE-en-us_08042023_009007.xlsx
    • Accounts_WY-en-us_08042023_009012.xlsx

    Result should be a txt delimited file:

    • Accounts_WY-en-us_08042023.txt

    A: This is possible using the following setup:

    • Source can be any type. We used a Windows folder as Source

    limagito filemover windows folder as source

    convert xls to tab delimited source files

    • We added *.xlsx as include filename filter:

    limagito file mover include filename filter

    • In this example we used two Destinations
      • First Destination (ID1) is our XLS option which will convert the xlsx file to a tab (#09) delimited txt file
      • Second Destination (ID2) is our Script option and this will:
        • check if the files we need are available
          • we’ll use a filter based on the current date (filter format MMDDYYYY)
          • If we find three files then we are ready to merge, otherwise > error
        • when available will
          • load and merge them, without the header, together
          • save the resulting merged txt file

    limagito file mover destination options

    • XLS Destination Setup:

    Important: The “Destination Directory” in the XLS setup will be used in the second destination (Pascal Script, const ctSearchPath). They both need to be the same., because the script needs to search this “Destination Directory” for converted files.

    limagito file mover xls as destination setup

    limagito file mover xls as destination setup

    • Pascal Script Destination Setup (you’ll need to adjust the ctSearchPath and ctMergedPath):
    Var
      iFilesList, tmpPos: integer;
      tmpFilter, tmpFile, tmpData, tmpOutputName: String;
      tmpFilesList, tmpList: TStringList;  
    Const 
      ctDateMask = 'MMDDYYYY';
      ctSearchPath = 'C:\Test\PVBank\Output\';
      ctMergedPath = 'C:\Test\PVBank\Merged\';  
    Begin
      // Create Var
      tmpList := TStringList.Create;
      Try
        // Init Var
        psExitCode:= -1;
        tmpData := '';
        // Accounts_CO-en-us_08042023_009004
        tmpFilter := FormatDateTime(ctDateMask, Now);
        tmpFilesList := psListFilesEx(ctSearchPath, '*' + tmpFilter + '*', False);
        // Check Qty Files
        If tmpFilesList.Count = 3 Then
        Begin
          psLogWrite(1, '', 'We have found The necessary files using filter: ' + tmpFilter);
          For iFilesList := 0 to (tmpFilesList.Count - 1) Do
          Begin
            Try
              tmpList.Clear;
              tmpFile := tmpFilesList.Strings[iFilesList];
              Try
                tmpList.LoadFromFile(tmpFile);
              Except
                psLogWrite(1, '', 'Exception loading txt file: ' + tmpFile);
              End;
              If tmpList.Count > 0 Then
              Begin
                tmpList.Delete(0);
                tmpData := tmpData + tmpList.Text;
                If iFilesList = (tmpFilesList.Count - 1) Then
                Begin
                  Try
                    tmpList.Text := tmpData;
                    // Save Merged Result
                    tmpFile := ExtractFilename(tmpFile);
                    tmpPos := Pos(tmpFilter, tmpFile);
                    If tmpPos > 1 Then
                      tmpOutputName := Copy(tmpFile, 1, (tmpPos-1) + length('MMDDYYYY')) + '.txt' 
                    Else
                      tmpOutputName := tmpFile;
                    tmpList.SaveToFile(ctMergedPath + tmpOutputName);
                    psExitCode := 1;
                    psLogWrite(1, '', 'Successfully saved merged data to: ' + ctMergedPath + tmpOutputName);
                  Except
                    psExitCode := 0;
                    psLogWrite(1, '', 'Error saving merged data to: ' + ctMergedPath + tmpOutputName);
                    Break;  
                  End;
                End; 
              End
              Else
              Begin
                psExitCode := 0;
                psLogWrite(1, '', 'Error, found empty txt file: ' + tmpFile);
                Break;  
              End; 
            Except
              Break;
            End;
          End;
        End
        Else
        Begin
          psLogWrite(1, '', 'We have not found The necessary files using filter: ' + tmpFilter);  
        End;
      Finally  
        // Free Var
        tmpFilesList.Free;
        tmpList.Free;
      End;  
    End.

    limagito file mover pascal script as destination setup

    • XLS as Destination conversion to txt files:

    limagito convert xls to tab delimited output

    • Pascal Script as Destination merged data as text file:

    limagito file mover convert xls to tab delimited merged

    • Content of the merged tab delimited txt files:

    limagito file mover convert xls to tab delimited content

    If you need any info about this ‘convert xls to tab delimited’ question, please let us know.

    Best regards,

    Limagito Team

  • 31 May

    How to convert from XML to JSON format

    Q: We receive some files in XML format. Could you please confirm, if Limagito tool can convert XML files to JSON format? If yes, Do we have any limitations while converting the file type? Please let us know if you need any additional information.

    A: We added an option in version v2023.5.31.0 to achieve this.

    • Our Source will be a Windows folder

    limagito file mover Windows folder as source

    • We added the following include Filename filter because we only want to scan for *.xml files:

    limagito file mover filename filter

    • We’ll need a Pascal Script to convert the xml file to a Json file so we added Pascal Script as Destination:

    limagito file mover destination setup

    • We noticed that the xml we received from the customer was not a valid xml so we added some extra code (line 18) in the script to adjust this

    limagito filemover xml to json

    • Pascal Script we used (don’t forget to adjust the ctOutputPath const):
    Var
     tmpFileInput, tmpFileOutput, tmpResult: String;
     tmpXmlText: String;
     tmpList: TStringList;
    Const
      ctOutputPath = 'C:\Test\Out_JSon\'; // Must end with a \ 
    Begin
      // Init Var
      tmpFileInput := psFilePath + psFileName;
      tmpFileOutput := ctOutputPath + ChangeFileExt(psFileName, '.Json');
      psExitCode:= 0;
      // Read non valid Xml file and adjust before creating Json File
      tmpList := TStringList.Create;
      Try
        Try
          tmpList.LoadFromFile(tmpFileInput);
          tmpXmlText := tmpList.Text;
          tmpXmlText := psStringReplace(tmpXmlText, '-<', '<'); // Adjust Non Valid Xml
          // psLogWrite(1, '', 'StringReplace Xml Result: ' + tmpXmlText); // Debug
          tmpResult := psXmlTextToJSonFile(tmpXmlText, tmpFileOutput, True, True, True, True);
          If tmpResult = '' Then
          Begin
            psLogWrite(1, '', 'Saved Json Data to ' + tmpFileOutput);      
            psExitCode := 1;
          End  
          Else
            psLogWrite(1, '', tmpResult);
        Except
          psLogWrite(1, '', 'Load Xml from ' + tmpFileInput + ' Exception');
        End;
      Finally
        tmpList.Free;
      End;
    End.

    limagito file mover convert from xml to json

    In the example above we used The following function:

    Function psXmlTextToJSONFile(Const aXmlText, aJsonFile: String; Const aXmlParse, aXmlPack, aJsonIndent, aJsonEscape: Boolean): String;

    The psXML functions have a aXmlPack option that determines the format returned, Pack = False returns all tags as values with multiple nested levels of objects, Pack = True is simpler and easier to process but does lose some tag names.

    aXmlPack = False : all XML content is converted into Json values, each level with two or three names: #attributes is an optional object from opening tag, #Name
    is the string from <Tag>, and #children which is an array of other Json objects and arrays nested within the tag.

    aXmlPack = True : tags are converted to Names and their content to Values, or nested objects. For a single record, a single object is returned with the tag names
    lost, for multiple records (ie tags of the same name) an array is created with each record being an object.

     

    • RunTime Log Result:

    limagito file mover xml to json runtime log result

    • Json File Result:

     

    limagito filemover xml to json

    • Another available Pascal Script function that can be used only when source xml is valid (direct file to file conversion):

    Function psXmlFileToJSONFile(Const aXmlFile, aJsonFile: String; Const aXmlPack, aJsonIndent, aJsonEscape: Boolean): String;

    • Pascal Script will result in:
    Var
     tmpFileInput, tmpFileOutput, tmpResult: String;
    Const
      ctOutputPath = 'C:\Test\Out_JSon\'; // Must end with a \ 
    Begin
      // Init Var
      tmpFileInput := psFilePath + psFileName;
      tmpFileOutput := ctOutputPath + ChangeFileExt(psFileName, '.Json');
      psExitCode:= 0;
      // Xml File To JSon File
      tmpResult := psXmlFileToJSonFile(tmpFileInput, tmpFileOutput, True, True, True);
      If tmpResult = '' Then
      Begin
        psLogWrite(1, '', 'Saved Json Data to ' + tmpFileOutput);  
        psExitCode := 1;
      End  
      Else
        psLogWrite(1, '', tmpResult);
    End.
    

    limagito file mover convert from xml to json

    #filetransfer #mft #filemanagement #xml #json

    If you need any info  about this ‘Convert from XML to JSON format’ request, please let us know.

    Best regards,

    Limagito Team

1 2 3 4
SEARCH