Appending Data in Excel

[KamenRider]

Hi

This report is done daily wherein we append data to "Time_Mist Analysis" file. Need to use a directory tool to call the latest excel file to append with. The source of data has filename for example Time_mist + current date. Everyday appended data goes to the bottom part of the Time_Miist Analysis file. It has two tabs that needs updating. The RENO tab and the CBAS tab. In the CBAS tab, need to automate tby entering the current data and transpose the data from "drop" column. Below is the sample:

Source:

 

Output after append in the pool data tab time_mist_analysis file. We just need the data and the data in the drop column.

 

In the RENO tab, just transfer and append data to the time mist analysis file in the reno tab.

Below is the files attached.

Hope to hear from someone assistance.

Thanks,

Kamen

[MeganBowers]

Hi @KamenRider, see if the solution attached works for you. One thing to note-- I set up the outputs to write to a new file so I could check that the workflow is working properly. If this looks good, then you can change the output file to be the source file you want to update. Let me know if you have any questions!

 

[Qiu]

@MeganBowers 
Your flow looks very elegant😁

[KamenRider]

Hi @MeganBowers 

Nice workflow! Thank you for this ideas however there are some questions and updates I would like to integrate in the workflow as follows

1. Is it possible to keep the format of the date in the RENO going to the output file. The format looks like this: "2024-08-16 00:00:00".

 

2. Is it possible instead of creating an new output file which is "combined", the data will append in the "time_mist_analysis.xlsx?

3. This third has new scenario, I have updated the sample file. This will capture the data from the source file and overwrite the data found in the "time_mist_analysis" under pipe data tab. There is a formula in column AD to AH which should automate when the data is pasted. The number of records varies. If it  is less, the excess rows will be deleted. The source will be coming from the Aggregate tab of time_mist + date.

Apologize for the new data. It just comes in. Please let me know if you have questions.

Hope to hear from you,

Thanks,

Kamen

[KamenRider]

Hi @MeganBowers  and others with great ideas.

Can I ask for your assistance on my inquiries below.

Thanks and looking forward for your help.

Kamen

[MeganBowers]

Hi @KamenRider,

1. You can use the DateTime tool to convert the ASOFDATE into a string with the custom format yyyy-mm-dd hh:mm:ss. Then you will need to update the workflow to treat that value as a string moving forward

2. You can write to your input file by switching the connection in the output tool to "\time_mist_analysis .xlsx|||`reno_data$`" (use the correct path in front of this depending on where you are storing the file on your computer). I would save a copy of your original input elsewhere in case you overwrite the file incorrectly by accident.

3. I'm hoping the workflow provided with these changes in 1 and 2 is enough for you to attempt to add on to it with your new data :) 

[KamenRider]

HI @MeganBowers 

Tried connecting the file as output to "time_mist_analysis.xlsx" however while the Alteryx runs with no error, when I attempt to open the excel file, here's the error I got.

 

 

I know you are busy but can you please help me produce an output where I can use as reference on how doing it? This will help me proceed with the other tabs.

Looking forward for your response.

Kamen

[MeganBowers]

Hi @KamenRider, apologies for the delay. I got the same error, although I clicked yes to repair the workbook. Then I got the message:

Repair Result to time_mist_analysis  (1)0.xml

Errors were detected in file '/Users/megan.dibble/Downloads/time_mist_analysis  (1).xlsx'

Removed Records: Formula from /xl/calcChain.xml part (Calculation properties)

It looks like it's an issue within the .xlsx file. Can you take a look at the solution to this discussion thread and try that on your Excel workbook to remove the calcChain.xml part? (It's weird to me that this is an issue in a simple Excel file, but perhaps there were previously additional tabs or pivot tables that were deleted?)