Excel doc import into tables for player numbers, names, positions

Automation & Control
1

I am trying to make it easy for Graphics operator to receive .csv file and import it into a table that looks like so.

I have read some other posts about file pickers and am not quite understanding how to construct the table.

867×534 25.9 KB

Thanks for your help!

2

Hi Shawn!

You can use the following OGLML code template to import the csv file into your table:

//Start of example

cols.0 cols.1 cols.2 cols.3 cols.4 cols.5 cols.6 cols.7 cols.8 cols.9 cols.10 cols.11 cols.12 cols.13 cols.14 cols.15 cols.16 function processCSV(fileContent) { if (fileContent == null) { ogscript.debug("CAN'T LOAD FILE"); return; }

var rows = fileContent.split(‘\n’);
var dataByColumn = ; //It is more efficient if we only call “SET” on the parameters once

for (var r = 0; r < rows.length; r++)
{
var cols = rows[r].trim().split(‘,’);
for (var c = 0; c < cols.length; c++)
{
if (c >= dataByColumn.length)
{
dataByColumn.push(); //Add a new column if it wasn’t already created
}
dataByColumn[c][r] = cols[c].trim(); //Push the cell’s value into the array of column data at row “r”
}
}

//Now that we have collected all of the data for each column into an array, we can call setAllValues for the array parameter
for (var c = 0; c < dataByColumn.length; c++)
{
params.setAllValues(‘cols.’ + c, dataByColumn[c]); //Set all of the values in the column
}
}

ogscript.asyncPost(‘CSV Reader Data.csv’, null, processCSV);

//End of example

Feel free to reach out if you have any more questions!

Cheers

3

This did not do what I wanted to have. It created one table of 16 columns.

I am looking to create a way to "Browse"a files either xml or csv. To then import the values in the file into a table that consists of a jersey number, name, and position.

I need to do this for a home team and an away team. I tried using the xml table loader in the example setsunder Reading and Writing Files or Directories. I cannot figure out how to change parameters and seperate them to make a home team and away team. In the parameters I would like to have (a.jersey ; a.name ; a.position) for the away team and (h.jersey ; h.name ; h.position) for the home team. These perameters are used also to fill in a Lower 3rd in Xpression.

979×715 13.6 KB

Please help ASAP

Thanks

4

Hi Shawn
Do you have an example of the CSV or XML file/files you’ll be using?

5

Here is an example of the h.roster and a.roster

6

Hi Shawn,

Here is a custom panel grid that has two tables reading data from two different XML files which follow the pattern you attached. Here we make use of ogscript.runXPath() method after parsing the XML.

away_selection var xmlDocument = ogscript.parseXML(params.getValue("home_filename", 0)); //Parse the XML var nodeList = xmlDocument.getElementsByTagName("player"); //Get the tags we want to include in the constraint

var homePlayerData = new Array();

var jerseyList = ogscript.runXPath(‘/roster/player/h.jersey’, xmlDocument);
var nameList = ogscript.runXPath(‘/roster/player/h.name’, xmlDocument);
var positionList = ogscript.runXPath(‘/roster/player/h.position’, xmlDocument);

for (var i = 0; i < nodeList.getLength(); i++)
{
var currentJersey = “”;
currentJersey = currentJersey + jerseyList.item(i).getTextContent();

var currentName = “”;
currentName = currentName + nameList.item(i).getTextContent();

var currentPosition = “”;currentPosition = currentPosition + positionList.item(i).getTextContent();

if(currentJersey != “” || currentName != “” || currentPosition != “”)
{
homePlayerData.push({“jersey”: currentJersey, “name”: currentName,“position”: currentPosition});
}
}

params.setAllValues(“home_players”, homePlayerData);

var homePlayerData = new Array(); homePlayerData.push({"jersey": 0, "name": "No Data Found","position": ""}); params.setAllValues("home_players", homePlayerData); var xmlDocument = ogscript.parseXML(params.getValue("away_filename", 0)); //Parse the XML var nodeList = xmlDocument.getElementsByTagName("player"); //Get the tags we want to include in the constraint

var awayPlayerData = new Array();

var jerseyList = ogscript.runXPath(‘/roster/player/a.jersey’, xmlDocument);
var nameList = ogscript.runXPath(‘/roster/player/a.name’, xmlDocument);
var positionList = ogscript.runXPath(‘/roster/player/a.position’, xmlDocument);

