How to reed data from 18F4550 EEPROM into EXCEL via PC USB interface?

Printable View

- 26th May 2010, 23:09
jellis00

How to reed data from 18F4550 EEPROM into EXCEL via PC USB interface?

I have spent the last hour searching the forums for an answer to this question and have finally given up and decided to post this new thread. Lots of discussion about such a datalogging application using RS232 or serial but very little if any on how to do this with USB. Here is what I want to do...any referralls to threads or WEB tutorials on any of the pieces of this application would be appreicated:
1) Data measurements (one word, once per day) are stored with the date value are stored by an 18F4550 based microcontroller into the MCU's EPROM memory for 31 days.
2) On monthly cycle a USB cable is connected to the 18F4550 based microcontroller to download the data to a laptop.
3) Need a VB6 program on the laptop as the PC side of the USB interface and to insert the collected data into 31 rows of three columns each (date, Data_LSB, Data_MSB) on an Excel spreadsheet.
4) Need to also read the RTC date/time (DS1337) from the microcontroller into the VB6 program at time of USB connection where user can reset the RTC time from the VB6 program if the read time isn't within timing tolerances.

Would prefer to do this with PBPro and not assembly. I have Darrel Taylor's and Mister-E's VB6 HIDs and have attempted to adapt them to provide the PC side of the USB interface, but still stuggling with how to have it read the 31 days of EEPROM data in a sequence that can easily be inserted into an EXCEL spreadsheet in the format desired.

Any help on this would be greatly appreciated and I will share back the results when finished.
- 27th May 2010, 05:47
wolwil

1st question...Why does it have to be USB? 2nd...Can it be USB to Serial? If so then you should use a FT232R USB to serial converter IC then goto this link and figure out the rest http://www.lvr.com/serial_ports_dotnet.htm

This way you communicate to the Microcontroller through a serial Com port and dont have to dink with the USB mumbo jumbo.

This method works well for me and its fairly easy to understand.

What you are asking for it to do is not hard if you break it apart into parts:

1) Read data from EEPROM Byte by Byte and send it serially to VB and buffer it there in a variable
2) Take the Variable in your VB Prog and format it however you want and dump it to Excel
- 27th May 2010, 08:04
jellis00

Must be USB!

Quote:

Originally Posted by wolwil

1st question...Why does it have to be USB?

Unfortunately the answer to this question is yes. I am doing this for a potential customer and he wants USB not serial. I have the USB interface between my MCU and an adaptation of Mister-Es HID and DT's HID working to a point....data is showing but it is not showing the correct values that I know the PIC is putting into its BufferOut array. The HID is all in VB6 and I don't understand it to even be able to trouble shoot it. What I need is some way to see what variables are showing up on the PC side of the USB to verify that everything is being sent correctly from the PIC. Once I have that verified then I will attack the problem of troube shooting the output of the VB6 to the PIC.

I was hoping someone had some ideas as to an HID I could use that is easier to understand and troubleshoot with the USB interface from the PIC.
- 27th May 2010, 11:49
Dave

jellis00, I do exactly that, I have 4, 24F1025's that I save data to and when requested by the host (PC) thru a terminal program. I dump the contents of the eeproms to it in CSV format (Comma Seperated Variable) which Excel will accept without any problems. I then use this data for presentation in Excel. The data is dumped at 57,600 baud thru a FTDI SERIAL to USB converter. It works for me as I hate to write more programs for the PC as well....

Dave Purola,
N8NTA
- 27th May 2010, 12:12
rsocor01

Quote:

Originally Posted by jellis00

I have the USB interface between my MCU and an adaptation of Mister-Es HID and DT's HID working to a point....data is showing but it is not showing the correct values that I know the PIC is putting into its BufferOut array.

I am not familiar with Mister_E or DT's HID programs. I use EasyHID VB6 interface to talk to my 18F4550 with a USB connection. You need to send the whole array of data from the PIC to the PC. This array of data is defined in the HID descriptor. For EasyHID the array size is 8 bytes at a time.

Quote:

Originally Posted by jellis00

The HID is all in VB6 and I don't understand it to even be able to trouble shoot it.

Hmmm, you are in trouble :eek:. You need to know at least the basics to be able to display the incomming data in a Label window. About sending the data to Excel, I haven't done it yet, but there is a way to do it using APIs in VB6.

Robert
- 27th May 2010, 16:38
Bruce

Have a look at the VB6 code in this project; http://www.rentron.com/PicBasic/PIC_USB.htm

It shows how to display data coming from the USB PIC on labels, in a text box, log data to file with time/date stamp, etc..

