File Mover Blog

  • 05 Dec

    SQL Batch Move turn off SQL datatype guessing

    SQL Batch Move turn off SQL datatype guessing

    We received the following information from a user:

    Q: We’ve noticed an issue with the SQL Import (= SQL as Destination) – it appears that Limagito is attempting to guess the datatype, when this doesn’t match it fails to import – is there a way we can disable the datatype guessing?

    Briefly the issues encountered:

    1) In this example the import failed even though the source data was only 1 char long and table datatype nchar(1) – but that character was an N, what Limagito wanted to do was convert this to the word FALSE, I could see this after changing the table datatype to nchar(5). So it had assumed a boolean (conversely putting a Y in the field transformed it to TRUE)
    6/11/2021 3:11:52 PM Batch Move Exception: [FireDAC][Phys][ODBC]-345. Data too large for variable [TapShift]. Max len = [1], actual len = [5] Hint: set the TFDParam.Size to a greater value

    2) In this example the import failed as Limagito guessed this was a datetime field, it’s not – the table is setup to hold an nvarchar(10)
    1/12/2021 1:56:18 PM Batch Move Exception: [FireDAC][Comp][DM]-607. Bad text value [B2523:1] format for mapping item [FurnPrep]. ‘B2523:1’ is not a valid time

    A: Some background information first. In version 2019.8.17.0 we added a Batch Move option which will take the data from the source (csv) file and will insert this data into the Database (= SQL as Destination).

    In version v2021.12.5.0 we added a ‘GuessFormat Override DataType by Field Names’ option. This option gives you the possibility to override the DataTypes we guessed. To help you we also added some extra information in the (RunTime) log which shows you the guessed DataTypes.

    Limagito File Mover GuessFormat log

    Where can you find this option? Open your Destination Setup. Select an existing or open a new SQL as Destination.

    Limagito File Mover SQL as Destination

    ‘Batch Move of Data’ should be selected.

    Limagito File Mover SQL Batch Move

    Select ‘Batch Move’ Tab > ‘Text’ Tab. Here you will find the ‘GuessFormat Type Override by Field Names’ option. You can enter the field name(s) for the data types you want to override. When adding multiple field names, you need to use a , (comma) to seperate them (important).

    Enter Field Names which we should override. We are using CommaText to get or set these field names. The single comma-delimited string format is also known as the system data format (SDF). For SDF format, Field Names are separated by commas, and optionally enclosed in double quotes. Double quote marks that are part of the string are repeated to distinguish them from the quotes that surround the string. Commas that are not contained within double quote marks are delimiters. Two commas next to each other will indicate an empty string and will be ignored.

    Suppose we want to override the following Field names:

    Field1
    Fie,ld2
    Field 3
    Field”4

    Result as CommaText should be:

    Field1,”Fie,ld2″,Field 3, “Field””4”

    In the following screenshot we override the guessed DataType to STRING for the Field Names STRING1 – STRING 2 – STRING,3 – String4 found in the csv source file.

    Limagito File Mover Batch Move Setup

    #FileTransfer

    If you need any info about this ‘how-to turn off SQL datatype guessing’ question, please let us know.

    Best regards,

    Limagito Team

    By Limagito-Team SQL
  • 24 Nov

    Q&A 47: Question regarding multiple source

    Q: Question regarding multiple source. I have to delete files from multiple paths. My filter rule (folder/files) is exactly the same this is why I would like to create one Limagito rule for more than one source. In the Source window, I can weite only one source…

    Limagito File Mover Multiple WIN as Source

    Is it possible to create one limagito rule for several source ?

    A: Yes (only) with WIN as Source separate them with ;

    For example:

    Limagito File Mover Multiple WIN as Source

    #FileTransfer

    If you need any info about this question, please let us know.

    Best regards,

    Limagito Team

    By Limagito-Team WIN ,
  • 14 Nov

    I want to be able to Find in File text content. Is this possible ?

    Q: I want to be able to Find in File text content. Is this possible? For example, if the file contain a string in the file content, than do an operation. Is it possible in Limagito or we can only do something with the file name?

    In attachment, you will find folders and files example.

    –          If we find the string ST^832^ then we need to move the file in the folder « Price »

    –          If we find the string ST^810^ then we need to move the file in the folder « Invoice »

    So, the process should do this :

    –          Move the files !EDIFile.Exaple1 and !EDIFile.Example2 in folder « Price »

    –          Move the file ASSET.Example in folder « Invoice »

    A: Yes this is possible using a custom script which we create for you. I’ve added some screenshots to help you.

    • Source is a Windows folder:

    Limagito File Mover Windows Folder as Source

    • Open ‘Pascal Script’ Setup:

    Limagito File Mover Pascal Script option

    • Enable and add the following ‘On Destination’ Pascal Script:
    Var
      tmpFile: String;
      tmpList: TStringList;
    Const
      ctInvoiceSearch = 'ST^810^';
      ctPriceSearch = 'ST^832^';
      ctInvoiceSubDir = 'Invoice';
      ctPriceSubDir = 'Price';
    Begin
      psVSA := '';
      psExitCode:= 0;
      // ... add your code here
      tmpFile := psFilePath + psFileName;
      tmpList := TStringList.Create;
      Try
        Try
          tmpList.LoadFromFile(tmpFile);
          // Check Invoice
          If pos(ctInvoiceSearch, tmpList.Text) <> 0 Then
          Begin
            psVSA := ctInvoiceSubDir;
            psExitCode := 1;
            psLogWrite(1, '', 'Destination SubDir: ' + ctInvoiceSubDir + ' for ' +tmpFile);
          End;
          // Check Price
          If pos(ctPriceSearch, tmpList.Text) <> 0 Then
          Begin
            psVSA := ctPriceSubDir;
            psExitCode := 1;
            psLogWrite(1, '', 'Destination SubDir: ' + ctPriceSubDir + ' for ' + tmpFile);
          End;
          // Nothing was Found
          If psVSA = '' Then
          Begin
            psLogWrite(1, '', 'Destination SubDir Unknown for ' + tmpFile);
          End;
        Except
          psLogWrite(1, '', 'Error loading ' + tmpFile);
        End;
      Finally
        tmpList.Free;
      End;
    End.
    

    Limagito File Mover On Destination Pascal Script

    • Set Function to ‘Move Files’:

    Limagito File Mover Function Setup

    • Add ‘WIN’ as Destination

    Limagito File Mover Destination Selection

    • Select your Destination Directory:

    Limagito File Mover WIN as Destination Setup

    • Adjust the default ‘Create Subdir, option’ to:  %VSA\%SFS

    The reason you need to change this is because we’ want to use information from the previous Pascal Script to select the correct Subdirectory. In this example it will be ‘Price’ or ‘Invoice’.

    Limagito File Mover Destination Sub Directory Setup

    • RunTime Log Result:

    Limagito File Mover RunTime Log

    #FileTransfer

    If you need any info about this ‘Find in File text content’ question, please let us know.

    Best regards,

    Limagito Team

    By Limagito-Team Pascal Script WIN , ,
1 51 52 53 54 55 56 57 134
SEARCH