How to create a sub program to read data from excel in ZPL?Does zemax have this function?
Solved
read data from excel
Best answer by David.Nguyen
Accessing external files in ZPL is limited to text files with a specific format. If we take the READ keyword for example, this is an excerpt of the Help File:
The file must be already open, see the keyword OPEN for details. Each READ command reads a single line from the file. The first valid data field from this line is placed in the variable first listed. The data from the second field is placed in the second variable listed, if any.
Therefore, the number of variables listed in the read command should match the number of columns in the text file. Numeric data in the file should be delimited by spaces.
The data may be in free-form, and is internally promoted to double precision. In order to use decimal separator currently selected in Windows settings use READ_LOCALE keyword. A maximum of 2000 characters can be read in on any single line. The maximum number of variable arguments is 199; for reading longer lines with more arguments use READNEXT instead. The variables listed must be valid ZPL variable names.
You could easily save your Excel file as a comma-separated value file (*.CSV), but since the format for ZPL should be that: numeric data in the file should be delimited by spaces. I am not sure if it will work :( let us know if you test this.
Another option is to use the ZOS-API to have a direct connection between OpticStudio and Excel as described by
I hope this helps.
Take care,
David
+2
Accessing external files in ZPL is limited to text files with a specific format. If we take the READ keyword for example, this is an excerpt of the Help File:
The file must be already open, see the keyword OPEN for details. Each READ command reads a single line from the file. The first valid data field from this line is placed in the variable first listed. The data from the second field is placed in the second variable listed, if any.
Therefore, the number of variables listed in the read command should match the number of columns in the text file. Numeric data in the file should be delimited by spaces.
The data may be in free-form, and is internally promoted to double precision. In order to use decimal separator currently selected in Windows settings use READ_LOCALE keyword. A maximum of 2000 characters can be read in on any single line. The maximum number of variable arguments is 199; for reading longer lines with more arguments use READNEXT instead. The variables listed must be valid ZPL variable names.
You could easily save your Excel file as a comma-separated value file (*.CSV), but since the format for ZPL should be that: numeric data in the file should be delimited by spaces. I am not sure if it will work :( let us know if you test this.
Another option is to use the ZOS-API to have a direct connection between OpticStudio and Excel as described by
I hope this helps.
Take care,
David
Thank you very much for your reply. I used READ. In fact, READ seems to be able to read only one row of data. Is there a way to read a certain row of multiple rows of data?
+2Strange, this works for me. If I use a text file with two lines like so:
1.23 4.56
7.89 9.99The macro:
OPEN "E:\read_from_zpl.txt"
READ xx, yy
READ zz, aa
CLOSE
PRINT "First value first row: ", xx
PRINT "Second value first row: ", yy
PRINT "First value second row: ", zz
PRINT "Second value second row: ", aaReturns:
Can you share an example that doesn’t work for you so we can have a look at it?
Take care,
David
David
There are tens of thousands of lines of data in my txt file. I want to read some specific lines from the middle. For example, I want to read the 800th line. Do you have any good suggestions?
+2
It is hard to give you a precise advice without a clearer picture of what you want to achieve ultimately but I suggest to split your task. First, don’t rely on ZPL to parse your text file if it hasn’t been curated beforehand, there are much better tools for this purpose. Try using Python to curate your data and save only what is necessary for OpticStudio using the syntax described in the Help File for the READ keyword. You will find plenty of examples on how to parse text file on the internet (try looking in StackOverflow). Depending on how complex is your ultimate task, you could work with the ZOS-API from the beginning. That way you can have part of your code dedicated to parsing the data (without interacting with OpticStudio), and then pass that data to OpticStudio for use within your lens file. Does that make sense?
Take care,
David
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
Need more help?
To Chinese users:
Do not provide any information or data that is restricted by applicable law, including by the People’s Republic of China’s Cybersecurity and Data Security Laws ( e.g., Important Data, National Core Data, etc.).
不要提供任何受适用法律，包括中华人民共和国的网络安全和数据安全法限制的信息或数据（如重要数据、国家核心数据等）。