How to get number of files in SFTP listing?
AnsweredI have a simple SFTP connector and perform a List Directory command. I get back an array of file names as json:
["File A", "File B"]
what is the most efficient way to count the number of files? I tried adding an Array to CSV command then a Filter Rows command to include everything as this produces a Record Count output, which I can then use. However this failed with some nondescript key error in position 5 on the Array to CSV command:
🔌 JSON1.1.1
⚙️ Array to CSV Configuration:
Columns: [{"name":"file_name","path":"."}]
Delimiter: comma
Array filter:
JSON Data: ["/FILEA-2021.csv","/FILEB-6361152.csv"]
JSON Text (DEPRECATED):
Multi-value delimiter: ,
Preview result: true
Path to root array:
Command description: Description
ERROR: There was a problem converting your JSON to CSV: Key length is zero at 5
Result: Error: Key length is zero at 5
My inputs are:
VARIABLE |
VALUE |
|---|---|
Columns |
[{"name":"file_name","path":"."}]
|
Delimiter |
comma |
Filter |
|
JSON Data |
["/WORKIVA_LEDGER-2021.csv","/WORKIVA_LEDGER-6361152.csv"] |
Input Text (Deprecated) |
|
Multi-value Delimiter |
, |
Preview Result |
|
Path to root |
-
The output from the List Directory command will need to be modified a bit, otherwise the error you ran into will be hit. First, the List Directory output is almost in a useable CSV format expect for the leading and trailing brackets. After removing the brackets, the data can be queried in Advanced Query. So the steps are to use Create File with the output from List Directory, then two instances of Find and Replace to remove the opening and closing brackets from the Create File output. The resulting file (which should now contain "/FILEA-2021.csv","/FILEB-6361152.csv" given your example as original input) can then be passed into the Advanced Query command as mentioned above to get the row count.
1
Hi Waldo, since you have transformed the data into a CSV, you could use the Advance Query command with the PRAGMA statement to get the table_info(). This will include the record count of the data.
0
Ok and if I do not convert it? How do I ask a JSON array its length? That would be even simpler.
0
I don't think it would be simpler in Chains. I imagine you would need to do something a little more tricky like iterate over it to capture a count or split it into chunks. The simplest way that I am aware of is to transform it to a CSV and then use Advanced Query.
0
Thanks Jeff. Last question - why did my approach fail? The Array to CSV failed as per above so I cannot implement your solution without moving beyond it.
0
I am not convinced that would work. I ended up doing below which does. Unless you implied I do some kind of transpose query in Advance Query as I would need to then count the number of columns as you have not transposed the data. My solution based on yours below for anyone needing to do something similar:
0
Historically, I haven't ran into a case requiring a transpose. But glad you were able to get a working solution.
0
Hey I know this is 2 years old, but the "key error in position 5" error is caused by using "." as the path in an array of flat objects. To convert the List Directory output array into a csv, you want to set the path to null/blank.
0Please sign in to leave a comment.
Comments
8 comments