Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now

Qlik Replicate and ADSL target: How to rename output files LOAD######## to table name format (Replicate runs on Windows)

100% helpful (2/2)
cancel
Showing results for 
Search instead for 
Did you mean: 
john_wang
Support
Support

Qlik Replicate and ADSL target: How to rename output files LOAD######## to table name format (Replicate runs on Windows)

Last Update:

Aug 15, 2023 11:19:12 PM

Updated By:

john_wang

Created date:

Jul 31, 2023 11:49:36 PM

When using a Microsoft Azure ADLS as a target in a Qlik Replicate task, the Full Load data are written to CSV, TEXT, or JSON files (depending on the endpoint settings). The Full Load Files are named using incremental counters e.g. LOAD00000001.csvLOAD00000002.csv. This is the default behavior. 

In some scenarios, you may want to use the table name as the file name rather than LOAD########.

This article describes how to rename the output files from LOAD######## to <schemaName>_<tableName>__######## format while Qlik Replicate running on a Windows platform.

In this article, we will focus on cloud types of target endpoint (ADLS, S3, etc...) The example uses Microsoft Azure ADLS  which locates remote cloud storage.

 

This customization is provided as is. Qlik Support cannot provide continued support for the solution. For assistance, reach out to Professional Services.

Resolution

  1. Open and modify repctl.cfg 

    Default file location: C:\Program Files\Attunity\Replicate\bin

    Set enable_execute_user_defined_commands from false to true 

    Example: "enable_execute_user_defined_commands": true,

  2. Download and install the latest release of the Azure CLI (v2.50.0 is used in this sample)

  3. Add the az cli installation folder to Windows PATH enviornment. The default installation folder is "C:\Program Files (x86)\Microsoft SDKs\Azure\CLI2\wbin"

  4. Restart the Qlik Replicate Server service. 

  5. Create a text/batch file named myrename3_adls.bat and save it in the Qlik Replicate Server folder: C:\Program Files\Attunity\Replicate\bin

    Content of the file:
    @Echo on
    for  %%a in (%1) do set "fn=%%~na"
    echo %fn%
    set  sn=%fn:~4,8%
    echo %sn%
    az storage fs file move -p %1 -f johwg --new-path johwg/demo/%2.%3/%2_%3__%sn%.csv --account-name mydemoadlsgen2johwg --account-key Wbq5bFUohzfg2sPe7YW6azySm24xp4UdrTnuDSbacMi44fkn4UqawlwZCcn2vdlm/2u70al/vsWF+ASttoClUg==

    where  johwg is the Container Name. account-name and account-key are used to connect to ADLS storage. The values are obfuscated in the above sample.

  6. Set the Microsoft Azure ADLS Target endpoint and configure it with the following settings: 

    General
        Storage Type       :
    Azure Data Lake Storage (ADLS) Gen2
        Container             : johwg
        Target folder       : /demo

    manage endpont connections.png


    Advanced
        Post Upload Processing, choose "Run command after upload"
        Command name  : myrename3_adls.bat
        Working directory: leave blank
        Parameters             : ${FILENAME} ${TABLE_OWNER} ${TABLE_NAME}

    Manage Endpoint Connections Advanced.png

  7. Startup or Reload the Full Load ONLY task and verify the file output.

    In Azure Storage Explorer, it will typically looks like:

    Azure Storage Explorer.png

    Where SCOTT is the source table schema name, KIT is source table name, 0000000<n> is the unique incremental number.

    You can tailor the myrename3_adls.bat file for any particular needs.

Environment

Qlik Replicate  
Microsoft Azure ADLS target

 

Related Content:

Qlik Replicate and File target: How to rename output files LOAD######## to table name format on Wind...
Qlik Replicate and File target: How to rename output files LOAD######## to table name format on Linu...

Labels (1)
Comments
Leandro_Medeiros
Contributor II
Contributor II

Hi @john_wang ,

There are some way to set a XLSX file as a Source Endpoint ?

Any idea in this way will be welcome.

 

Thank you in advance

 

Rgds, Leandro Medeiros

 

john_wang
Support
Support

Hello Leandro Medeiros, @Leandro_Medeiros 

There is NO prebuilt driver for MS Excel files in current Replicate versions. The alternative options are

1. Export the MS Excel files to CSV format files (pre-process step), Qlik Replicate supports the delimited text files as source endpoint.

2. Using the generic ODBC Driver which connect to the MS Excel files. The ODBC Driver can be Microsoft Excel Driver or 3rd party ODBC Drivers. I'm pasting a driver sample below for your reference.

john_wang_0-1749815340175.png

BTW, please take note the ODBC endpoint limitations and going through Professional Services and approved by your Account Manager .

Hope this helps.

Regards,

John.

Leandro_Medeiros
Contributor II
Contributor II

Hi @john_wang ,

Noted.

Thanks a lot

Best Rgds, 

Version history
Last update:
‎2023-08-15 11:19 PM
Updated by: