LK Script to Exchange Data with Excel

Hi !

I am trying to use an LK script code to exchange data between excel and SAM (the idea is to pre-size my PV system with excel, export the main outputs data to SAM and use the simulation to asses my system.

I have issue on importing the load profile on SAM. I want to import the load hour by hour, for each hour of the year, in the Energy Usage data.

For that, I created the following loop which seems to be okay when I simulates :

for( i=5; i<8766; i++) {

index='L'+i;
Energy_usage[i-5]= xl_get(xl,index);
outln(index);

}

L is the letter where the load are on excel, going from 5 to 8766
Energy_usage is how I named the data I want to extract from excel.

But after, I tried to link this Energy_usage to the load_user_data with this :

set('load_user_data',Energy_usage);

But the following error message appears :
"equation engine: [62]: reference to unassigned variable: count\

equation engine: [62]: failed to evaluate function call argument 1 to 'msgbox()'\

fail: [load,load_annual_total,annual_peak,energy_1,peak_1,energy_2,peak_2,energy_3,peak_3,energy_4,peak_4,energy_5,peak_5,energy_6,peak_6,energy_7,peak_7,energy_8,peak_8,energy_9,peak_9,energy_10,peak_10,energy_11,peak_11,energy_12,peak_12] = f( load_model,load_user_data,normalize_to_utility_bill,utility_bill_data,scale_factor )
no variables calculated in a single iteration, cannot make progress!
eqn not evaluated: [load,load_annual_total,annual_peak,energy_1,peak_1,energy_2,peak_2,energy_3,peak_3,energy_4,peak_4,energy_5,peak_5,energy_6,peak_6,energy_7,peak_7,energy_8,peak_8,energy_9,peak_9,energy_10,peak_10,energy_11,peak_11,energy_12,peak_12] = f( load_model,load_user_data,normalize_to_utility_bill,utility_bill_data,scale_factor)"

I understood that load_user_data is an array type of variable, so I tried to do the same for Energy_usage, but I really do not see how to move forward,

if someone have a clue on that, it would really help me !

Thanks,

Clara

Hi Clara,

It's hard to know for sure without seeing your entire script, but here are a couple of ideas:

You might want to read the data from a range of cells in Excel into an array in one step so that you call the xl_get() function once instead of looping through the spreadsheet cells. The named range will make it possible to move things around in the spreadsheet without breaking your LK code.

You might want to check the data type of the values lk_get() is reading from Excel. If they are being read as strings, then the call to the set() function will fail because the load_user_data variable is an array of numbers.

You can use the to_real() function to convert a string to a number.

If you still have trouble, please [url=mailto:sam.support@nrel.gov?subject=LK%20Script%20Question t=_self]email me[/url] a copy of your LK script with the Excel file you are reading.

Best regards,
Paul.

Hi Clara,
Thank you for emailing me your script. It's a little tricky in LK to read a column of data from an Excel file. Here's what you need to know to make it work properly:

For the xl_get() function, use a named range in your Excel worksheet to refer to the column instead of cell references. For example, if the worksheet contains the values 0,5,3,14 in cells D5:D8, give that range of cells a name like MyData so that in LK, you can write

instead of
The xl_get() function reads data from a range of cells as a string of space-delimited values. For example, the value

would be read as the string
You can use the real_array() function to convert the string into an array of numbers.

 Here's an example, assuming the Excel file testworksheet.xlsx has a range of cells named "MyData" that contain numbers:


I emailed you a version of your script with the above changes that should work.

Best regards,
Paul.