topic Re: Trouble Sending CSV to SFTP in Qlik Automate

Trouble Sending CSV to SFTP

DavidJ — Wed, 26 Jun 2024 20:40:05 GMT

Hi there,

I'm new to Automations and a novice Qlik developer - I'm looking to move data from a Straight Table I've built to an SFTP. I've got a test up, and can see data being moved, but I have two problems.

1. Instead of sending the full table, it's sending each row as its own file.

2. I'm using a comma to delimit things, but there's commas in the values of the table, and so it's splitting the values in an unusual way.

I'm attaching an excel workbook with three tabs. The first has an image of the Automation flow I'm using. The second has an image of the (very simple) two row table. The next two tabs are the output in the SFTP.

Does anyone have any tips here? I'm sure the solution is fairly simple but I'm missing something. Screenshots below as well. We are using Qlik Cloud

Thanks,

David

Re: Trouble Sending CSV to SFTP

Shai_E — Fri, 28 Jun 2024 13:56:01 GMT

Hi @DavidJ

I did not try to create an automation of the same nature, but i did notice a few things from what you mentioned and pictures.

For the first point, the reason why its creating multiple CSV files is because in each iteration of the get Stright table data loop you are creating a file on SFTP

Take it out of the loop so it will only create a file once.

-----------------------------

For the second point for the values of the table that have a ',' maybe use a text formula named "Remove" to remove the ',' from the values.

-----------------------------

Another thing that i could suggest for the current configuration is to maybe also save the contents of the file once, outside of the loop.

I am not quit sure if you need to save after every written line but you can try that.

-------------------------------

Finally, while it is not the same use case you have, we have a template that does a similar thing named "Copy data from a Qlik Sense straight table to an Excel sheet"

Maybe you can make use of that

Re: Trouble Sending CSV to SFTP

cotiso_hanganu — Fri, 28 Jun 2024 15:51:09 GMT

When comma is creating problems in CSV files because the content has also commas inside , try to use another delimiter, like pipe, or # or ~ .

Both when generating the CSV, but also in the read of CSV back in Qlik, if it is the case, using

(delimiter is'~')

Re: Trouble Sending CSV to SFTP

DavidJ — Tue, 10 Sep 2024 21:20:31 GMT

Thanks for your replies - both were very helpful.

I've been able to correct the output, as listed viewed using the Output block in Qlik, to include commas correctly, as well as loop correctly, to list all of the desired data in one table. The result from the block looks great (listed in the first screenshot, with some sensitive values in the data omitted).

However, now I'm having trouble with the file encoding. I've been using CSV, with a pipe delimiter, and the output block is listing everything correctly, but the file that's dropped in the SFTP either lists "ArrayArrayArrayArrayArray...", or the column headers I provide it, and that's it. It does not match the output block at all.

I'm assuming I've messed something up in the Write Line to File block, but I can't tell what it is, and it's hard to troubleshoot with the Output block telling me I've done it correctly.

Any advice? Thanks,

David