Topic: Import from ExcelSubscribe | Previous | Next
Inna Kim 3/20/2014
2097 Views Dear Taylor,
I have two Excel files, when I import from one file and try to overwrite those data with data from another file, Leap still has links with previous file? And when I open Linked Excel files Leap shows that some variables are taken from new file, but other linked with old Excel file. Does it mean that before importing data from new Excel file I need to remove all the links with old Excel file?
Also, when I import my project to Ecxel, which has regions, LEAP doesn't export variables: Exogenous capacity and historical production
Taylor Binnington 3/20/2014
2093 Views Hi Inna,
I'm confused by some of what you've said. When you import data into LEAP from Excel, you can either import expressions as data, or import expressions as links to the original Excel spreadsheet - is this what you mean when you say 'link'? In that case, you'll want to make sure that wherever you see the Excel() function used in your model, it's pointing to the correct spreadsheet document. Read more about the Excel() function here:
http://www.energycommunity.org/WebHelpPro/Expressions/Excel.htm
Otherwise, all that I can suggest is that you read carefully the documentation for both Importing to Excel and Exporting to Excel, here:
http://www.energycommunity.org/WebHelpPro/Supporting_Screens/ImportFromExcel.htm
http://www.energycommunity.org/WebHelpPro/Supporting_Screens/Export_to_Excel.htm
Note that when you export data from a multi-regional LEAP model to an Excel sheet, you should be given an option to export data from either the 'active' region, or all regions.
Good luck,
Taylor
Inna Kim 3/25/2014
2064 Views Dear Taylor,
I apologize for confusing you. What I have done is following:
1. Export from Leap to Excel to get a template
2. Filling in Excel template
3. Import from Excel as links to the original Excel spreadsheet
4. Change some data into the Excel template and save it under different name
5. Import from new Excel file as links to the original Excel spreadsheet
Although second time I made import from different Excel file, some variables are still linked to the first Excel file.
Regarding export to Excel, I did check "all regions" before export and still got nothing in Excel.
Taylor Binnington 3/26/2014
2059 Views Hi Inna,
Regarding the issue with exporting 'All Regions':
In the dialog that appears when you select Analysis: Export to Excel, did you select the 'Autofilter Excel Sheet' option? if this option is selected, then your data will automatically be filtered to display only the Region that was active when you performed the Export. Data for all regions still exist in the spreadsheet, but you need to remove the filters to display them. Let me know if this resolves the problem.
Regarding the issue with renaming Excel documents:
I am unable to reproduce the problem on my own computer, but I have a few follow up questions:
a) Before re-importing the new Excel file (with a different name), did you change anything in the Excel file other than the data in the 'Method' column and the year columns?
b) When performing either of the two imports from Excel, did you select the box 'Values in spreadsheet only replace constants, INTERP, and STEP expressions. GROWTH, REMAINDER and other complex expressions with not be replaced.'?
c) When performing either of the two imports from Excel, did you select the box 'Match branches by name'?
Also, to be certain, please make sure that you have only one Excel document open on your desktop when you perform the import.
Taylor
Inna Kim 3/27/2014
2054 Views Hi Taylor,
Regarding the first question, I still have this problem while fulfilling all those instructions you gave.
Regarding the second question, seems like I did check box 'Values in spreadsheet only replace constants, INTERP, and STEP expressions. GROWTH, REMAINDER and other complex expressions with not be replaced', therefore got some links to the old file.
Taylor Binnington 3/27/2014
2053 Views Hi Inna,
Okay, it sounds like we're half-way there.
Though I still cannot reproduce the problem that you describe when trying to export a multi-region area to Excel.
I've created a simple LEAP area, which I have emailed to you. Open it in the most recent version of LEAP (v0056), and follow these steps carefully (sorry for being too pedantic, I just want to make sure we're doing exactly the same steps):
1) Select the top level "Demand" branch with your mouse.
2) Select Analysis: Export to Excel in LEAP's main menu.
3) In the dialog that appears, select all of the following:
- Branches: Demand and below
- Scenarios: Current Accounts
- Regions: All
- Variables: All
- Data to Export: All
- Format: Values for all years
4) Click Okay, and let me know if you see a line in the resulting Excel spreadsheet for each of the two regions.
Good luck,
Taylor
Inna Kim 3/28/2014
2050 Views Dear Taylor,
Thank you for your fast reply.
In my project figures from demand branch are also exported without any problem.
What I found out that if I have just figure for "Exogenous Capacity" variable in transmission then it is exported to Excel, but if I have time series, then it is not exported.
I added transmision branch in the test project you sent with two power plants (ex. hydro and natural gas) and put some figures in "Exogenous Capacity", then exported to excel, it worked. After I replaced those figures with step function and it didn't work.
Taylor Binnington 3/28/2014
2048 Views Hi Inna,
This sounds like a separate issue, Inna. Let's try to resolve one issue at a time, then we can tackle the issue with exporting time series.
When you perform the steps outlined in my previous post, do you find that data from *both* regions in the model are correctly exported to Excel?
Taylor
Inna Kim 3/29/2014
2042 Views Hi Taylor,
With all the steps you outlined, all data (not a step function thiugh) are exported correctly for both regions.
Inna
Taylor Binnington 3/31/2014
2040 Views Okay, thanks Inna. Now let me try to address the second part of your question:
>> I added transmision branch in the test project you sent with two power plants (ex. hydro and natural gas) and put some figures in "Exogenous Capacity", then exported to excel, it worked. After I replaced those figures with step function and it didn't work.
The test LEAP file that I sent you contained only a single year, in Current Accounts. So if you added a process with an exogenous capacity containing a step function (or any other time-series), the only value that would be exported to Excel is the base year value. If you were also exporting scenarios that go many years into the future, then values in each year in your Excel document would reflect any changes that you impose using the Step() function.
Does this answer your question?
Taylor
Inna Kim 3/31/2014
2030 Views Hi Taylor,
Have you tried to reproduce the case I described? Your answer didn't help, since what I get in Excel are just zeros in all scenarios.
Taylor Binnington 4/2/2014
2026 Views Hi Inna,
I went through the steps which you described, but can not reproduce the same problem that you are seeing.
Can you send me a copy of the *.leap file for which you're seeing this problem (to leap@sei-us.org)? The simpler the file the better. Send me the file that is giving you unexpected results (i.e. with the time series function entered, which is giving you a row of zeroes when exported to Excel).
Please also let me know the operating system and version that you're using, and the Microsoft Excel version that you have installed.
Thanks again,
Taylor
Inna Kim 4/3/2014
2020 Views Hi Taylor,
I really appreciate your help.
I have tried dozen of cases by now.
Everything got exported and imported well now. Seems like I got this problem because I used comma instead of dot as decimal separator, and semicolon instead of comma for data separation.
Thank you for your help.
Inna
Taylor Binnington 4/3/2014
2017 Views Interesting - thank you for posting this, Inna!
I know that the regional settings on a computer have been known to impact LEAP (in exactly the way that you just said), but I didn't think that would be the cause here. Glad you've figured it out!
Taylor