Question is how to configure one csv file with multiple sheets of data using PI UFL?
A csv file cannot contain multiple sheets. Are you referring to an Excel (xls / xlsx) formatted file?
Thanks for the reply.
I see, yes Excel csv File. Then on it, there's multiple sheets of data but they are all in the same format to be configure in PI UFL. So this will not be able to work in PI UFL?
What is the file extension of your Excel file? CSV files are text files that can be opened and edited in Excel, but there is only ever 1 "sheet". If you add more sheets, save the file, close it, and reopen it, only 1 sheet will remain.
Your file extension is probably XLS, XLSX, XLSM, or XLSB. The PI Interface For UFL and PI Connector For UFL are best used on text files, which a spreadsheet is not but a CSV file is. You will need to save each sheet as a CSV file. First, save your spreadsheet. Click on one of your sheets. Go to File → Save As and save the sheet as a CSV file. At this point, Excel will continue displaying all of the sheets from your original spreadsheet, but you are actually working in the CSV file now, so none of the other sheets are part of the CSV file. However, we'll take advantage of the fact that Excel still shows the other sheets. Select another sheet and repeat the same process. Do this for all of your sheets that you want to save as CSV files.
If all of your sheets are in the same format as each other, it might be a good idea to copy and paste the information from each sheet into a single sheet so then you need to save only 1 CSV file. You could also use formulas to bring all of the information into a single sheet. However, ideally, you wouldn't have multiple sheets in the exact same format and you would have 1 giant table in 1 long sheet to begin with. In general, when working in Excel, it is best, from a data analysis perspective, to avoid having 1 sheet for each month or each equipment or each [something].
I have personally always preferred doing so programmatically, assuming this is a recurring task. I have had great success with this Import-Excel Powershell module GitHub - dfinke/ImportExcel: PowerShell module to import/export Excel spreadsheets, without Excel
To save each sheet as a CSV (and manipulate it as needed). Most of the other automated solution involve manipulating COM objects and installing excel, both of which can be a pain.
Retrieving data ...