A question about GET XLS DATA

Use this forum to post Vensim related questions.
Post Reply
kong17
Senior Member
Posts: 60
Joined: Wed May 30, 2018 1:12 pm
Vensim version: PRO

A question about GET XLS DATA

Post by kong17 »

Hello everyone,

I try to import database into Vensim, it awalys report an error said Unable to parse the data file.
The reason might be that when importing database, Vensim did not recognize the data's name.
But, in the excel, the name is set. I have tried many times and it still doesn't work.
So I was wondering what's wrong with that?
Forgive me for asking a very simple question.

Thanks
Error.png
Error.png (4.5 KiB) Viewed 5640 times
Error1.png
Error1.png (12.79 KiB) Viewed 5640 times
Error3.png
Error3.png (4.8 KiB) Viewed 5640 times
Administrator
Super Administrator
Posts: 4573
Joined: Wed Mar 05, 2003 3:10 am

Re: A question about GET XLS DATA

Post by Administrator »

Can you upload the Excel file?
Advice to posters seeking help (it really helps us to help you)
http://www.ventanasystems.co.uk/forum/v ... f=2&t=4391

Units are important!
http://www.bbc.co.uk/news/magazine-27509559
kong17
Senior Member
Posts: 60
Joined: Wed May 30, 2018 1:12 pm
Vensim version: PRO

Re: A question about GET XLS DATA

Post by kong17 »

Administrator wrote: Thu May 07, 2020 12:05 pm Can you upload the Excel file?
Input.xlsx
(11.51 KiB) Downloaded 200 times
This is the Excel file
Administrator
Super Administrator
Posts: 4573
Joined: Wed Mar 05, 2003 3:10 am

Re: A question about GET XLS DATA

Post by Administrator »

I managed to get it to import. Edit cells A1 and A2 and delete the ' from the start of the cell. Save and now try importing.

What version of Excel did you create this spreadsheet with?
Advice to posters seeking help (it really helps us to help you)
http://www.ventanasystems.co.uk/forum/v ... f=2&t=4391

Units are important!
http://www.bbc.co.uk/news/magazine-27509559
kong17
Senior Member
Posts: 60
Joined: Wed May 30, 2018 1:12 pm
Vensim version: PRO

Re: A question about GET XLS DATA

Post by kong17 »

Administrator wrote: Thu May 07, 2020 12:36 pm I managed to get it to import. Edit cells A1 and A2 and delete the ' from the start of the cell. Save and now try importing.

What version of Excel did you create this spreadsheet with?
I was wondering did you mean delete the A1 and A2, then try importing. If yes, it is still an error, said: Unable to read the file input.xlsx as an Excel file (sst)..
The version of Excel is Microsoft 365 for enterprise.
Administrator
Super Administrator
Posts: 4573
Joined: Wed Mar 05, 2003 3:10 am

Re: A question about GET XLS DATA

Post by Administrator »

ok, that spreadsheet has some extra info attached to the cells A1 and A2 which are causing problems when importing. It will be fixed in the next release.

If you edit cell A1, the contents are 'Time, delete the ' from the start of it.
Advice to posters seeking help (it really helps us to help you)
http://www.ventanasystems.co.uk/forum/v ... f=2&t=4391

Units are important!
http://www.bbc.co.uk/news/magazine-27509559
kong17
Senior Member
Posts: 60
Joined: Wed May 30, 2018 1:12 pm
Vensim version: PRO

Re: A question about GET XLS DATA

Post by kong17 »

Administrator wrote: Thu May 07, 2020 4:32 pm ok, that spreadsheet has some extra info attached to the cells A1 and A2 which are causing problems when importing. It will be fixed in the next release.

If you edit cell A1, the contents are 'Time, delete the ' from the start of it.
Sorry, I don't really understand edit cell. Especially the contents are 'Time, delete the' from the start of it.
Do you mean the content of cell A1 is 'Time, delete the', but Excel hides ', delete the' ?
But, I was wondering which interface can enter the edit cell that you said?
I only find the format cells but it doesn't edit content
Error.png
Error.png (9.41 KiB) Viewed 5616 times
tomfid
Administrator
Posts: 3804
Joined: Wed May 24, 2006 4:54 am

Re: A question about GET XLS DATA

Post by tomfid »

You could just save it as csv or tab and import that.
Administrator
Super Administrator
Posts: 4573
Joined: Wed Mar 05, 2003 3:10 am

Re: A question about GET XLS DATA

Post by Administrator »

You could also try saving it in an older format (XLS file).

Although you cannot see any issues, the text cells have geographical data embedded within them, I'm not sure how you would remove it.
Advice to posters seeking help (it really helps us to help you)
http://www.ventanasystems.co.uk/forum/v ... f=2&t=4391

