MP277, interactive databasing with csv file's

[userxyz]

Order

For the orders I use wincc flex...

Each order has products (records) not much. the orders (recipes) can be saved (exported to csv) and fetched (imported from csv).

All in wincc flex. This works fine. If they doe 10 orders / day (is not much) then you get 3650 flat file recipes stored in csv format. Can this be a problem ? Making folders is possible in vb-script if too many files in folder.

For the corrections, same logic, but even more then the orders I think.



Ow, now I see, nonooo, not the flat file's have so many records !!! I'm building the corrections a little different. Each correction (record) = a flat file, while the ordersystem in the office works with the recipes form wincc flex.

Flat files with 10000+ records ?
Even in an office environment I dont like this very much.
What software are you using in the office to manage this ?

 

[JesperMP]

If it works for you, then fine.
But I would not do it that way.

 

[userxyz]

not many options

I do not have many options, they need such thing and I cannot spend more money on databasetools.

But do you think it will be a problem to work with flat files, have you bad experienced with this or so ???

Why not this way if it can be free

the only thing I can't test is, 10000 csv's in a dir, how windows ce is going to react, will it load the csv I'm asking, will it have troubles... my only concern at the moment

If it works for you, then fine.
But I would not do it that way.

 

[JesperMP]

You are not really explaining how it actually works.
In case Excel is used, then the limit is 65,536 rows by 256 columns.
Even so, it is easy to goof up, make double entries and so forth.
And what if 2 persons must work on the data at the same time ?
If this is something that integrates into the production ordering system of a medum-sized factory, it shouldnt be taken lightly. Whoever that specify such requirements must expect to pay what it costs.
With a home-grown system, the costs for creating and maintaining the application must be factored in. So maybe it isnt cheaper at all in the long run.
For example, FactorySQL costs 2000 USD. Thats about 1 week engineering time.
My stance on VB scripts and this is that it should be kept to a minimum, and should definitely not be used for stuff that is critical.

 

[userxyz]

hmm

I'll send you the project in private, if you have the time, then take a look. Download all in a PLC. Run the PC_Runtime. There you can make products. When made, you'll get C:\S:\ORDERS\xxx.csv file's.

In the MP277 you can see the scripts, the save script works, the fetch not. The historian works too, but you cannot test this because it's windows ce.

You are not really explaining how it actually works.
In case Excel is used, then the limit is 65,536 rows by 256 columns.
Even so, it is easy to goof up, make double entries and so forth.
And what if 2 persons must work on the data at the same time ?
If this is something that integrates into the production ordering system of a medum-sized factory, it shouldnt be taken lightly. Whoever that specify such requirements must expect to pay what it costs.
With a home-grown system, the costs for creating and maintaining the application must be factored in. So maybe it isnt cheaper at all in the long run.
For example, FactorySQL costs 2000 USD. Thats about 1 week engineering time.
My stance on VB scripts and this is that it should be kept to a minimum, and should definitely not be used for stuff that is critical.

 

[JesperMP]

Combo, do you expect me to use my time with that, just to take a look at it ? Sorry, but I dont think so.
I do not doubt that what you have created before worked as you described.
As you describe it, the reason for you trying to cram it into an MP277 is that noone reserved the money needed to make it the right way.

 

[userxyz]

yep

Okay, nevermind then, I tought you were interested in how it worked, sorry.

We started with a simple batching thing: length, diameter and corner. No recipes, nothing. Then the customer wants an extra, and again an extra, and another extra, sorting, historian, etc..., it had grown, and I agree, a little too big for wincc flex and using flat files. If I knew what I know now, I would've used other tools. I wouldn't create it, more an IT'guy. Indeed, money, allways the same priority.

I have no choice now, continu with what I have, and search why the FETCH code doesn't work

Combo, do you expect me to use my time with that, just to take a look at it ? Sorry, but I dont think so.
I do not doubt that what you have created before worked as you described.
As you describe it, the reason for you trying to cram it into an MP277 is that noone reserved the money needed to make it the right way.

 

[userxyz]

the line missing

found the error:

the line f.open (file_name_path), 1 is missing in the read script

Okay, in an other topic I was searching for saving and fetching data out of a database. I was doing it in a PLC, but it eated all the retentive memory of the CPU.

So the idea is writing to csv and reading from it.

I have 5 correctionparameters that I save and retrieve. I write them on a buttonevent and read them when the productcode changes.


This is the write script:

