csv

  • 30 Dec

    Split delimited files based on duplicate column

    Split delimited files based on duplicate column

    Q: We receive | (pipe) delimited files (no double quotes):

    Rvman International Inc. – US|CONTENT + US|1310001|0000000122|10/25/2023 1:54 PM
    Rvman International Inc. – US|CONTENT + US|1310002|0000000616|0/25/2023 2:10 PM
    Rvman International Inc. – US|CONTENT + US|1310002|0000000616|0/25/2023 2:10 PM

    Is it possible to create separate files of rows with the same content in column 4?

    The filename(s) would be incremental:

    original .csv

    Rvman International Inc. – US|CONTENT + US|1310001|0000000122|10/25/2023 1:54 PM

    original1.csv

    Rvman International Inc. – US|CONTENT + US|1310002|0000000616|0/25/2023 2:10 PM
    Rvman International Inc. – US|CONTENT + US|1310002|0000000616|0/25/2023 2:10 PM

    Thanks for such a great product!

    A: Yes this is possible using our Pascal Script option

    We used a Windows folder as Source. In this folder we’ll search for .csv files.

    limagito file mover as source

    Since we are only interested in .csv files we added a filename include filter. We want to be sure we’ll only handle .csv files.

    limagito filemover filename filter

    As Destination we used our Pascal Script option. Please replace the default Destination Pascal Script.

    You can download the script: here

    The script will read the csv file with  a pipe as delimiter (this can be adjusted > ctPipe constant). We’ll check the content of column 4 which is index 3 (TStringList = zero index based).

    tmpCol4 := tmpPipe.Strings[3];

    Limagito File Mover pascal script as destination

    Output Result:

    Split delimited files based on duplicate column

    RunTime Log Result:

    limagito file mover runtime log result

    #csv #managedfiletransfer #filetransfer #filemanagement

    If you need any help with this ‘Split delimited files based on duplicate column’ question, please let us know.

    Best Regards,

    Limagito Team

  • 26 May

    Get a field value to rename a csv file

    Q: Get a field value to rename a file. I received a file with a specific filename every week which goes through various processes, after I have finished I need to send the original data with some extra data columns generated by our process back to the client using the original filename. Unfortunately, I cannot keep hold of the filename throughout the process but I can have a column in the return file which contains the original filename. Is there a way I can use Filmover to have a look at a specific column of a data file get the contents of it and use it to rename the file to that value then deleted the column that I got the information from? Bit complex but I thought I’d ask before exploring other avenues. The column that contains the original filename to be used and then deleted is called IMPNAME.

    A: Should be possible (we received an example file of the customer).

    • Please set filename filter to *.csv

    limagito file mover filename filter

    • Add ‘Pascal Script’ as Destination:

    limagito file mover pascal script as destination

    • Don’t forget to adjust the ctOutputPath Const (must end with a \ )
    Var
      iEntry, iIndex, iList: Integer;
      tmpFilename, tmpInputFile, tmpOutputFile: String;
      tmpEntryList, tmpFileList: TStringList;
    Const
      ctOutputPath = 'C:\Test\Out_Csv\';  // Must end with a \
      ctIMPNAME = 'IMPNAME';
    Begin
      psExitCode := 0;
      iIndex := -1;
      tmpInputFile := psFilePath + psFilename;
      tmpOutputFile := '';
      // ... add your code here
      tmpEntryList := TStringList.Create;
      tmpEntryList.Delimiter := ',';
      tmpEntryList.StrictDelimiter := True;
      tmpEntryList.QuoteChar := '"';
      tmpFileList := TStringList.Create;
      Try
        Try
          tmpFileList.LoadFromFile(tmpInputFile);
          tmpEntryList.DelimitedText := tmpFileList.Strings[0];
          For iEntry := 0 to (tmpEntryList.Count - 1) Do
          Begin
            If SameText(ctIMPNAME, tmpEntryList.Strings[iEntry]) Then
            Begin
              iIndex := iEntry;
              psLogWrite(1, '', 'Found ' + ctIMPNAME + ' at Column with Index ' + IntToStr(iIndex));
              Break;
            End;
          End;
          // Did we find 'IMPNAME'
          If iIndex <> -1 Then
          Begin
            tmpFileList.Strings[0] := psStringReplace(tmpFileList.Strings[0], ',"' + ctIMPNAME + '"', '');
            For iList := 1 to (tmpFileList.Count - 1) Do
            Begin
              tmpEntryList.DelimitedText := tmpFileList.Strings[iList];
              If tmpEntryList.Count > iIndex Then
              Begin
                If tmpOutputFile = '' Then
                Begin
                  tmpFilename := tmpEntryList.Strings[iIndex];
                  If tmpFilename <> '' Then
                    tmpOutputFile := ctOutputPath + tmpFilename + '.csv';
                End;  
                If tmpFilename <> '' Then
                Begin
                  tmpFileList.Strings[iList] := psStringReplace(tmpFileList.Strings[iList], ',"' + tmpFileName + '"', '');           
                  psLogWrite(1, '', 'Entry: ' + tmpOutputFile + ' : ' + tmpFileList.Strings[iList]);
                End;  
              End;
            End;
          End;
          // Save To File
          If tmpOutputFile <> '' Then
          Begin
            Try
              tmpFileList.saveToFile(tmpOutputFile);
              psExitCode := 1;
            Except
              psLogWrite(1, '', 'Error saving file: ' + tmpOutputFile);
            End;
          End;
        Except
          psLogWrite(1, '', 'Error loading file: ' + tmpInputFile);
        End;
      Finally
        tmpEntryList.Free;
        tmpFileList.Free;
      End;
    End.

    Get a field value

     

    #filetransfer #mft #filemanagement #csv

    If you need any info  about this ‘get a field value to rename a file’, please let us know.

    Best regards,

    Limagito Team

    By Limagito-Team CSV ,
  • 04 Apr

    Q&A 42: Remove quotation marks around the data in a csv file

    Q: Remove quotation marks around the data in a csv file. An Excel file is saved that has quotation marks around the data in the file. Which can be removed by opening Excel and re-saving as the CSV, but this is a manual step at this time. Would we be able to setup a Limagito rule to be able to do something similar for us?
    A: Yes this is possible using our XLS as Destination option. XLS as Destination also handles CSV files. Just be sure that the source files have .csv as extention so we know we need to handle them differently.

    Some screenshots to get you started:

    1.  Since we only want csv files, be sure the set the ‘Filename Include’ filter to *.csv in the ‘File Filter Setup’.

    Limagito File Mover filename inlcude filter

    2. We added XLS as Destination, XLS as Destination also handles CSV files. Just be sure that the source files have .csv as extention.

    Limagito File Mover XLS/CSV as Destination

    3. XLS.CSV as Destination setup. The Output File Format will be ‘Text, Delimiter separated values – with CRLF as End Of Line’.

    Limagito File Mover CSV Remove Quotation Marks

    Also ‘Text, Delimiter separated values – with LF as End Of Line’ which was added in version v2021.4.4.0

    This way you also can convert:

    • CSV with CRLF as Source to CSV with LF as end Of Line Destination
    • CSV with LF as Source to CSV with CRLF as end Of Line Destination

    Limagito File Mover CSV Remove Quotation Marks

    To remove the quotation marks you need to enable ‘Import full csv file as text’ option (Common Tab).

    Limagito File Mover import full csv file as text

    #FileTransfer

    If you need any help with this ‘Remove quotation marks’ request, please let us know.

    Happy Easter,

    Limagito Team

    By Limagito-Team Q&A XLS/CSV , , , ,
1 2 3 4
SEARCH