We are creating .CSV files for data logging on a SNAP-PAC R2. The file contains a date and time stamp in one of the columns. When I open this file with notepad, the date and time is formatted correctly for us (hh:mm:ss.000, hours, minutes, seconds.ms). In excel, however, the hours are truncated and we’re left with mm:ss.0. Our server is receiving this file automatically and interpreting it as hh:mm.s. I know I can format the columns properly. The problem is that this CSV file is generated and sent with ftp to our server so I have no way of formatting the cells before ftp.
Can I use OptoScript to format the cells as the CSV is created?
Welcome to the forums! You can do just about anything in OptoScript, so I’m confident we can find you a solution or three.
BTW, Ben introduced us to this excellent (free!) tool that’s even better than notepad and very helpful in situations like this, it’s called Notepad++ (plus plus): http://notepad-plus-plus.org/
A few questions on this formatting problem:
How is the .CSV file currently getting created?
What should it look like to have your server-side interpret it correctly?
The .CSV is created in an OptoScript block. We build the string and tack on .CSV to the end.
We need the time stamp column to be in this format: 09:45:23.000 where 09 = Hours, 45 = Minutes, 23 = seconds, .000 = milliseconds. We are not concerned with accuracy to the Msec, but the WonderWare server expects to see .000 for Msec. I know the script we’ve written is formatting it correctly because it shows up in Notepad the right way.
If I paste this format into a cell in Excel, it takes off the hh and all I see is 45:23.0. This seems to be a known problem as I’ve read many forums. They all say to use the “Custom” format within excell and it of course, it works.
Forgive me if I’m asking dumb questions here, but I’m still not exactly clear on what you’re trying to change/fix. Sounds like because of WonderWare restrictions, you CAN’T change the format of the file? (Therefore the fix would have to happen in Excel?)
When I pasted that example you gave into Excel, I saw the same hour truncation you described. When I RIGHT-click and choose Format Cells, I have a few choices (under “Custom” as you mentioned) so I picked h:mm:ss instead and that seemed to give a better format – see that top cell.
Is that what you’re looking for? Perhaps just a re-format of the Excel cells? Forgive me if I’m barking up the wrong tree here…
I can easily change the formatting in the cell manually to get hh:mm:ss.000. The problem is that we have script to create the file, and send the file via ftp into our wonderware server automatically so I can’t format the cell each time the file is created. I know there are ways (using Macros) within Excel to format the cells properly each time. I guess I was grasping at straws hoping that I could perform similar Macros coding in the PAC so it would format the “Custom” cells automatically.
You mean build the .xls format within OptoScript? I hope not. That would be very ugly.
In any case, it sounds like you might need a different file to feed to WonderWare vs. Excel.
For this new file with similar data, perhaps you could just leave off (or round) those milliseconds to the nearest second so your value would look like: 09:45:23 and that’s it.
Or perhaps you could add another timestamp column (in the same file) where the one column is ignored by WonderWare and the other column (which has the milliseconds and drops the hours) is ignored by Excel? Just brainstorming here…
Just out of curiosity, how are you creating your CSV files in OptoScript? What commands are you using to create and write to CSV files? Also, is it possible you can use the colon instead of the decimal point as the last separator for your milliseconds?
I used the colon instead of decimal but wonderware expects the decimal. We made some changes in WW and PAC to leave off the milliseconds and it works.
You can build a .CSV or .TXT file by building a string variable…String_Handle = “file:w,/sdcard0/” + Your_File_Name + ".csv"
We created Your_File_Name by adding a few separate string values together.
Then, you use the commands
SetCommunicationsHandleValue(String_Handle, Your_Comm_Handle)
OpenOutgoingCommunications(Your_Comm_Handle)
I tried using the commands, but I’m having difficulty trying to have my OptoScript block write to the file properly. Can you please post a sample OptoScript code that would simply write the timestamp to the CSV file?
