Download ZIP file error zip.txt empty

Hello,

I am trying to download zip file and output them into a folder. I applied workflow called DownloadAndExtractZips(solution in one of other community post on how to download zip file) into my workflow but the process have an error. It seems like my zip.txt file (which includes all the name of the files inside the zip folder) is empty. The workflow is not fetching the names properly. The zip file is password protected so I believe this may have caused the issue. What are the commands I need to add to be able to get the file names?

Thank you.

DownloadAndExtractZips.yxmd

Workflow

Developer Tools

Output

Macros

Download

Accepted answers

Maskell_Rascal

@MinhTa - I don't have a base64 zip file via a download to test this on, but the below updated code should work for you with the attached workflow.

from ayx import Alteryx
import pandas as pd
import zipfile
import glob
import base64
filepath = Alteryx.read("#1")
file = filepath['File'].iloc[0]
file2 = filepath['FileDecoded'].iloc[0]

with open(file, 'rb') as file_input, open(file2, 'wb') as file_output:
    base64.decode(file_input, file_output)

for zip_file in glob.glob(file2):
    zf = zipfile.ZipFile(zip_file)
    dfs = [pd.read_csv(zf.open(f), header=None, sep=",") for f in zf.namelist()]
    df = pd.concat(dfs,ignore_index=True)
    print(df)
    
Alteryx.write(df,1)

I've added in a base64 decode to the script, and a formula tool prior to the Python to give the base64 decode a file to write it to. All of this will still happen in the temp files for you.

You should be able to update the URL in the text input on the attached workflow and get it to run successfully.

Let me know if this works for you.

Phil

Download and Extract All Files from base64 ZIP.yxmd

All comments

apathetichell

there's a slew of things that could be off - for starters - add a summarize tool to concatenate the strings that you want in your .bat file/command line

Maskell_Rascal

Hi @MinhTa

I might be the outlier here, but I prefer to use the Python tool to extract zip files into my workflows.

You can first use the download tool to get the URL and then let Python do all the rest of the work.

In this example, I have the headers set to "None", since the zip file I downloaded has inconsistencies with header being included in the data. You can change this to "0" if you have more consistent data.

from ayx import Alteryx
import pandas as pd
import zipfile
import glob
filepath = Alteryx.read("#1")
file = filepath['File'].iloc[0]

for zip_file in glob.glob(file):
    zf = zipfile.ZipFile(zip_file)
    dfs = [pd.read_csv(zf.open(f), header=None, sep=",") for f in zf.namelist()]
    df = pd.concat(dfs,ignore_index=True)
    print(df)
    
Alteryx.write(df,1)

The zip file contains 64 .txt files and is downloaded as a temporary file.

Attached is a sample workflow for you to try.

Let me know if this works for you.

Cheers!

Phil

Download and Extract All Files from ZIP.yxmd

MinhTa

@apathetichell Can you give me some suggestion on how to do this? Honestly, I dont know what I want in my .bat command line, not too familiar with this.

MinhTa

@Maskell_Rascal Thank you for the wonderful suggestion. However, I was not able to make it work. I kept getting "NameError". Do you have any suggestion on how to solve this?

Thank you.

Maskell_Rascal

@MinhTa - can you post a screenshot of the code in your Python tool? The error you’re receiving shows that you didn’t define “df”. If you look at the solution I provided, you’ll see that “df” is defined at the end to concatenate the files together.

MinhTa

@Maskell_Rascal Hello, I did not change your code at all. I kept it the same and plug it in my workflow. Here is the python code:

Maskell_Rascal

What is the input tool labeled "Output.csv"? Is that the actual zip file? In your initial post you were using a download tool to pull the file to a temp folder and then working on extracting it. If you already downloaded the zip file and just want to input the contents, that's a different workflow.

MinhTa

@Maskell_Rascal Output.csv is the temp file. When I download the zip file from the db, it comes in the form of base64. I had to decode it and then save it to a temp file. Afterward, I try to convert it to zip file using your tool. I am trying to replicate the process as much as possible so the output.csv is basically step after your select tool. Output.csv contains the directory to the temp file.

Maskell_Rascal

@MinhTa - I don't have a base64 zip file via a download to test this on, but the below updated code should work for you with the attached workflow.

