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

Question

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

cbolin · Answer

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

list.png

cbolin · Answer

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

pedrodrfaria · Answer

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.

New Workflow15.yxmd

XLSX Opener.yxmc