Extract path of each files from a ZIP and create table in a Database

Question

Hello,

I am working on an automation to create tables in a database from ZIP Files.

Each files will be in a different table.

So, I would like to extract the path of the Zip files, for example with the following directory : C:\user\Mydata\Sales.zip

In this Zip, I have multiple files with the same extension (file1.csv, file2.csv, file3.csv ...)

The goal is to extract the path to these files (without unzipping the folder) and then create the table in a database.

I manage to get the name of the compressed folder using the directory tool however I don't know how to get the files contained in it.

After this, I think I have to use a macro to create the tables but I think I am going to have trouble to do that.

If I wasn't clear, do not hesitate to ask more clarification.

Thank you.

MattBSlalom · Answer

The Run Command tool isn't able to actually read the results back into Alteryx from the command prompt window.  The output anchor on the Run Command tool is actually used to pull results from a file after executing the command.  So, we need your powershell call to write the results to a file, then configure the Run Command to read that powershell result file to make use of those compressed filenames.  Since you're using a PowerShell call, we can use a script file instead of a batch file.  If we only care about the file names in the zip and not any stats about them, we can use the "Z1" parameter instead of the "l" parameter to list the files which will save a bit of parsing of the result file after the fact.

Update the command being written to the file to be something like this:

unzip -Z1 "C:\bryan\Market\product.zip" | Out-File -FilePath "C:\bryan\Market\productziplist.txt"

Then change the file extension in the Write Source of your Run Command to be ".ps1" instead of ".bat".

Next configure your Run Command "Command" to be "powershell" (without quotes) and the Command Arguments to be the path of the new ".ps1" file from the "Write Source".

Finally, configure the Read Results section of the Run Command tool to point to the "C:\bryan\Market\productziplist.txt" file (the Out-File file path from the powershell command above).

Sample of the Run Command configuration:

ByranCarter12 · Answer

@MattBSlalom  Thank you for you solution.

So i'am trying to use the Run command's method to extract list of files from a ZIP.

I can't use 7zip for my case, because of admin right. So I've use this command :

unzip -l "\C\bryan\Market\product.zip"

For executing as .bat I've added this

powershell -command unzip -l "\C\bryan\Market\product.zip"

So when I execute the .bat not in Alteryx, It's seem to work, I have a list of files with info.

Length Date Time Name
--------- ---------- ----- ----
100200 19/05/2018 16:57 Product 1.csv
1459244 10/09/2019 13:55 Product 2.csv
4859954 11/08/202012:14 Product 3.csv
599393 12/02/202014:29 Product A.csv
567474 15/02/2021 15:29 Product B.csv
--------- -------
7586265 5 files

But when I run the command in alteryx, i don't have result.

I don't know what I'm missing and I am not a specialist for using the command tool in alteryx.

Thank you for helping me.

MattBSlalom · Answer

This post has a good write up for using Python to get a file list from a zip file:  https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Reading-Multiple-Files-Of-Your-Choice-from-ZIP-files-with-ZIP/td-p/444413

Instead of that method, I decided to use a Run Command tool to call the 7zip utility to generate the list of compressed files from the Input zip file path.  The 7zip result file then needed some parsing to get to the content we actually care about (the compressed file list).

Whichever method you choose to get the list of compressed files, you are correct that you'll next need a batch macro to loop through each file and write them individually to new database tables.

The macro takes an input for the list of files in the zip & has the Control Parameter on top of a Filter tool to select a single file at a time.  That zip file location is fed into a Dynamic Input tool to retrieve the data.  Normally, I'd have the Dynamic Input tool configured to leave the File Name as an additional field, but in this case, the File Name is always the zip file.  So, instead, there's an Append Fields tool to get back the compressed file name (without the ".csv" extension) to be used as the target table name in the Output Data tool.

Get file list from zip.yxmd

WriteFilesFromZipIntoTables.yxmc