Deepak - please check follwing in psConfig.txt file: DataRootFolder parameter - if not empty, then this folder should be created. If it is empty, then you do not have to create folder. PSDataFolder - this folder must be created.
Regarding your error - could you please confirm that your PSData folder is specified correctly. In the error message you executing script in the folder (starting part): D:\Documents\PortfolioSlicerV3.0.0\ But in the psConfig.txt you specified location (starting part) D:\Documents\PortfolioSlicerV3\
Maybe that is the reason you are getting this error message?
Hi Vidas, just wanted to compliment you on the beta and the upgrade instructions. Migration to the new version was very smooth. I spent about 30 minutes to migrate all my data (~7 yrs), custom scripts, changing the default locations etc and had no issues whatsoever. And it looks great, a big thumbs up!
I am a little confused by the UpdatePSDataFromExcel.bat script. Is this script only used during the initial conversion process? Or is it necessary to run this script everytime the UpdatePSData.bat script is also run? I am not using PowerBI at this point - only Excel.
Hi Vidas, I have just downloaded the v3 and I am struggling with the issue you commented back on February 24
"If at the start of the script you will see an error message that indicates that script is not able to read Excel file, then you will need to install drivers for that:
"Run external data management script UpdatePSDataFromExcel.bat. If you see any error messages first confirm that in psConfig.txt you have ExcelSourceFile section with your PortfolioSlicer source data file full path specified. If you still getting an error message about "connection error", then you will need to install Access drivers from Microsoft website here... ."
Okay I have installed the drivers. On the download site it says
"To use this download: If you are the user of an application, consult your application documentation for details on how to use the appropriate driver. If you are an application developer using OLEDB, set the Provider argument of the ConnectionString property to “Microsoft.ACE.OLEDB.12.0”
If you are connecting to Microsoft Office Excel data, add the appropriate Extended Properties of the OLEDB connection string based on the Excel file type:
After installing drivers you should not add anything to the connection string - it should work. When you run the script, what error message are you getting?
Hi Vidas, after initially using V3.0 I continued with V2.4 as I had custom reporting and didn't have time to migrate to V3.0.
I am now looking to move forward onto V3.1
In my PortfolioSlicer-Source file "Transactions" tab I have added a number of additional columns to use with some custom visuals. The data exported to the transactions.csv file does not bring these additional columns in.
In v2.4 I sourced these additional columns directly form the Portfolio Slicer excel worksheet and modified the table in PowerBI to use this data. I could do likewise by using the PortfolioSlicer-Source file as a data source.
My question is would this impact on performance? Or is there a different route forward. I do not want to impact on the measures that you have built.
I have worked on my question today and have successfully modified the GetExcelSrcData file to generate the TransactionsInExcel file with the additonal columns I have created.
I can see that I now need to change the psMakeAllDataFiles file to create the Transactions.csv file. I will work on this, in theory it should not be a problem. Following that I will need to check that running UpdatePSData that there will be no issues.
As an aside, I have noted that if you have "," in the comments filed of PortfolioSlicer-Source.xlsx that this possibly causes issues. I haven't checked this further as I resolved that by replacing all such instances with a space.
Comments
DataRootFolder parameter - if not empty, then this folder should be created. If it is empty, then you do not have to create folder.
PSDataFolder - this folder must be created.
I have created the PSData folder where it should be.
Regarding your error - could you please confirm that your PSData folder is specified correctly.
In the error message you executing script in the folder (starting part):
D:\Documents\PortfolioSlicerV3.0.0\But in the psConfig.txt you specified location (starting part)
D:\Documents\PortfolioSlicerV3\Maybe that is the reason you are getting this error message?
"If at the start of the script you will see an error message that indicates that script is not able to read Excel file, then you will need to install drivers for that:
https://www.microsoft.com/en-gb/download/details.aspx?id=13255"
I note that in your instructions you say
"Run external data management script UpdatePSDataFromExcel.bat. If you see any error messages first confirm that in psConfig.txt you have ExcelSourceFile section with your PortfolioSlicer source data file full path specified. If you still getting an error message about "connection error", then you will need to install Access drivers from Microsoft website here... ."
Okay I have installed the drivers. On the download site it says
"To use this download:
If you are the user of an application, consult your application documentation for details on how to use the appropriate driver.
If you are an application developer using OLEDB, set the Provider argument of the ConnectionString property to “Microsoft.ACE.OLEDB.12.0”
If you are connecting to Microsoft Office Excel data, add the appropriate Extended Properties of the OLEDB connection string based on the Excel file type:
File Type (extension) Extended Properties
---------------------------------------------------------------------------------------------
Excel 97-2003 Workbook (.xls) "Excel 8.0"
Excel 2007-2010 Workbook (.xlsx) "Excel 12.0 Xml"
Excel 2007-2010 Macro-enabled workbook (.xlsm) "Excel 12.0 Macro"
Excel 2007-2010 Non-XML binary workbook (.xlsb) "Excel 12.0"
My question is how do I add "the appropriate Extended Properties of the OLEDB connection string based on the Excel file type:"
I presume it is straight forward as no one else has raised this again. But I have no idea.
Thanks
I believe you should have run _x64.exe
I believe you should have run _x64.exe
Vidas, thanks - that was the problem.
I am now looking to move forward onto V3.1
In my PortfolioSlicer-Source file "Transactions" tab I have added a number of additional columns to use with some custom visuals. The data exported to the transactions.csv file does not bring these additional columns in.
In v2.4 I sourced these additional columns directly form the Portfolio Slicer excel worksheet and modified the table in PowerBI to use this data. I could do likewise by using the PortfolioSlicer-Source file as a data source.
My question is would this impact on performance? Or is there a different route forward. I do not want to impact on the measures that you have built.
Thanks again for your work
I can see that I now need to change the psMakeAllDataFiles file to create the Transactions.csv file. I will work on this, in theory it should not be a problem. Following that I will need to check that running UpdatePSData that there will be no issues.
As an aside, I have noted that if you have "," in the comments filed of PortfolioSlicer-Source.xlsx that this possibly causes issues. I haven't checked this further as I resolved that by replacing all such instances with a space.