I never got around to completing the project page, but all VB & PBP code is in a .zip file for download.
- 27th May 2010, 18:55
jellis00

How does your terminal program dump into Excel??

Quote:

Originally Posted by Dave

jellis00, I do exactly that, I have 4, 24F1025's that I save data to and when requested by the host (PC) thru a terminal program. I dump the contents of the eeproms to it in CSV format (Comma Seperated Variable) which Excel will accept without any problems. I then use this data for presentation in Excel.

Dave, I would very much appreciate learning moe about how you implemented the PC side of your interface to receive and dump the data to Excel. Can you elaborate? Sounds like you use some kind of teerminal program rather than VB6 and I am curious as to how you get the terminal program to parse the BufferIn() contents from the PIC sent array and store it into Excel.
- 27th May 2010, 19:51
jellis00

Looks like a good approach to avoid having to learn details of VB6

Quote:

Originally Posted by Bruce

Have a look at the VB6 code in this project; http://www.rentron.com/PicBasic/PIC_USB.htm

It shows how to display data coming from the USB PIC on labels, in a text box, log data to file with time/date stamp, etc..

I never got around to completing the project page, but all VB & PBP code is in a .zip file for download.

Thanks so much for this, Bruce. I downloaded the zip and installed the files per the readme.txt. The readme was pretty explicit except it didn't say where in my file folders to place the vbskfr2.DEP file....can you tell me??
I notice the LABXUSB.pbp and LABXUSB.hex files are for 18F4450. I am using 18F4550. I also notice in the .pbp code that no CONFIGS are set for the 18F4450, so I presume it is using default CONFIGS for a 18F4450. Besides the CONFIGS, are there any other differences in the .pbp code I should be aware of while I attempt to convert it to compile/assemble on 18F4550? I would just use a 18F4450 except it has no on chip EEPROM which I need for the data logging I am doing. Really appreciate your advice on this.
- 27th May 2010, 20:58
tenaja

Quote:

Originally Posted by jellis00

Dave, I would very much appreciate learning moe about how you implemented the PC side of your interface to receive and dump the data to Excel. Can you elaborate? Sounds like you use some kind of teerminal program rather than VB6 and I am curious as to how you get the terminal program to parse the BufferIn() contents from the PIC sent array and store it into Excel.

I haven't done this myself recently..it's been a while. I did it many years ago using the windows api calls or something like that. It was with Win3.11, if that gives you any idea of how long it's been. It was very easy.

Recently I had a friend do it for me, and he formatted it with html and put it on the clipboard. It pasted perfectly formatted into excel. That may not be product worthy, though, if you need a "click to appear in excel" solution...but you could do that with autohotkey. It will let you save the clipboard, get it with the new data, paste it, then restore the clipboard.
- 27th May 2010, 21:06
jellis00

Replace my previous post with this one...more detail/different questions for Bruce.

Thanks so much for this, Bruce. I downloaded the zip and installed the files per the readme.txt, but in the process I have some questions:

1) The readme was pretty explicit except it didn't say where in my file folders to place the vbskfr2.DEP file....can you tell me??
2) As I understand it, the code in the zipped files is for an 18F4550, yet I see an 18F4450.INC file included in the zip.....how come? I would just use a 18F4450 except it has no on chip EEPROM which I need for the data logging I am doing. It is also a very old file (6/5/06) for version 2.47 so I presume I shouldn't replace the one in my PBP folder that is 09/12/08 Version 2.60 which I am using.
3) I notice in the LABXUSB.inc file that the CONFIGS for an 18F4550 are listed for use with the bootloader but are commented out, so I presume it is using default CONFIGS for a 18F4550???
4) I am programming my chips with a PICKIT2 rather than bootloader, so not sure what I have to do to the CONFIGS that are listed????
5) I also notice that the 18F4550.bas file included in the zip is an older version for 2.46 (12/31/04) than the one in my PBP folder that is dated 9/12/08 for 2.6, ....and the older version has following files commented out:
BANKA $0000, $005F
BANK0 $0060, $00FF
BANK1 $0100, $01FF
BANK2 $0200, $02FF
BANK3 $0300, $03FF
'BANK4 $0400, $04FF
'BANK5 $0500, $05FF
'BANK6 $0600, $06FF
'BANK7 $0700, $07FF
'EEPROM $F00000, $F000FF
'LIBRARY "PBPPIC18"
LIBRARY "PBPUSB18"
include "PIC18EXT.BAS"
PORTL VAR PORTB
PORTH VAR PORTC
TRISL VAR TRISB
TRISH VAR TRISC
include "PBPPIC18.RAM"

