Pull data in from folder

[ChenChristyYu]

Hi Experts,

I am pulling data from a big folder, the folder is made up of smaller folders. Each smaller folder contains about 500 PDF file, I tried a folder pull, but instead of pulling in everything, I only want to pull in the name of the PDF files (no contents and others).  It ran very very slow. What can I do?

Thanks very much.

[TomMartens · Accepted solution]

Hey

this little R script

sourcePath <- "c:/windows"
files <- data.frame(files = list.files(path = sourcePath, full.names = TRUE, recursive = TRUE))

returns 170000 full-filenames within 28sec on my laptop.

Maybe you can use an R script as a datasource, you just have extract the "filename" from the fullname 🙂

I guess these links will get you started with R and Power BI 🙂

• https://community.powerbi.com/t5/Community-Blog/R-script-as-data-source-in-Power-BI-Desktop/ba-p/433...
• https://docs.microsoft.com/en-us/power-bi/desktop-r-in-query-editor

Regards

Tom

[TomMartens · Accepted solution]

Hey

this little R script

sourcePath <- "c:/windows"
files <- data.frame(files = list.files(path = sourcePath, full.names = TRUE, recursive = TRUE))

returns 170000 full-filenames within 28sec on my laptop.

Maybe you can use an R script as a datasource, you just have extract the "filename" from the fullname 🙂

I guess these links will get you started with R and Power BI 🙂

• https://community.powerbi.com/t5/Community-Blog/R-script-as-data-source-in-Power-BI-Desktop/ba-p/433...
• https://docs.microsoft.com/en-us/power-bi/desktop-r-in-query-editor

Regards

Tom

[ChenChristyYu · Accepted solution]

Hi Tom, 

I am trying to get it work using your way.

I input 

sourcePath <- "\\ChinaBusinessReports\BusinessReports\China\201803"
files <- data.frame(files = list.files(path = sourcePath, full.names = TRUE, recursive = TRUE))

 but got this error message

Details: "ADO.NET: R script error.
Error: '\B' is an unrecognized escape in character string starting ""\\ChinaBusinessReports\B"
Execution halted"

I am new to this. I looked at other sources, but have not found any clue solving this. Do you have any insight? Thank you so much.

Best,

Chen

[TomMartens]

Using R means you have to use „/„ (the forward slash) instead of „\“ (the backslash) 😉

Not sure if double \\ will work

I assume you have installed to your local machine.

Regards
Tom

[ChenChristyYu]

I have installed it and set up the r script option in Powerbi. I have been stuck on the Connecting process for a long time and have not been moving on from there.

[TomMartens]

Hey @ChenChristyYu,

here you will find a little word doument that describes the steps to use a R script as a data.source in more detail and here is the pbix file

To verify your setup, run this R script in your R ide and make sure that the variable files contains reasonable values and is of type data.frame by using the command str(files).

sourcePath <- "c:/windows"
files <- data.frame(files = list.files(path = sourcePath, full.names = TRUE, recursive = TRUE))

Hope this gets your issue fixed.

Regards

Tom

[Greg_Deckler]

Something similar to this came up before in another thread. Not sure that Power BI is the right tool for this, I would use PowerShell.