Loop Through CSV Files, Write Each File to a Sheet in Single Excel File

[cbolin]

I have 21 (may vary) CSV files in a folder. From each filename, I derived a sheet name. I would like to put each of these files into its own separate Excel sheet but within the same Excel workbook.

Currently, the important data I need is in the output table of a "Select Tool". It contains "FullPath" and "Sheetname" fields. Each record in this output table contains the data to create and populate a single Excel sheet.

I could hard-wire this with 21 Input Data tools and 21 Output Data tools. However, that is too ugly IMHO.

Before I try over-engineering a solution, does anyone have any straight forward ideas for making this happen?

Thanks,

Chuck

[pedrodrfaria]

Hi @cbolin 

Please see attached for an example of reading multiple CSVs, and saving them under the save file into different sheets.

This is using a Macro created to load all the files at once.

I attached 3 dummy files, a package with the workflow and the macro and the output file below.

Please assign the solution to the post if your question was answered.

Pedro.

[cbolin]

Hi,

I appreciate you sharing me the example. I think you have a newer version of designer. As a result my version (2020.1.5.25447) cannot open your workflow in the imported yxzp file.

I'm not allowed to update my current version in our company. Any chance you can show the workflow in an image?

Thanks,

Chuck

[pedrodrfaria]

HI @cbolin 

If you right click on the workflow or the macro file in your windows, and open it as a notepad, you can see where I highlighted, you can actually change the version of the alteryx workflow file to your version, in this case to 2020.1.

Either way, I added the alteryx files for the 2020.1 version.

Please mark it as a solution if it answered your questions.

Pedro.

[cbolin]

Hi,

I knew the yxzp file was a binary file but I didn't recognize it as compressed (zipped) file type. Just figured that out. I unzipped the file and broke it down into its components of files and directories. I then opened the macro file (yxmc) and the workflow file (yxmd) with Notepad++ and changed the version to 2020.1.

Thanks for explaining this concept to me. I'll get to testing the workflow a little bit later today.

Thanks,

Chuck

[cbolin]

Hi Pedro,

Just to re-calibrate, I have a bunch of CSV files in a folder. I have a data output table in Alteryx workflow that lists the filepath to the CSV and the sheetname to the target sheet in the workbook (see attached image). My goal is to load each CSV into its own sheet with a specific name. I am having a difficult time understanding how to work with this data given your workflow and macro.

I'm working my way through the workflow and macro you provided. Thank you. I encountered various errors and unexpected behaviors along the way.

I discovered that the 55.xlsx must be very important. It was not included. I'm guessing the sheet names in that Excel file correspond to the sheet names in your Output file. Is this correct?

I jumped to your macro. I do have an output table. I wish for the macro to read through the rows of the table and apply the data (source filename and target sheet name) and apply the macro code to each row. What is not clear is how to tie the control parameter that is incremented to a row number in my data table.

I see that Control Parameter is an arbitrary variable value and unrelated to anything in the workflow. It feeds the Action Tool configured with Update Value (Default). I'm trying to understand what underlying variable is being incremented by the the Action Tool. I think it is the output sheet ("...\Output.xlsx|||'1$'"). Is this correct? Does the dollar sign mean anything special in this case, such as indicating it is the variable?

I see that the Output.xslx must have been created already.

I cannot find any tool in your workflow that loads each of the CSV files into the targeted Excel file and worksheet. Where does that happen exactly? I may be getting lost in the the configuration options.

Thanks,

Chuck