LK Script to Exchange Data with Excel

Start
Prev
1
Next
End

Clara SERVOIS
Topic Author

19 Jan 2016 17:32 #4019 by Clara SERVOIS

LK Script to Exchange Data with Excel was created by Clara SERVOIS

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

Please Log in or Create an account to join the conversation.

Paul Gilman

19 Jan 2016 17:43 - 25 Oct 2019 18:37 #4020 by Paul Gilman

Replied by Paul Gilman on topic LK Script to Exchange Data with Excel

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.

Last edit: 25 Oct 2019 18:37 by Paul Gilman.

Please Log in or Create an account to join the conversation.

Paul Gilman

20 Jan 2016 21:44 - 25 Oct 2019 18:42 #4021 by Paul Gilman

Replied by Paul Gilman on topic LK Script to Exchange Data with Excel

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

xl_get(xlObject,'MyData')

instead of

xl_get(xlObject,'D5:D8')

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

0,5,3,14

would be read as the string

"0 5 3 14"

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:

xlObject = xl_create;<br />
   xl_open( xlObject , "c:\testworksheet.xlsx" );<br />
   data_str = xl_get( xlObject , "MyData" ); // string of space-delimited values<br />
   xl_close(xlObject);<br />
   xl_free(xlObject);<br />
   data = real_array( data_string ); // array of numbers

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

Best regards,
Paul.

Last edit: 25 Oct 2019 18:42 by Paul Gilman.

Please Log in or Create an account to join the conversation.

Start
Prev
1
Next
End

Moderators: Paul Gilman

NREL is a national laboratory of the U.S. Department of Energy, Office of Energy Efficiency and Renewable Energy, operated by the Alliance for Sustainable Energy, LLC.

NREL Home

NREL Research

Follow NREL

NREL is a national laboratory of the U.S. Department of Energy, Office of Energy Efficiency and Renewable Energy, operated by the Alliance for Sustainable Energy, LLC.