Test and Measurement Forum

VBA to Read data from Picolog PLW file

Post your VB and VBA discussions here

VBA to Read data from Picolog PLW file

Postby JonBear » Wed Feb 07, 2018 12:14 pm

Hi All,
I am being supplied with temperature data from a TC-08 and can load this into the "Picolog Player" and view the readings with no problem.
As I am going to be passed a PLW file daily I wanted to develop a more automatic method of extracting the data directly - eventually to import into a SQL database.
I can open the file in VBA, read the contents one character at a time and display then on a worksheet, but it would appear that the file is a combination of 8-bit ASCII characters with multi byte numeric values embedded in it. I have managed to combine the values, from what looks like the relevant section of the file, I cannot get a floating point value that matches what shows in "Picolog Player" spreadsheet view.
Has anyone managed to achieve this, or alternatively have details on the exact format of the file?
JonBear
Newbie
 
Posts: 0
Joined: Wed Feb 07, 2018 12:03 pm

Re: VBA to Read data from Picolog PLW file

Postby Hitesh » Wed Feb 07, 2018 5:00 pm

Hi JonBear,

You may wish to take a look at this post for reference.

Another option might be to convert the files to csv using the PLW to CSV file converter on our [url=PicoLog: PLW to CSV file converter]PicoApps[/url] page and then process them to add to the SQL database.

Hope this helps,
Hitesh

Technical Support Engineer
Hitesh
Site Admin
Site Admin
 
Posts: 2765
Joined: Tue May 31, 2011 3:43 pm
Location: St. Neots, Cambridgeshire

Re: VBA to Read data from Picolog PLW file

Postby JonBear » Thu Feb 08, 2018 10:55 am

Hi Hitesh,
Thank you for replying.
I have looked again at the 'PLW Recorder' and see that 'PLW' is the only output format.
There is a 'CSV' option on the Player, but I was hoping for my routine to not require any manual input. I wanted Excel to open the 'PLW' file, get the temperature values and TC-08 serial number, format the output then 'BULK INSERT' them into an existing SQL database.
I have already written my own software to take readings from a TC-08, which imports fine from a CSV output on site here. The problem is that this external supplier wants to use the Pico software supplied.
JonBear
Newbie
 
Posts: 0
Joined: Wed Feb 07, 2018 12:03 pm

Re: VBA to Read data from Picolog PLW file

Postby Hitesh » Fri Feb 09, 2018 9:21 am

Hi JonBear,

The PicoLog 5 software supports Microsoft Dynamic Data Exchange (DDE). If this is of interest, I can provide an example Excel file if you could please e-mail support@picotech.com

Regards,
Hitesh

Technical Support Engineer
Hitesh
Site Admin
Site Admin
 
Posts: 2765
Joined: Tue May 31, 2011 3:43 pm
Location: St. Neots, Cambridgeshire

Re: VBA to Read data from Picolog PLW file

Postby JonBear » Fri Mar 09, 2018 11:33 am

I have continued to look into the issue of reading the Start Time from the ‘PLW’ file and I think I have found a solution.
I will detail what I have done for interest if any other users have a similar problem.
For a ‘PLW’ time of 05-02-18 the 32-bit value at bytes 568-589 = 736,730. You stated that this possibly could possibly be stored in mS, which was confirmed by the code sample you sent. I started by taking the 736,730 value and converting it to mS, giving me a value of:
736730 * 24 Hours * 60 Minutes * 60 Seconds * 1000 milli-Seconds = 63,653,472,000,000
From the known date of 05-02-18 I know that I am looking to get a value of 43,164 (confirmed by entering the date into an Excel cell then displaying as a number).
I tried to get the 63e12 value into a value approaching 43e3, but found myself /1000 to get seconds the /60 to get minutes etc. This was just undoing the multiplying up I did of the number I started with (736,730). Therefore I reckoned that if any conversion to mS relied on constants i.e. 60 seconds, 1000 mS etc. then if I just subtract a constant value of 693,594 from the ‘PLW’ file value I will get the date I want.
I have tried this for various dates and they all seem to come out correctly. I also used times at the start and end of a day as, from the code sample, the start time (bytes 590-593) is applied to the start date to get a Date/Time value:
05-02-18 11:38:30 736730 - 693594 = 43164 05-02-2018
05-03-18 09:03:36 736758 - 693594 = 43164 05-03-2018
05-03-18 23:58:27 736758 - 693594 = 43164 05-03-2018
31-03-18 00:00:17 736784 - 693594 = 43190 31-03-2018
31-12-18 23:58:38 737059 - 693594 = 43465 31-12-2018
I am continuing to process the ‘PLW’ files supplied to me using this method and all seems good so far.

I hope this is of some help to someone.
Jon
Last edited by Hitesh on Wed Mar 14, 2018 4:37 pm, edited 1 time in total.
Reason: Remove ticket number ref.
JonBear
Newbie
 
Posts: 0
Joined: Wed Feb 07, 2018 12:03 pm

Re: VBA to Read data from Picolog PLW file

Postby Hitesh » Wed Mar 14, 2018 4:38 pm

Hi Jon,

Thanks for sharing the information here.

Regards,
Hitesh

Technical Support Engineer
Hitesh
Site Admin
Site Admin
 
Posts: 2765
Joined: Tue May 31, 2011 3:43 pm
Location: St. Neots, Cambridgeshire


Return to VB and VBA (Excel, Access)

Who is online

Users browsing this forum: No registered users and 0 guests