Dear all,

I'm trying to read a Excel file in my HMI and process that data in my PLC.

I'm able to read the data from the Excel file. But I got the following problem:

If I need to read a value at row 100k+ its takes 1/2 minutes to find it.

I need something that can loop through the Excel file faster.

The code is in the attachment.

Information:

HMI: KTP400 comfort

PLC: 1200

Kind regards

Hi,

Like Murof wrote WinCe panels are slow.

Since VBS Split does not produce errors e.g from empty line. You can do following and skip one variable. Thats about it.

columnArray = Split(fs.lineInputString, ";")

TToorn

If I need to read a value at row 100k+ its takes 1/2 minutes to find it.

I need something that can loop through the Excel file faster.

It may be better, to store such a huge amount of data not in Excel-Files. A database-based solution seems more reasonable in your case. Working with Excel files gets slower as bigger they get. In my experience, files larger that 10k begin to make problems, where databases stay fast till much bigger data amount.

 Dear,

I was thinking about someting like this. And also about the following:

Read the data from the file once and store in a local DB. That way i only need to read it once and then i can just read the data from the local DB, which is much faster.

The problem I faced was that I don't know exactly whats the best way to store the information in the DB. 

I tried:

An Array ( the problem is I can't create an array bigger than 1500)

creating a own data type of 100K tags 

Just a DB with 100K tags

Is there any other solution for this?

Does anyone have any idea for storing all excel data in a DB in once?

Kind regards

MB Software und Systeme GmbH

 

TToorn

If I need to read a value at row 100k+ its takes 1/2 minutes to find it.

I need something that can loop through the Excel file faster.

It may be better, to store such a huge amount of data not in Excel-Files. A database-based solution seems more reasonable in your case. Working with Excel files gets slower as bigger they get. In my experience, files larger that 10k begin to make problems, where databases stay fast till much bigger data amount.

Hello TTorn,

when you want to copy the information to a DB in the PLC, this TIP: STEP 7 V15: FileReadC/FileWriteC might be an option.

To understand your requirement better:

You have a file with lot of information and you need, e.g. only the information from line 100'000 and nothing else?

Do you need to read the information from the last line?

Regards

Frink

 Dear, 

I will look at the link.

No, I need al the data. So from 0 to 100000. 

And then I need to read 3 columns per row.

Kind regards.

Prof. Frink

Hello TTorn,

when you want to copy the information to a DB in the PLC, this TIP: STEP 7 V15: FileReadC/FileWriteC might be an option.

To understand your requirement better:

You have a file with lot of information and you need, e.g. only the information from line 100'000 and nothing else?

Do you need to read the information from the last line?

Regards

Frink

Murof

But If you will switch to a PC Based Runtime then the Script will also run faster in your current solution...

That is the way! 

For my opinion, you need a PC with a database on it. With this PC may also be able to program a little software that runs on this PC to do all the data handling. As this is my daily work, I would program a little C# Client which handles the database and the S7 on the other side. After all, not an easy way to solve the problem, but the best.

You might think of using a script with an interpreter on the PC instead using a programming language with compiler. This is also possible, but another dependency in the system. Especially if you use VBA + Excel you might run into trouble by future Excel updates that might end in a no longer working script.