Units are important!
http://www.bbc.co.uk/news/magazine-27509559
kong17
Senior Member
Posts: 60
Joined: Wed May 30, 2018 1:12 pm
Vensim version: PRO

Re: A question about GET XLS DATA

Post by kong17 »

Administrator wrote: Thu May 07, 2020 6:40 pm You could also try saving it in an older format (XLS file).

Although you cannot see any issues, the text cells have geographical data embedded within them, I'm not sure how you would remove it.
Yes, it works when use an older format. The data can import into Vensim.
But, when I run the model, the error said it cannot read data from the excel.
Error.png
Error.png (9.41 KiB) Viewed 5608 times
I have uploaded the simple model and data. Please check what's wrong with this?
Thank you very much!
Simple model.mdl
(2.61 KiB) Downloaded 178 times
INPUT DATA.xls
(23 KiB) Downloaded 167 times
Administrator
Super Administrator
Posts: 4573
Joined: Wed Mar 05, 2003 3:10 am

Re: A question about GET XLS DATA

Post by Administrator »

Change the equation for DATA from
GET XLS DATA('INPUT DATA.xlsx', 'Sheet1' , '1' , 'B2' )
to
GET XLS DATA('INPUT DATA.xls', 'Sheet1' , '1' , 'B2' )
Advice to posters seeking help (it really helps us to help you)
http://www.ventanasystems.co.uk/forum/v ... f=2&t=4391

Units are important!
http://www.bbc.co.uk/news/magazine-27509559
kong17
Senior Member
Posts: 60
Joined: Wed May 30, 2018 1:12 pm
Vensim version: PRO

Re: A question about GET XLS DATA

Post by kong17 »

Administrator wrote: Thu May 07, 2020 7:18 pm Change the equation for DATA from
GET XLS DATA('INPUT DATA.xlsx', 'Sheet1' , '1' , 'B2' )
to
GET XLS DATA('INPUT DATA.xls', 'Sheet1' , '1' , 'B2' )
It doesn't work, report same error.
tomfid
Administrator
Posts: 3804
Joined: Wed May 24, 2006 4:54 am

Re: A question about GET XLS DATA

Post by tomfid »

If it's reporting "does not exist" you probably have a path error. Change the equation to GET XLS DATA('?mydata', 'Sheet1' , '1' , 'B2' ) and it will query you to locate the file when you run the model. (Be sure that it's not open in Excel, because Excel locks the file.)
kong17
Senior Member
Posts: 60
Joined: Wed May 30, 2018 1:12 pm
Vensim version: PRO

Re: A question about GET XLS DATA

Post by kong17 »

tomfid wrote: Thu May 07, 2020 7:44 pm If it's reporting "does not exist" you probably have a path error. Change the equation to GET XLS DATA('?mydata', 'Sheet1' , '1' , 'B2' ) and it will query you to locate the file when you run the model. (Be sure that it's not open in Excel, because Excel locks the file.)
When running the model, excell will be popped up, then report an error.
Said Failed to connect Excel to get data from INPUT DATA.xls
Error1.png
Error1.png (8.63 KiB) Viewed 5604 times
kong17
Senior Member
Posts: 60
Joined: Wed May 30, 2018 1:12 pm
Vensim version: PRO

Re: A question about GET XLS DATA

Post by kong17 »

tomfid wrote: Thu May 07, 2020 7:44 pm If it's reporting "does not exist" you probably have a path error. Change the equation to GET XLS DATA('?mydata', 'Sheet1' , '1' , 'B2' ) and it will query you to locate the file when you run the model. (Be sure that it's not open in Excel, because Excel locks the file.)
I try to use my laptop to run the model. Very strange.
The same model, data and operation steps, the error is reported on the win system of my PC, but it can be run on the mac system of the laptop.
tomfid
Administrator
Posts: 3804
Joined: Wed May 24, 2006 4:54 am

Re: A question about GET XLS DATA

Post by tomfid »

I'm disenchanted with the cloud versions of Excel, which seem to get flakier with each update. I've switched my GET XLS protocol to Direct (on the Settings tab of Tools>Options). That makes GET XLS work like GET DIRECT. The downside is that the file can't be open in Excel. The benefit is that these problems don't happen, and you don't have to have Excel on the target machine at all.
kong17
Senior Member
Posts: 60
Joined: Wed May 30, 2018 1:12 pm
Vensim version: PRO

Re: A question about GET XLS DATA

Post by kong17 »

tomfid wrote: Thu May 07, 2020 8:56 pm I'm disenchanted with the cloud versions of Excel, which seem to get flakier with each update. I've switched my GET XLS protocol to Direct (on the Settings tab of Tools>Options). That makes GET XLS work like GET DIRECT. The downside is that the file can't be open in Excel. The benefit is that these problems don't happen, and you don't have to have Excel on the target machine at all.
Yes. It works on the windows.
Thank you for your help.
Post Reply