Directory output many .gz to many excel files?

[StephenF]

I have a directory full of .gz files containing one csv data file each I wish to spit them back out as xlsx files.

Best I can do is get a row of the file details printed on one sheet each in one xlsx file , what am I doing wrong?

I find this widget very unintuitive.

[Claje]

Hi,

You're very close!

With Excel files, "Change File/Table Name" updates the sheet name, not the actual file name itself.  Alteryx thinks of excel sheets as "tables".

To output to unique files, you will need to use "Change Entire File Path" - however this will output these files to the directory your Alteryx workflow is in unless we make one more change - adding G:\FTP\sales\Samantha\DataExtracts\out\ to the "FileNameOutput" field.

You could modify your FileNameOutput formula to include this, or add a new formula tool with the following formula:

"G:\FTP\sales\Samantha\DataExtracts\out\"+FileNameOutput

[StephenF]

Ok I guess I have the excel files now, but they don't contain the contents of the source .gz files, just the path and filename information.

Maybe i need something else between the directory input and the output.

Maybe Dynamic Input but I dont know how to get the path/filename information into each file.

[Claje]

My apologies, I thought you had already successfully extracted the data.

I would definitely recommend the Dynamic Input tool for this - you can set it to modify the full path to your file, and set the template to "Output Full path" under option 5 of the template.

You may run into a challenge if the schema of each individual file is different - if this is the case, let me know and I'll see if I can put together a batch macro for you that should help.

[StephenF]

Yes, each file has a different number of columns. I'll need help alright as I have no clue what I'm doing.

[JordanB]

HI @StephenF 

You will need to extract the .gz files somewhere on your machine to allow Alteryx to read them in using a dynamic input (If all the files are the same) or a batch macro (If the files are different)

Here is a nice thread on unzipping zip files by @jdunkerley79 & @chris_love  - I have not tested this on .gz files so you may have to test this first. 

Based on your brief I have attached a workflow which you should be able to modify to achieve your desired output. I have an image below with annotations in the tool containers and tool notes to describe what you will need to do. The Yellow containers are all you should have to update to get the workflow to work. That said you need to confirm if the unzipping process using the run command tool successfully unzips .gz. 

Best,

Jordan

[StephenF]

Dynamic input complains about a missing connection in when I Use it. Any idea?

[JordanB]

Hi @StephenF 

You are close. Rather than a dynamic input you will want to use a regular input tool. 

Configure it to point to one of the csv files you have on your machine. 

You will then need to go to View>>Interface Designer

In here you will need to go the fourth icon down on the left of the pop out window. It will look like a cog icon.

Change the radio button at the bottom for output option to 'Auto configure by name'. 

Save this macro and insert into your master workflow

[StephenF]

I have no .CSVs, so I am pointing it at the .GZ which contains the .CSV. That seems to work.

For the output to excel does that need to be in the Macro or no?

Further, I have added the original filename inserted into a column "filename" on each file but on output i get "You must specify a sheet name." error.

[JordanB]

HI @StephenF 

that is correct the output tool will be outside the macro. 

As this is an xlsx. output in your filename field before outputting you will need to add a sheet name. In a formaul you can up date the filename field with [filename]+"|||Sheet1". You can of course use another field in your data set to label the sheet or rename it in the formula.

You then need to select the tick box in the bottom left in the configuration window of the output tool in your image below. "Take file/table name from field'

Change the dropdown menu to 'change entire file path' - Here you will then select the filename field in the final dropdown menu. If you want to include this as a field in your dataset don't uncheck the box. 

Once you runt his workflow this should generate your files

Best

Jordan