Read Dynamic name changing csv from zip file

[AIPH123]

Hi All,

I have a single csv file in a zip folder where the name of csv change every month. How do I read the data from this CSV every month irrespective of its file name and host it in server? Please advise.

Thank you.

Regards,

Anil 

[binuacs]

@AIPH123 you can use the wild card option in the input tool like below

[Emmanuel_G]

Hi @AIPH123 ,

Try looking at this link : Dynamic Read csv from zip file in folder

[AIPH123]

But the CSV is in the zip folder. I tried giving wild card with zip file specification and I get an error.

[Emmanuel_G]

@AIPH123 ,

Find a test attached.

There is a macro that will read inside your .zip file all the .csv files and export them in a column with the name of the zip file as shown in the screenshot.

All you have to do is specify the folder where your zip file(s) containing the files you want to play is located.

And an optional possibility if you want to take into account just a small number of lines to run fast enough in your test phases.

So you won't need to unzip and it's dynamic.

Let me know if it works as you want or any malfunction.

[AIPH123]

Thank you Emmanuel, but the flow is extracting all the data into a single column. I will only have 1 CSV file in ZIP but the name of the file keeps changing everytime I get it. In this case, I just want to read that csv and get the data into workflow and use that data for further data transformations. For this I have developed a workflow with directory tool where I have specified the zip file location and created a file path. When I am trying to use dynamic input tool, I dont understand what should we specify in "Input data source template" in the dynamic tool? Can dynamic input tool give us an output of the csv content read from ZIP?

[Emmanuel_G]

Hi @AIPH123 ,

Yes the dynamic input tool can do that as long as you have the BOM c:\some\path\to\file.zip|file1.csv or c:\some\path\to\file.zip|file1.csv;file2.csv if many files. Find a zip file support here for more details.

For your first question, in fact the dynamic input needs a model on which it will base itself to read the file from the path in the input anchor. So in the dynamic input, you have to do as if you were reading a .csv file coming from a .zip file.( take a look at screenshoots below )

It will use this reading as a model and do it for all rows that will come as input. 

You change The File in Archive ( Line 3 of dynamic input ) to edit the name of .csv file.

Note that whatever .csv file you put in this line 3 of the dynamic input, as long as it's a CSV and even if your file changes, the model to read remains a .csv so your new file to read will be interpreted as it is.

That's why in the settings on the left, you indicate Change entire File path.

Putting a pattern just tells the dynamic input how to interpret the path to the file to read.

Is it clear to you?

[Emmanuel_G]

@AIPH123 

For the macro,it reads your file .csv in zip and puts all the data in a single column.

You can then use the Text in columns tool ( screenshoot below ) on this column containing concatenated values ​​to parse and order your columns.

[AIPH123]

I am still having problems with the Dynamic input tool. Below is the screenshot of the full path which is used as an input into dynamic input tool.  In dynamic input tool, for input data tool, I gave a dummy zip folder with empty csv file in it. Not sure if its the right way of doing. Can you please correct me If I am doing it right?

[Emmanuel_G]

@AIPH123  Just after the FullPath, change "Append Suffix File/Table Name" by "Change Entire File Path" such as my screenshoot above.