for (var i = 0; i < nodeList.getLength(); i++)
{
var currentJersey = “”;
currentJersey = currentJersey + jerseyList.item(i).getTextContent();

var currentName = “”;
currentName = currentName + nameList.item(i).getTextContent();

var currentPosition = “”;
currentPosition = currentPosition + positionList.item(i).getTextContent();

if(currentJersey != “” || currentName != “” || currentPosition != “”)
{
awayPlayerData.push({“jersey”: currentJersey, “name”: currentName,“position”: currentPosition});
}
}

params.setAllValues(“away_players”, awayPlayerData);

var awayPlayerData = new Array(); awayPlayerData.push({"jersey": 0, "name": "No Data Found","position": ""}); params.setAllValues("away_players", awayPlayerData); away_selection

I hope this helps. Please reach out if you have any other questions.

Best Regards,

7

I put this into my code, and it worked great! Except for the Away team read button, it is not populating the Away table.

away_selection var xmlDocument = ogscript.parseXML(params.getValue("home_filename", 0)); //Parse the XML var nodeList = xmlDocument.getElementsByTagName("player"); //Get the tags we want to include in the constraint

var homePlayerData = new Array();

var jerseyList = ogscript.runXPath(‘/roster/player/h.jersey’, xmlDocument);
var nameList = ogscript.runXPath(‘/roster/player/h.name’, xmlDocument);
var positionList = ogscript.runXPath(‘/roster/player/h.position’, xmlDocument);

for (var i = 0; i < nodeList.getLength(); i++)
{
var currentJersey = “”;
currentJersey = currentJersey + jerseyList.item(i).getTextContent();

var currentName = “”;
currentName = currentName + nameList.item(i).getTextContent();

var currentPosition = “”;currentPosition = currentPosition + positionList.item(i).getTextContent();

if(currentJersey != “” || currentName != “” || currentPosition != “”)
{
homePlayerData.push({“jersey”: currentJersey, “name”: currentName,“position”: currentPosition});
}
}

params.setAllValues(“home_players”, homePlayerData);



var homePlayerData = new Array();
homePlayerData.push({“jersey”: 0, “name”: “No Data Found”,“position”: “”});
params.setAllValues(“home_players”, homePlayerData);


var xmlDocument = ogscript.parseXML(params.getValue(“away_filename”, 0)); //Parse the XML
var nodeList = xmlDocument.getElementsByTagName(“player”); //Get the tags we want to include in the constraint

var awayPlayerData = new Array();

var jerseyList = ogscript.runXPath(‘/roster/player/a.jersey’, xmlDocument);
var nameList = ogscript.runXPath(‘/roster/player/a.name’, xmlDocument);
var positionList = ogscript.runXPath(‘/roster/player/a.position’, xmlDocument);

for (var i = 0; i < nodeList.getLength(); i++)
{
var currentJersey = “”;
currentJersey = currentJersey + jerseyList.item(i).getTextContent();

var currentName = “”;
currentName = currentName + nameList.item(i).getTextContent();

var currentPosition = “”;
currentPosition = currentPosition + positionList.item(i).getTextContent();

if(currentJersey != “” || currentName != “” || currentPosition != “”)
{
awayPlayerData.push({“jersey”: currentJersey, “name”: currentName,“position”: currentPosition});
}
}

params.setAllValues(“away_players”, awayPlayerData);



var awayPlayerData = new Array();
awayPlayerData.push({“jersey”: 0, “name”: “No Data Found”,“position”: “”});
params.setAllValues(“away_players”, awayPlayerData);



away_selection


ogscript.debug(‘Starting to Create Dropdowns’);
ogscript.debug(‘HomeNames.length:’ + HomeNames.length);
ogscript.debug(‘AwayNames.length:’ + AwayNames.length);


8

Hi Shawn,

Thanks for using and confirming the functionality of the home table. I used the grid panel code from the previous message and it works fine and the code looks good too. Can you please confirm that the XML file that you are using for the away table follows the pattern of the a.Roster.xml that you attached previously?

Best Regards,

9

It was formatted differently. I made the change and now it works! Thanks!

I appreciate your help as well through all of this!

10

Hi Shawn,

Thanks for confirming! Glad I could help.

Best Regards,

11

Altaz,

I am now trying to get my table parameters to connect to select dropdowns to be used for lower 3rds and the parameters that are used in the table are not showing up in my list of parameters that I already have existing. How do I get them to merge?

I am really lost here, thank you!

12

Hi Shawn,

Can you please help me with what you need in the dropdowns? Should it be just the Jersey number or should it be appended string of JerseyNumber + Name + Position as one option?

Best Regards,