Ability to upload shifts, resource schedules

Hi @JonWoster ,

As per my knowledge, I do not think that shifts could be uploaded in Spreadsheet format. But, what you can do is configure a XML according to the Modelling schema which includes rota data & then upload it.

Hope this helps. Thanks!

Thanks Charith, that’s interesting and sounds attractive. Where can we learn more about how that would be done? Are there any examples of that?

Hi @JonWoster ,

I’ve attached the Modelling schema for your reference. There are 2 ways that you could do it as I guess.

1. Prepare a XML which includes rota data referring to the document
2. Configure rota data in PSO & get an export of it. Then modify the exported file so that it includes only the rota data. configure it according to your requirement.

The second option is easier since since the exported data would be up to the modelling schema itself & you do not need to configure it from scratch.

Thanks!

@JonWoster - Not sure if that helps, I always import rota data into my demo system based on an XLS. Have attached it as a working sample. I use a simple VBA script to convert the XLS data into XML and upload this to PSO. If you have a VBA expert, it would probably take him less than a day to develop the upload from Excel to PSO directly.

Thanks for sharing the example, that’s super helpful.

Hi @Charith Fernando

We are looking for the feasibility of upload resource shift planning data in bulk.

I found this topic is related and looking on the Rota.xlsx excel file attached above. 

I have few questions on this topic - 

1. As you mentioned earlier that we can upload bulk shift planning as xml feed. Is it possible, only RAM_Rota_Item data can be uploaded in bulk not other sheets data?
2. Do we have any native feature by which we can convert RAM_Rota_Item data to native xml feed from excel?

Hi @pankaj.roy ,

Good day!

Regarding first point, I do not think that this would be possible. Uploading only this column might not be feasible. You will need to test this.

For the second feature, I guess @Alexander Heinze could provide an answer.

Many thanks!

For #2 I asked ChatGPT “Write me a VBA script that converts a table structure into XML.” and got the response below. No warranties, but a good start. :-)

-------------------------------------------------------------

To convert a table structure into XML using VBA in Microsoft Excel, you can follow these steps:

1. Open Excel and press `Alt + F11` to open the VBA editor.
2. Insert a new module by clicking `Insert` > `Module`.

Here's a VBA script that demonstrates how to convert a simple Excel table into XML:

Sub ConvertTableToXML()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim xmlData As String
    Dim iRow As Long

    ' Set the worksheet containing your table
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet name

    ' Set the table
    Set tbl = ws.ListObjects("Table1") ' Change "Table1" to your table name

    ' Initialize the XML string
    xmlData = "<?xml version=""1.0"" encoding=""UTF-8""?>" & vbCrLf
    xmlData = xmlData & "<data>" & vbCrLf

    ' Loop through each row in the table
    For iRow = 1 To tbl.ListRows.Count
        xmlData = xmlData & "    <record>" & vbCrLf
        For Each col In tbl.ListColumns
            xmlData = xmlData & "        <" & col.Name & ">" & tbl.ListRows(iRow).Range.Cells(1, col.Index).Value & "</" & col.Name & ">" & vbCrLf
        Next col
        xmlData = xmlData & "    </record>" & vbCrLf
    Next iRow

    ' Close the root element
    xmlData = xmlData & "</data>"

    ' Create a new worksheet to store the XML
    Set xmlWs = ThisWorkbook.Sheets.Add
    xmlWs.Name = "XMLData"

    ' Paste the XML into the new worksheet
    xmlWs.Cells(1, 1).Value = xmlData

    ' Format the XML if needed
    xmlWs.Cells(1, 1).EntireColumn.AutoFit

    ' Optional: Save the XML to a file
    xmlWs.SaveAs "C:\YourFolder\YourFile.xml" ' Change the path and filename

    ' Clean up
    Set xmlWs = Nothing

    MsgBox "Table converted to XML and saved!", vbInformation
End Sub

Make sure to customize the code according to your specific table and sheet names. This script creates a new worksheet named "XMLData" to store the XML and optionally saves it to a file. You can modify the file path and name as needed.

To run this script, press `Alt + F8`, select "ConvertTableToXML," and click "Run."

Please be cautious when running VBA macros, especially if they modify your data. Always make a backup of your workbook before running macros.