In the newer version this is the equivalent list and nothing is commented out:
BANKA $0000, $005F
BANK0 $0060, $00FF
BANK1 $0100, $01FF
BANK4 $0400, $04FF
LIBRARY "PBPPIC18"
include "PIC18EXT.BAS"
PORTL VAR PORTB
PORTH VAR PORTC
TRISL VAR TRISB
TRISH VAR TRISC
include "PBPPIC18.RAM"
USBMEMORYADDRESS Con $400 ' USB RAM starts here

I presume since I am using PBP v2.6 compiler that I should not replace the 18F4550.bas file in my PBP folder with the one from the zip?? Should I comment out and of the BANK statements or change the LIBRARY to the one listed in the old file?

Really appreciate your advice on this.
- 27th May 2010, 22:02
Bruce

Hi Jellis00,

This was done on a 4550 and compiled with PBP v2.47. Look in LABXUSB.INC for config fuse settings. I had these commented because I used Microchips USB boot-loader. You can get rid of the DEFINE RESET_ORG 800h since you're not using the USB loader. Note there are a few routines in the PBP code that were just experimental also.

The 18F4450.INC was in there because someone requested it way back when I first put this together, and I compiled the Microchip USB loader for an 18F4450 for them.

Just use the 18F4550.INC file you have, and set config fuses to the same as in the LABXUSB.INC file.

The Readme.txt file has instructions for the vbskfr2.DEP and other related files. This is a VB skin program that gives the form the look it has.

It's a lot of reading, but it's all there. If you're using PBP 2.6 look for the changes Darrel mentioned, and make sure you use whatever include files are with 2.6.
- 27th May 2010, 22:19
jellis00

That answers most of my questions...still 2 more

Quote:

Originally Posted by Bruce

The Readme.txt file has instructions for the vbskfr2.DEP and other related files. This is a VB skin program that gives the form the look it has.

It's a lot of reading, but it's all there. If you're using PBP 2.6 look for the changes Darrel mentioned, and make sure you use whatever include files are with 2.6.

Thanks, Darrel...that clears up a lot of it.

However, I looked again in the Readme.txt file and although it explicitly tells you where to place most of the files, there is no instruction where to place the vbskfr2.DEP file. Since it is a "dependency" file I'm not sure where it goes. I found some info here
http://support.microsoft.com/kb/178354/en-us but it still doesn't say where to place the .dep file. Should it go into the PBP root folder or in my project file under the PBP root folder....or does it go somewhere in the Windows/system32 folder like the other files in the zip?

Not sure what you are referring to in your statement "look for the changes Darrel mentioned"...he wasn't one of the posts in this thread. Are you referring to some other post of his?
- 27th May 2010, 22:39
chuck

Why not use SD card ?, with the SD card you can create a CSV file and open it striaght up in excel. With SD card you can unplug oen insert another SD card to carry on taking the samples then you can just insert it to a card reader either fixed or usb sd card reader.
That's the way I would do it
- 27th May 2010, 22:55
Bruce

See this thread on USB changes to PBP 2.6: http://www.picbasic.co.uk/forum/show...5&daysprune=-1

I haven't used USB with PBP 2.6 yet, but there have been changes, and you may need a newer version of EasyHID also.

I dropped the .ocx in Windows/system, and just left the other vbskin files in the VB6 directory with the VB project files.
- 28th May 2010, 09:04
ardhuru

Hi, here are a few thoughts, some, all, or none of which might be of use.

1) The folks at Parallax have a free application, PLX DAQ (earlier StampDaq) that directly takes in data from the serial port and inserts them in a spreadsheet; you might have to play around with the data frame structures; http://www.parallax.com/tabid/393/Default.aspx If you use the CDC mode, the app might be able to take your data directly from the virtual com port, eliminating a USB-to-serial chip / adapter.

2) As Dave pointed out, again using CDC, you could spit out the data in your 3 columns in a 31 iteration loop, grab it on the PC with a terminal emulator (one that recognises virtual com ports), and simply copy-paste it into Excel. This technique I have tried (not with USB, with serial) to plot the discharge curve of a battery and works fine.

3) Take a look at Windmill software, http://www.windmill.co.uk/ as also another product from Parallax, http://www.parallax.com/ProductInfo/...1/Default.aspx

Regards,

Anand
- 28th May 2010, 12:24
Dave

Quote:

Originally Posted by jellis00

Dave, I would very much appreciate learning moe about how you implemented the PC side of your interface to receive and dump the data to Excel. Can you elaborate? Sounds like you use some kind of teerminal program rather than VB6 and I am curious as to how you get the terminal program to parse the BufferIn() contents from the PIC sent array and store it into Excel.