from ayx import Alteryx
import pandas as pd
import zipfile
import glob
import base64
filepath = Alteryx.read("#1")
file = filepath['File'].iloc[0]
file2 = filepath['FileDecoded'].iloc[0]

with open(file, 'rb') as file_input, open(file2, 'wb') as file_output:
    base64.decode(file_input, file_output)

for zip_file in glob.glob(file2):
    zf = zipfile.ZipFile(zip_file)
    dfs = [pd.read_csv(zf.open(f), header=None, sep=",") for f in zf.namelist()]
    df = pd.concat(dfs,ignore_index=True)
    print(df)
    
Alteryx.write(df,1)

I've added in a base64 decode to the script, and a formula tool prior to the Python to give the base64 decode a file to write it to. All of this will still happen in the temp files for you.

You should be able to update the URL in the text input on the attached workflow and get it to run successfully.

Let me know if this works for you.

Phil

Download and Extract All Files from base64 ZIP.yxmd

Maskell_Rascal

I suspect the reason the original workflow wasn't working for you was because your CSV file field name is "Output file". In the Python code I was reading in the entire input but then defining which field I was using.

MinhTa

Maskell_Rascal

Hi @MinhTa - I'm not sure what is happening. The only thing I know for sure is that the path/file you are pointing to that is feeding into the Python code is not a zip file. I'm guessing that somewhere between your decode process and saving as a tmp file it is being corrupted.

Did you try the workflow I provided that has a base64 decode built into the python code?

MinhTa

Hello @Maskell_Rascal Thank you for your reply. I just tried the workflow implemented base64 you provided. However, I got the following error:

SUCCESS: reading input data "#1"

---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
c:\users\ata\appdata\local\alteryx\bin\miniconda3\envs\designerbasetools_venv\lib\site-packages\pandas\core\indexes\base.py in get_loc(self, key, method, tolerance)
   2888             try:
-> 2889                 return self._engine.get_loc(casted_key)
   2890             except KeyError as err:

pandas\_libs\index.pyx in pandas._libs.index.IndexEngine.get_loc()

pandas\_libs\index.pyx in pandas._libs.index.IndexEngine.get_loc()

pandas\_libs\hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item()

pandas\_libs\hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item()

KeyError: 'FileDecoded'

The above exception was the direct cause of the following exception:

KeyError                                  Traceback (most recent call last)
<ipython-input-1-2e915d41677b> in <module>
      6 filepath = Alteryx.read("#1")
      7 file = filepath['File'].iloc[0]
----> 8 file2 = filepath['FileDecoded'].iloc[0]
      9 
     10 with open(file, 'rb') as file_input, open(file2, 'wb') as file_output:

c:\users\ata\appdata\local\alteryx\bin\miniconda3\envs\designerbasetools_venv\lib\site-packages\pandas\core\frame.py in __getitem__(self, key)
   2897             if self.columns.nlevels > 1:
   2898                 return self._getitem_multilevel(key)
-> 2899             indexer = self.columns.get_loc(key)
   2900             if is_integer(indexer):
   2901                 indexer = [indexer]

c:\users\ata\appdata\local\alteryx\bin\miniconda3\envs\designerbasetools_venv\lib\site-packages\pandas\core\indexes\base.py in get_loc(self, key, method, tolerance)
   2889                 return self._engine.get_loc(casted_key)
   2890             except KeyError as err:
-> 2891                 raise KeyError(key) from err
   2892 
   2893         if tolerance is not None:

KeyError: 'FileDecoded'

I wonder if I have any issue with how I save the encoded zip file. Here are the steps I took:

After I download zip file from db, the response I get is:

The resource_content is the zip file encoded in base64. I extracted the resource content, only keep the encoded part and saved it in a temp file with directory saved in output.csv. Before I had base64 decoder after extracting encoded base64 from resource content but I have removed it to test the python base64 decoder. This is the configuration of the output tool to save the temp file.

Output.csv is then used as input to second work flow. (contains directory to temp file)

Applied python code with base64 decoder implement (pretty much copy and paste the code you suggested earlier)

Maskell_Rascal

I would try using the workflow I provided to download the file directly and don't bother with the decoding/zip workflows you are using. The latest one I provided is designed to pull down the file, decode it, and unzip the contents.