Dim f, fs, file_path, file_name, file_name_path
On Error Resume Next
Set fs = CreateObject("filectl.filesystem")
Set f = CreateObject("filectl.file")
file_path= "\Storage Card MMC\"
file_name= CStr(SmartTags("ACTIVE BATCH.ORDER_DATABASE.ARTIKEL_NUMMER"))& ".csv"
file_name_path= file_path & file_name
If Err.Number <> 0 Then
 ShowSystemAlarm "Error#" & CStr(Err.Number)&""& Err.Description
 Err.Clear
 Exit Sub
End If 
f.open (file_name_path), 2  
f.Lineprint (SmartTags("INSTELLING PONSUNIT.CORRECTIES.LINKS.ECS") & ";") 
f.Lineprint (SmartTags("INSTELLING PONSUNIT.CORRECTIES.LINKS.HOEK") & ";")
f.Lineprint (SmartTags("INSTELLING PONSUNIT.CORRECTIES.LINKS.HOOGTE") & ";")
f.Lineprint (SmartTags("INSTELLING PONSUNIT.CORRECTIES.RECHTS.ECS") & ";")
f.Lineprint (SmartTags("INSTELLING PONSUNIT.CORRECTIES.RECHTS.HOEK") & ";")
f.Close
 
Set f = Nothing
Set fs = Nothing
 
If Err.Number <> 0 Then
 ShowSystemAlarm "Error#" & CStr(Err.Number)&""& Err.Description
 Err.Clear
 Exit Sub
End If 
ShowSystemAlarm "Corrections storage was successful"

So I print 5 line's in a csv, the csv is named after the product (artikelnummer). This is done for retrieving easely.


This is the read script:

Dim f, fs, file_path, file_name, file_name_path, DataSet,MyZf, field, HiField, i, j
On Error Resume Next
Set fs = CreateObject("filectl.filesystem")
Set f = CreateObject("filectl.file")
file_path= "\Storage Card MMC\"
file_name= CStr(SmartTags("ACTIVE BATCH.ORDER_DATABASE.ARTIKEL_NUMMER"))& ".csv"
file_name_path= file_path & file_name
If Err.Number <> 0 Then
 ShowSystemAlarm "Error#" & CStr(Err.Number)&""& Err.Description
 Err.Clear
 Exit Sub
End If 
If fs.dir(file_name_path)="" Then
 SmartTags("INSTELLING PONSUNIT.CORRECTIES.LINKS.ECS") = 0 
 SmartTags("INSTELLING PONSUNIT.CORRECTIES.LINKS.HOEK") = 0
 SmartTags("INSTELLING PONSUNIT.CORRECTIES.LINKS.HOOGTE") = 0
 SmartTags("INSTELLING PONSUNIT.CORRECTIES.RECHTS.ECS") = 0
 SmartTags("INSTELLING PONSUNIT.CORRECTIES.RECHTS.HOEK") = 0
Else
 Do While f.eof=False
  MyZf=f.lineinputstring
  field=Split(MyZf,";")
  For i= 0 To 4
   field(i)= Replace(field(i),"","")
   HiField(j,i)=field(i)
  Next
 SmartTags("INSTELLING PONSUNIT.CORRECTIES.LINKS.ECS") = HiField(0,1) 
 SmartTags("INSTELLING PONSUNIT.CORRECTIES.LINKS.HOEK") = HiField(1,1)
 SmartTags("INSTELLING PONSUNIT.CORRECTIES.LINKS.HOOGTE") = HiField(2,1)
 SmartTags("INSTELLING PONSUNIT.CORRECTIES.RECHTS.ECS") = HiField(3,1)
 SmartTags("INSTELLING PONSUNIT.CORRECTIES.RECHTS.HOEK") = HiField(4,1)  
  j=j+1
 Loop
f.close
End If
Set f = Nothing
Set fs = Nothing
 
If Err.Number <> 0 Then
 ShowSystemAlarm "Error#" & CStr(Err.Number)&""& Err.Description
 Err.Clear
 Exit Sub
End If 
ShowSystemAlarm "Corrections fetching was successful"

This one doesn't work...
Any ideas please ?

Thanks is forward.

 

[userxyz]

test

I did a test with 15000 csv file's in 1 folder en tested the scripts for that folder (tested on MMC).

It was no problem, no delays or whatever, works great!

So this a sort of databasing that can work !! It's cheap and it works...


The reason why it isn't slow... can it be because I write and read to csv's directly (path + filename)... ? I agree when I open the folder in Windows that it takes a while before I can see the files... But that doesn't matter

 

