PowerBI query export table using R script

[Tinus1905]

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? 

[Anonymous · Accepted solution]

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 · Accepted solution]

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. 

[Tinus1905]

@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? 

[Anonymous]

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. 

[Tinus1905 · Accepted solution]

@Anonymous this is perfect. 

Is it also possible in the R script to send the export as an attachement directly with email? 

[Anonymous]

Hi @Tinus1905 ,

To export data from Power BI into a file using R, you can use the gdata package. To send an email with an attachment using R, you can use the mailR package.

Please refer: Export Data from Power BI into a file using R - Ben's Blog (datakuity.com)

how do you send email from R - Stack Overflow

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. 

[Tinus1905]

I made a R script for email and that is ok, but now I want the attachment to be the file that is exporting from the other R script. 

So now I have: a table (every previous week) -> R script for export to CSV. 

What I want: a table (every previous week) -> R script for export to CSV -> email the CSV as an attachment. 

[Anonymous]

Hi @Tinus1905 ,

In order to better solve your problem, please open a new case, we recommend a case to solve only one problem, because this can get with good help, and also to give other users a better reference!

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. 

[Tinus1905]

@Anonymous ; is it possible that you help me with the topic: 

"Export and email a table or report from PowerBi" that I posted. I cant find the solution. 

[Tinus1905]

Thanks. I opend a new case.