MinhTa

@Maskell_Rascal I just tried as you recommended, no more extract resource_content, just directly save the response to temp file. However, I got the same error message.

SUCCESS: reading input data "#1"

---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
c:\users\ata\appdata\local\alteryx\bin\miniconda3\envs\designerbasetools_venv\lib\site-packages\pandas\core\indexes\base.py in get_loc(self, key, method, tolerance)
   2888             try:
-> 2889                 return self._engine.get_loc(casted_key)
   2890             except KeyError as err:

pandas\_libs\index.pyx in pandas._libs.index.IndexEngine.get_loc()

pandas\_libs\index.pyx in pandas._libs.index.IndexEngine.get_loc()

pandas\_libs\hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item()

pandas\_libs\hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item()

KeyError: 'FileDecoded'

The above exception was the direct cause of the following exception:

KeyError                                  Traceback (most recent call last)
<ipython-input-1-2e915d41677b> in <module>
      6 filepath = Alteryx.read("#1")
      7 file = filepath['File'].iloc[0]
----> 8 file2 = filepath['FileDecoded'].iloc[0]
      9 
     10 with open(file, 'rb') as file_input, open(file2, 'wb') as file_output:

c:\users\ata\appdata\local\alteryx\bin\miniconda3\envs\designerbasetools_venv\lib\site-packages\pandas\core\frame.py in __getitem__(self, key)
   2897             if self.columns.nlevels > 1:
   2898                 return self._getitem_multilevel(key)
-> 2899             indexer = self.columns.get_loc(key)
   2900             if is_integer(indexer):
   2901                 indexer = [indexer]

c:\users\ata\appdata\local\alteryx\bin\miniconda3\envs\designerbasetools_venv\lib\site-packages\pandas\core\indexes\base.py in get_loc(self, key, method, tolerance)
   2889                 return self._engine.get_loc(casted_key)
   2890             except KeyError as err:
-> 2891                 raise KeyError(key) from err
   2892 
   2893         if tolerance is not None:

KeyError: 'FileDecoded'

Maskell_Rascal

You're missing the formula tool after the select tool. That's where we create the field "FileDecoded". This is the traceback error you are receiving. Throw a formula tool in right before the python tool configured like the below.

MinhTa

Maskell_Rascal

Hi @MinhTa - Can you confirm which of the files is password protected?

base64
zip file
files in the zip file

The error you provided appears that the CSV file inside the zip file requires a password, but want to confirm before we think about next steps.

MinhTa

@Maskell_Rascal I just confirmed this with higher up and only the zip is password protected. Files inside the zip are not.

Maskell_Rascal

Hi @MinhTa

Lets try this again! 😀

I added a text input that is reading into Python with a password. This password is then fed into the zip file when opening/extracting. You will need to update the text input file with your password to make this work. You could also feed in a file stored locally on your computer, so the password isn't saved in the actual workflow. Just make sure that the file has a column labeled "Password".

from ayx import Alteryx
import pandas as pd
import zipfile
import glob
import base64
filepath = Alteryx.read("#1")
file = filepath['File'].iloc[0]
file2 = filepath['FileDecoded'].iloc[0]
pswd = bytes(Alteryx.read("#2")['Password'].iloc[0],'utf-8')

with open(file, 'rb') as file_input, open(file2, 'wb') as file_output:
    base64.decode(file_input, file_output)

for zip_file in glob.glob(file2):
    zf = zipfile.ZipFile(zip_file)
    dfs = [pd.read_csv(zf.open(f, mode='r', pwd=pswd), header=None, sep=",") for f in zf.namelist()]
    df = pd.concat(dfs,ignore_index=True)
    print(df)
    
Alteryx.write(df,1)

Let me know if this one works for you.

Phil

Download and Extract All Files from base64 ZIP with password.yxmd

MinhTa

Hello @Maskell_Rascal,

Thank you very much, I was able to extract the file successfully (saw the data appear in the output section). There is 1 question I have left is how can I distinguish where the data came from (from which file)? I want to output them into excel or csv file so I need to way to distinguish them when output. The zip file I have only 1 file inside but lets say I have more than 1 then how can I solve this.

Thank you, very grateful for your help.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703