Outputting to Multiple Excel Files (some containing multiple sheets)

[raychase]

Hello,

I recently got some help outputting my data to multiple excel files, and some of those files contain multiple sheets.  The single output tool is set up to "overwrite sheets" and is grabbing the entire path name from a field in my data.  Unfortunately, sometimes my files with multiple sheets get written at the same time and I get errored out due to locked files.  I understand I can get away with this using a block until done tool, but I have a lot of files being generated (20+), and some of them have 5+ sheets.  Is there a way to efficiently make use of the block until done tool?  Do I need an iterative macro?  Is there another way around this error?  Please help!

Thanks.

[DavidP]

It wouldn't be an iterative macro, but a batch macro could help you out. Do you have a workflow you can share?

[raychase]

Hi David,

I appreciate the response.  I am attaching a simplified version of the workflow that I'm using, as well as some very basic mock data.  The principles are all the same, just highly scaled in my actual scenario.  You wouldn't expect any errors from this version, but with a larger data set that involves multiple files and sheets, the potential for error locking occurs.

Let me know what you come up with.

Thanks!

[DavidP · Accepted solution]

Ok, have a look at the attached workflow and macro.

I've taken your sample workflow and just bypassed the filter tool in order to create more output files. The filename formula creates a list of filenames with sheet names, like this:

K:\Analytics\David\Output multiple files and multiple sheets\Please fix 1.xlsx|||SheetA
K:\Analytics\David\Output multiple files and multiple sheets\Please fix 1.xlsx|||SheetB
K:\Analytics\David\Output multiple files and multiple sheets\Please fix 1.xlsx|||SheetC
K:\Analytics\David\Output multiple files and multiple sheets\Please fix 2.xlsx|||SheetA
K:\Analytics\David\Output multiple files and multiple sheets\Please fix 2.xlsx|||SheetB
K:\Analytics\David\Output multiple files and multiple sheets\Please fix 3.xlsx|||SheetA
K:\Analytics\David\Output multiple files and multiple sheets\Please fix 4.xlsx|||SheetA
K:\Analytics\David\Output multiple files and multiple sheets\Please fix 5.xlsx|||SheetA
K:\Analytics\David\Output multiple files and multiple sheets\Please fix 6.xlsx|||SheetA
K:\Analytics\David\Output multiple files and multiple sheets\Please fix 7.xlsx|||SheetA

Now you want to create your macro that will write the appropriate content to each of these files and sheets, so start with building a workflow that does it for the 1st one, like this. The Text input Tool is a placeholder for the data you're going to pass into the macro. Use a filter tool to only pass through the data for the appropriate file and sheet, a select tool to remove the filename field and then an Output data tool set to write to a sheet in an excel file.

Now right click on the text input tool and select "Convert to Marco Input". Drag a Control Parameter tool from the Interface palette onto your canvas. From the Q below the control parameter, drag to the lightening above the Output Data tool. An Action tool will automatically be inserted between the 2. You have to tell the Action tool what value/parameter you want it to update in the Output Data tool from the Control Parameter. In this case, we want it to update the file value, so simply click on it in the configuration window.

We also want the same control parameter to update the value in the filter tool though, so from the Q at the bottom of the control parameter, drag to the lightening above the filter tool - another action tool will be inserted. Here we want to update the operand value in the filter as shown below, so find it and click on it.

You can do more fancy stuff with action tools like use formulas or replace specific parts of a string. You could also have multiple Control parameters coming from your workflow to update values in your macro.

Our macro now looks like this:

Finally you need to update your initial workflow to call the macro, like this. From you dataset, you have to tell the macro which field will be your control parameter. In our case it's [filename].

You'll probably want to bring your email tool into the macro as well. 

Workflow package attached, let me know if you have any problems getting it to work

[raychase]

Hi David,

I am trying to work through your message.

Since I have multiple instances of the same filename, I decided to isolate them with a unique tool, and output those filenames to a .csv file.  This file will be used as an input in the macro.

I need to make sure that all of the records that are tied to a specific filename (varies from 1-thousands), are written just once to that particular filename.  When I ran my macro, it was writing all of the records to the problem files, but it was doing it for every row of data.  I also need to email that particular filename to its corresponding data steward, which is another field in the data.  It should be noted that filenames are unique; however, multiple filenames can be associated with the same file, just having different sheets.  As such, I need to find a way to only send 1 email.  To do that, I think I can just text-to-columns the filename to isolate the actual .xlsx name, and forget the sheets.  Then I can use a unique tool to get the file name, and use that for attachments.  I'll also use Data Steward Emails 1 and 2 for the TO and CC boxes.  How can I get those data fields accessible within the macro?  I only see the filename coming through, so it's the only thing selectable (in fact, it is being removed by the select tool, so I have no usable fields).

I am an absolute novice with macros, so I appreciate your patience.

Thank you.

[DavidP]

The filename is something you create with the formula tool, so you could choose to have a [filename] field, a [sheetname] field and [fullpath] = [filename] + [sheetname] field.

As I said earlier, you could have multiple control parameters, for instance, [fullpath] to control the Output Data Tool and [filename] to control the email tool. Alternatively, you could create a text box field inside the macro with one placeholder entry, pass the [fullpath] parameter to it and then bring in a formula tool to chop off the sheetname before connecting it to the email tool.

Remember, a batch macro is just a normal workflow that you've added some control parameters to in order to repeat the same process for a list or lists of entries.

The data stewards' email fields are part of the data coming in with the macro input, so you can just access them within the macro as normal.

[raychase]

Thanks for your continued efforts with me.

I am able to successfully output the proper records to their respective file path; however, it is repeating the operation for EVERY record.  Is there a way to resolve this? (see screenshot below)

I was able to add more parameters, as you suggested, for Email 1 and Email 2; however, the action for the attachment doesn't seem possible.  Any particular reason for this?  I did create a valid "file" name, rather than the full path (which includes sheet).  I'd like to use this as the attachment field.

Thanks.

[raychase]

I realized I don't need those extra parameters, and I can actually handle the emailing at the end, once all of the outputs are created.

I am still running into the problem of writing the outputs for the total # of records that I have (~10000).  If I can sort that issue out, I'll be golden.

Updated look of the workflow:

[raychase]

Update: I fixed my own problem by using the Unique File Paths as the Control, and the full data as the Input.

That pretty much finalizes my attempt; however, it doesn't seem to have resolved my ultimate problem, which was controlling the output of files so I can "pause" for a bit after each file output, to avoid files being locked when new sheets are written.  Is there a way to build in a pause at the end of the macro run?  That should just about do it.

[raychase]

I used the CREW WaitASecond macro (within my macro) to achieve my desired result.

Thanks for getting me on the right track!