[GeoffC]

I agree with Jesper I would not do it in a flat file. I would use MySQL, it is also free but is a full SQL database system. I don't know about Wincc but can't you just read and write to a database using SQL commands, or are there tools to access a SQL database. I do this all the time in Citect
Also it is much easier to do a database backup than try and save and manage 1000s of files

 

[userxyz]

hey

Hey,

Yes, you're right, on the PC side I can do that. I can connect data to an SQL database with vbscripting.

But the MP277 needs a database also. We allready bought the MP277, so we need to continu with it. An MP277 has also vbscripting, but no supportation of SQL databasing... It should have been a panelpc with flex runtime, then I could do sql databasing

I agree with Jesper I would not do it in a flat file. I would use MySQL, it is also free but is a full SQL database system. I don't know about Wincc but can't you just read and write to a database using SQL commands, or are there tools to access a SQL database. I do this all the time in Citect
Also it is much easier to do a database backup than try and save and manage 1000s of files

 

[userxyz]

Another thing, why is Siemens offering import and export fuctions for csv (recipesystem of wincc flex)... if it's not trustable or efficiënt, why are they offering it then ?

 

[JesperMP]

Hi Combo.

That Siemens makes it possible to edit the recipe-csv files manually, does not mean that it is a good idea. Maybe yes for a simple small project.

Anayway, the system limits is another indication that it may not be a good idea. The number of data records are limited to 500 on an MP277 and 5000 on a PC RT. And you said you need "at least 10000".

I would really consider to let a 3rd party product on a server PC handle all of this. Like I said, FactorySQL would do the trick for what amounts to 1 week work. Can you do it faster than that ? Will it be as reliable ?

edit: Since you mention that it is possible to connect to MySQL via scripting.
This is not what I suggest.
I warn against connecting to SQL via VBS. I have been burned. It would be OK for non-critical stuff. In your case it sounds as it is an important part of the project.

 

[userxyz]

hm

at least 10000, hmm, I'm sorry, but that's not the case, sorry that I wrote that. I wanned to be sure that 10000 was no problem. They make different products / year.

10000 / 500 = 20 years, I think, 15000 = absolute max (30 years is a long time).


And I agree , the next time I'll go for inductive automation, I've heard very positive comment of them. For now I think I'll need to go for the recipes in wincc flex with the import and export with csv file's.

Hi Combo.

That Siemens makes it possible to edit the recipe-csv files manually, does not mean that it is a good idea. Maybe yes for a simple small project.

Anayway, the system limits is another indication that it may not be a good idea. The number of data records are limited to 500 on an MP277 and 5000 on a PC RT. And you said you need "at least 10000".

I would really consider to let a 3rd party product on a server PC handle all of this. Like I said, FactorySQL would do the trick for what amounts to 1 week work. Can you do it faster than that ? Will it be as reliable ?

edit: Since you mention that it is possible to connect to MySQL via scripting.
This is not what I suggest.
I warn against connecting to SQL via VBS. I have been burned. It would be OK for non-critical stuff. In your case it sounds as it is an important part of the project.

 

[userxyz]

Sql

Hellow,

Long time ago I used the flatfile read and write with VBS. This works.

Now for a logging project I need a solution to log parameters of a used recipe. Everything in a table form. It has to be possible to request on date and time which recipe has been used, how long did the steps needed before transistion , etc...
This cannot be done in flat files, because searching after certain data would take too much time.

So I was thinking and searching...
Found: 24677043 on the siemens support site.

It's a helppdf + project that is explaining how to read and write to and from an SQL database. Could this be done or should I use OPC and write my own query program in C or VB or something ? I've red from JesperMP that you can be burned by reading and writing with SQL via VBS code...
What's the best solution ?
FactorySQL is not an option.

Kind regards,
Gerry

Hi Combo.

That Siemens makes it possible to edit the recipe-csv files manually, does not mean that it is a good idea. Maybe yes for a simple small project.

Anayway, the system limits is another indication that it may not be a good idea. The number of data records are limited to 500 on an MP277 and 5000 on a PC RT. And you said you need "at least 10000".

I would really consider to let a 3rd party product on a server PC handle all of this. Like I said, FactorySQL would do the trick for what amounts to 1 week work. Can you do it faster than that ? Will it be as reliable ?

edit: Since you mention that it is possible to connect to MySQL via scripting.
This is not what I suggest.
I warn against connecting to SQL via VBS. I have been burned. It would be OK for non-critical stuff. In your case it sounds as it is an important part of the project.