Create a folder based on the field values

[dineshbabu2811]

Hello All,

I have a table where it has 2 columns employee Name and Department, My requirement is to create a folders based on the department and generate excel output in that folder with respect to department and employee Name.

I tried to create a folder using bat file its creating successfully, but my bat file still remains in that folder so it might be risk or junk file for end users.

How to create the folder and delete that bat folder using Alteryx version 2020.1

could some one advise on this to resolve it.

Thanks
Dinesh

[jbravman_]

python would be an easier way honestly. 

pass 2 columns like the below into python tool, it will generate the folders and give them back in a dataset then you can use that for whatever in your workflow 

folder_path folder_name

c:\temp A

c:\temp b

etc 

from ayx import Alteryx
import os
import pandas as pd 
df = Alteryx.read("#1")


def create_folder(dataframe):
    created_paths = []
    for index, row in dataframe.iterrows():
        folder_path = row['folder_path']
        file_name = row['file_name']
        full_path = os.path.join(folder_path,file_name)
        
        if not os.path.exists(full_path):
            os.makedirs(full_path)
        created_paths.append(full_path)
    return created_paths

created_paths = create_folder(df)
created_paths_df = pd.DataFrame({'created_paths':created_paths})
Alteryx.write(created_paths_df,1)

[dineshbabu2811]

Hi @jbravman_ 

I have never used Python in Alteryx, could you please share me the sample workflow so that it would be more helpful

[Hamder83]

@dineshbabu2811  - Let me know if this works :) 

[dineshbabu2811]

@Hamder83 I'm getting the below error

Python (11) ---------------------------------------------------------------------------¶TypeError                                 Traceback (most recent call last)¶c:\users\dj209\appdata\local\alteryx\bin\miniconda3\envs\jupytertool_venv\lib\site-packages\pandas\core\indexes\base.py in get_value(self, series, key)¶   4380             try:¶-> 4381                 return libindex.get_value_box(s, key)¶   4382             except IndexError:¶pandas\_libs\index.pyx in pandas._libs.index.get_value_box()¶pandas\_libs\index.pyx in pandas._libs.index.get_value_at()¶pandas\_libs\util.pxd in pandas._libs.util.get_value_at()¶pandas\_libs\util.pxd in pandas._libs.util.validate_indexer()¶TypeError: 'str' object cannot be interpreted as an integer¶¶During handling of the above exception, another exception occurred:¶KeyError                                  Traceback (most recent call last)¶<ipython-input-2-7f4481179d47> in <module>¶     17     return created_paths¶     18 ¶---> 19 created_paths = create_folder(df)¶     20 created_paths_df = pd.DataFrame({'created_paths':created_paths})¶     21 Alteryx.write(created_paths_df,1)¶<ipython-input-2-7f4481179d47> in create_folder(dataframe)¶      8     created_paths = []¶      9     for index, row in dataframe.iterrows():¶---> 10         folder_path = row[r'C:\Users\dj209\Documents\New folder\New folder\test']¶     11         file_name = row['testscript']¶     12         full_path = os.path.join(folder_path,file_name)¶c:\users\dj209\appdata\local\alteryx\bin\miniconda3\envs\jupytertool_venv\lib\site-packages\pandas\core\series.py in __getitem__(self, key)¶    866         key = com.apply_if_callable(key, self)¶    867         try:¶--> 868             result = self.index.get_value(self, key)¶    869 ¶    870             if not is_scalar(result):¶c:\users\dj209\appdata\local\alteryx\bin\miniconda3\envs\jupytertool_venv\lib\site-packages\pandas\core\indexes\base.py in get_value(self, series, key)¶   4387                     raise InvalidIndexError(key)¶   4388                 else:¶-> 4389                     raise e1¶   4390             except Exception:  # pragma: no cover¶   4391                 raise e1¶c:\users\dj209\appdata\local\alteryx\bin\miniconda3\envs\jupytertool_venv\lib\site-packages\pandas\core\indexes\base.py in get_value(self, series, key)¶   4373         try:¶   4374             return self._engine.get_value(s, k,¶-> 4375                                           tz=getattr(series.dtype, 'tz', None))¶   4376         except KeyError as e1:¶   4377             if len(self) > 0 and (self.holds_integer() or self.is_boolean()):¶pandas\_libs\index.pyx in pandas._libs.index.IndexEngine.get_value()¶pandas\_libs\index.pyx in pandas._libs.index.IndexEngine.get_value()¶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: 'C:\\Users\\dj209\\Documents\\New folder\\New folder\\test'¶

[OllieClarke]

@dineshbabu2811 you could write your bat file to the temp drive by setting its filepath as something like:
%TEMP%/script.bat

This file would then be automatically deleted whenever the temp drive is flushed, and either way wouldn't be easily exposed to users

[OllieClarke]

@dineshbabu2811 there's more information on writing to the temp directory here: https://help.alteryx.com/20231/designer/alteryx-and-temporary-files#:~:text=By%20default%2C%20the%20.... 

[Hamder83]

@dineshbabu2811  try and remove # from:

[dineshbabu2811]

It didn't work as expected

[dineshbabu2811]

I can able to create a folder using temp bat file, but when it comes to end user while running from gallery its not working. Also I tried to hardcorde the path of the bat file, after creating the folder bat file still exists in the folder, obviously we don't need bat file to be present. Is there way to kill itself ?