Input specific columns from Excel file as columns in Sheet#1 of a new excel file

Question

HI, can someone help me with this please using R tool? I had previously written the code in R and want to migrate it to Alteryx.

I have an excel file as input. and using the data from the file I want to create a TAB (TAB_1) in a new csv file. And the mapping will be as follows:

day_1 <- Sys.Date()
day_1

file_1.tb <- read.Alteryx("#1",mode="data.frame")

col_1 <- file_1.tb[1,1]   # first colum from input file will be first column in new output tab
col_2 <- file_1.tb[1,6]   # 6th column from input file will be second column in new output tab

tab_1.df <- data.frame( Date = col_1, Name = col_2)

tab_1_directory = paste("H:/Documents/New folder/Manual Updates/",day_1,"/tab_1.xlsx",sep="")

write_xlsx(tab_1.df,tab_1_ldirectory)

nkazi01 · Answer

Hi, Thank you for the solution. Just wanted to clarify a few things.

In the first step, I want to grab different excel files each day from a folder that was created that day and names as the date. For example, the excel files will in this path for today "H:/Documents/New folder/Manual Updates/2022-07-20/excel_1.xlsx", "H:/Documents/New folder/Manual Updates/2022-07-20/excel_2.xlsx", ... etc.

Tomorrow's excel files will be taken from this path "H:/Documents/New folder/Manual Updates/2022-07-21/excel_1.xlsx"....

So firstly, how do I dynamically select the folder to choose the excel files from?. After I select each excel file, then I understand that I can use the select tool to choose which columns I want to choose for my output.

Secondly, how do i specify which excel file goes to which tab. For example my input excel_1 might be sheet#2 in my output file, Excel_2 might be sheet#4 in my output file. The out file will be one single excel file. The different input files will be going into the output file as different sheets.

Amol_Telore · Answer

Hey @nkazi01

Here is my solution.

To create dynamic folder with current date, I have used render tool.

I am under impression that input file has Date and Name in header hence I have not renamed columns but if header names are different then you will need to rename these fields in select tool (2nd tool in workflow).

R_to_Alteryx.yxmd