jellis00, The comma separated variables are generated in the 18F2620. The terminal program I use for all of my communications is TeraTermPro.exe. It is free ware and the BEST terminal program I have found. It allows you to open multiple instances and has all of the features to emulate VT-100 and ANSI commands. I mearly turn on the log function and then send the 18F2620 the command to dump the array data. After the data is downloaded I close the log file and the file is then ready to be inported into Excel...

Dave Purola,
N8NTA
- 28th May 2010, 18:34
chuck

Quote:

Originally Posted by ardhuru

Hi, here are a few thoughts, some, all, or none of which might be of use.

1) The folks at Parallax have a free application, PLX DAQ (earlier StampDaq) that directly takes in data from the serial port and inserts them in a spreadsheet; you might have to play around with the data frame structures; http://www.parallax.com/tabid/393/Default.aspx If you use the CDC mode, the app might be able to take your data directly from the virtual com port, eliminating a USB-to-serial chip / adapter.

2) As Dave pointed out, again using CDC, you could spit out the data in your 3 columns in a 31 iteration loop, grab it on the PC with a terminal emulator (one that recognises virtual com ports), and simply copy-paste it into Excel. This technique I have tried (not with USB, with serial) to plot the discharge curve of a battery and works fine.

3) Take a look at Windmill software, http://www.windmill.co.uk/ as also another product from Parallax, http://www.parallax.com/ProductInfo/...1/Default.aspx

Regards,

Anand

I was going to suggest that, But you would have to get written permission for bussiness use has you can us it and alter it for free for private use. If i remember there is one file that cannot be altered due to copyrights.
May be worth e-mailing them to ask and see what they say
- 28th May 2010, 22:15
jellis00

Reference is to Parallax or Windmill?

Quote:

Originally Posted by chuck

I was going to suggest that, But you would have to get written permission for bussiness use has you can us it and alter it for free for private use. If i remember there is one file that cannot be altered due to copyrights.
May be worth e-mailing them to ask and see what they say

Thanks, Chuck for ideas. Are you referring to Parralax or Windmill?
- 29th May 2010, 06:42
chuck

Quote:

Originally Posted by jellis00

Thanks, Chuck for ideas. Are you referring to Parralax or Windmill?

I was referring to Parralax has this is a freeware not sure about the windmill software.
I've used the Parralax many of times and it really easy to use.
- 29th May 2010, 17:50
jellis00

Will it work with USB interface?

Quote:

Originally Posted by chuck

I was referring to Parralax has this is a freeware not sure about the windmill software.
I've used the Parralax many of times and it really easy to use.

Thanks, Chuck. As I have said before, however, I must use it on a USB intereface for this particular application. Have you (or anyone else that might answer) ever been able to get Parralax to work with a USB interface between the PC and the PIC?? If I can verify that is doable I am going to give it a try.
- 29th May 2010, 18:07
ardhuru

From what I hear, USB-to-serial adapters work fine with PLX DAQ, BUT you have to make sure Windows assigns a lower com number (1 to 4) to the virtual serial port.

Regards,

Anand
- 30th May 2010, 00:04
chuck

Quote:

Originally Posted by jellis00

Thanks, Chuck. As I have said before, however, I must use it on a USB intereface for this particular application. Have you (or anyone else that might answer) ever been able to get Parralax to work with a USB interface between the PC and the PIC?? If I can verify that is doable I am going to give it a try.

Sorry I have not tired the USB way, But I just used an RS232 chap and uploaded it via USB-to-serial cable and this works spot on. This was becasue the laptop does not a a serial port on it.
I have treid to do a search for usb into excel but never found anything, I shall carry and look and have ago at doing my own has USB is the way forwardm I susspose it easy to so once you have mastered it. :D
- 30th May 2010, 05:50
ardhuru

USB is not my forte (yet!), but from what little I understand (Bruce, Darrel or any of the other Gurus can confirm or rubbish this), if you use CDC descriptors on the pic side it would be equivalent to using a RS232-to-serial. Then, you dont need to worry about inputing data from USB into Excel; just assign a low com port to the virtual connection and Excel wouldnt even know the data is coming over USB, not the serial port.

Regards,

Anand
- 30th May 2010, 08:02
mackrackit

Quote:

Originally Posted by ardhuru

USB is not my forte (yet!), but from what little I understand (Bruce, Darrel or any of the other Gurus can confirm or rubbish this), if you use CDC descriptors on the pic side it would be equivalent to using a RS232-to-serial. Then, you dont need to worry about inputing data from USB into Excel; just assign a low com port to the virtual connection and Excel wouldnt even know the data is coming over USB, not the serial port.

Regards,

Anand

I am not a guru but you are correct.