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

[john_wang]

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.csv, LOAD00000002.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
   
   

   
   

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

7. Startup or Reload the Full Load ONLY task and verify the file output.
   
   In Azure Storage Explorer, it will typically looks like:
   
   
   
   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...

[Leandro_Medeiros]

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]

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.

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]

Hi @john_wang ,

Noted.

Thanks a lot

Best Rgds,