Forum Discussion
PowerBI query export table using R script
Hi,
I have a table and whant to export this to CSV on a local computer.
This R script runs every week so on the local computer I whant the output name as: 48-2023, 49-2023 and so on.
In my table there are multiple columns such as: Week, Date, etc.
This is what I have in the R script:
# 'dataset' holds the input data for this script
now<-format(Sys.time(),"%d%m%Y")
Table <- paste("\\\\af01\\Users\\Tinus\\Desktop\\new\\TEST",now,".csv")
write.table(dataset, file=Table, sep=";",na="", row.names=FALSE, quote = FALSE)
Every week I get "TEST 23-11-2023", TEST 30-11-2023.
I guess I must change <-format(Sys.time(),"%d%m%Y") to somyething else, but what?
Hi Tinus1905 ,
Please try:
# 'dataset' holds the input data for this script # Get the current date and time now <- Sys.time() # Format the date to get the ISO week number and year week <- strftime(now, "%V-%Y") # Create the file path with the new naming convention Table <- paste0("\\\\af01\\Users\\Tinus\\Desktop\\new\\TEST", week, ".csv") # Write the dataset to the file write.table(dataset, file = Table, sep = ";", na = "", row.names = FALSE, quote = FALSE) ```Please note that %V gives you the ISO week number, and %Y gives you the year. Make sure that your system's locale settings support the %V format specifier for week number. If not, you may need to use an alternative method to calculate the week number. After making these changes, your script should export the table to a CSV file with the naming convention you desire (e.g., "TEST48-2023.csv").
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous this is perfect.
Is it also possible in the R script to send the export as an attachement directly with email?
9 Replies
Hi Tinus1905 ,
Please try:
# 'dataset' holds the input data for this script # Get the current date and time now <- Sys.time() # Format the date to get the ISO week number and year week <- strftime(now, "%V-%Y") # Create the file path with the new naming convention Table <- paste0("\\\\af01\\Users\\Tinus\\Desktop\\new\\TEST", week, ".csv") # Write the dataset to the file write.table(dataset, file = Table, sep = ";", na = "", row.names = FALSE, quote = FALSE) ```Please note that %V gives you the ISO week number, and %Y gives you the year. Make sure that your system's locale settings support the %V format specifier for week number. If not, you may need to use an alternative method to calculate the week number. After making these changes, your script should export the table to a CSV file with the naming convention you desire (e.g., "TEST48-2023.csv").
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous thanks, this works.
But what if I whant the week before the current week (the report is always the week before the current week).
Or is there a calculation that brings the value of the column 'week' (this is the same value for all rows of the week before the current week) of the report into the exportname?
Hi Tinus1905 ,
To export the table for the week before the current week, you can modify the script as follows:
# 'dataset' holds the input data for this script now <- Sys.time() - 604800 # 604800 seconds = 1 week week <- format(now, "%V") year <- format(now, "%Y") Table <- paste0("\\\\af01\\Users\\Tinus\\Desktop\\new\\TEST ", week, "-", year, ".csv") write.table(dataset, file = Table, sep = ";", na = "", row.names = FALSE, quote = FALSE)This will give you the desired output name format of "TEST week-year.csv" (e.g. "TEST 48-2023.csv", "TEST 49-2023.csv", etc.) for the week before the current week.
Alternatively, if the value of the column 'week' is the same for all rows of the week before the current week, you can extract that value and use it in the file name. Here's how you can modify the script to do that:# 'dataset' holds the input data for this script week <- unique(dataset$Week) - 1 year <- format(Sys.time(), "%Y") Table <- paste0("\\\\af01\\Users\\Tinus\\Desktop\\new\\TEST ", week, "-", year, ".csv") write.table(dataset, file = Table, sep = ";", na = "", row.names = FALSE, quote = FALSE)
This will give you the desired output name format of "TEST week-year.csv" (e.g. "TEST 48-2023.csv", "TEST 49-2023.csv", etc.) for the week before the current week.
Please note that the backslashes in the file path should be escaped with another backslash, or you can use forward slashes instead.Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.