Data into excel?

A

agoodevans

New Member
Thanks to WestAust and others, my 40x will now collect data and send to Terminal via download cable. Would like to automatically get data from terminal into MS Excel or a text file first? Any ideas or help would be appreciated.
Thanks in advance.
 
T

tjetson

Senior Member
Text file would be a lot simpler than Excel, but as far as I know, the only way to do either is to write your own program (ie no features built in).
 
W

westaust55

Moderator
It is all very easy. :) But a little published fact.

Instead of using F8 for the usual terminal window,

Within the Programming Editor, Press F9 for the datalogger window.

Make sure that your program includes a comma chracter after each field and a LF and CR chracters at the end of each line.
At the end of data logger transfer, send a null character (value = zero).

Then once the data is transferred, use the Save function from the datalogger window.

This will save the data received from the PICAXE chip into a .csv (comma delimited file).

Excel can read that file straight into excel. Just use File/Open, navigate to where you saved the .csv file and select all file types.

The one caveate - if there is a pause of any appreciable time, not sure exact dutration but something like 10 or 20 seconds between each record sent by the PICAXE, the datalogger window takes this as the end of the data transfer and stops receiving.


will work with either SEROUT command via a normal output pin or SERTXD comamnd via the standard PICAXE programming output. :)
 
W

westaust55

Moderator
As stated - its there but not widely known.

I was going to write an article for Silicon Chip relating to a 18X based weather station/datalogger that I built using two piggy-backed Kiwi-Patch Boards but as SiChip seem too dis-organised and lacking on feedback for me to be bothered with, now all have the information that Rev Ed could have better informed the world about.

Have previously informed a few forum members here by PM of this feature but since there will be no further Si Chip articles from me may as well tell the world.
 
Last edited:
A

agoodevans

New Member
Fabulous

Thanks again for the prompt assistance. Was just about to delete the thread as other threads are covering similar ground with Plx-Daq software and other solutions. WestAussie - I searched the PicEditior help and options as I thought there had to be a way. Much appreciated. I also downloaded Plx-Daq and it seems easy to use but havn't actually tested it as yet. VBasic was my next port of call.
 
W

westaust55

Moderator
The use of the F9 Datalogger window in the Programming Editor is almost a secret unless one studies the datalogger datasheet.

Many here have in the apst recommended software such as Stampplot and the like.
While many of these are free, it is something else to download and as many teachers here can attest (I am not a teacher) there can be issues with schoold IT departments not permitting instalaltion of unauthorised software.

So the inclusion of a feature within the PE is great - it just needs more exposure by Rev Ed.

@Stan - thanks, I know you have mentioned this to SiChip in support of PICAXE, HOPERF modules, and more.
 
J

Jeremy Leach

Senior Member
Just for info - there are ways of getting the data directly into Excel from the COM port - and it's been mentioned in the past - but it's quite advanced use of VBA. No additional software needed, or use of Terminal/PE though.
 
S

SAborn

Senior Member
Westy,

You have me interested with F9, and could you post a example line of code of how the data needs to be presented to the pc.

I very much agree with you on SC as i have been there to.
They will give good communication up to the point of disclosure of the design, then ignore you after.
Your idea may well still end up in print some months later as their design. (no mention of you)
SC has deteriated with time since Allan Simpson has been in editor, with an arrogent attitude
What the electronic tinker wants has been ignored.
I was once a monthly buyer of the mag and now i read it in the news agent as there is only a few pages worth reading.
I am trying not to give up on the mag as it really was a good electronics mag and figure Allan Simpson cant last there forever.
I just hope its not too late when he finally steps aside so a breath of life can be injected into the mag to what the readers wanted.....more circuits ....more ideas....not just another scope test or coloured disco light generator (who the hell builds one of those??) and yet another stero amplifier (you can buy them cheaper than you can make one).

A weather station sounds interesting, a way to make circuit boards is interesting, how to build a I2C slave sister board for a LCD with a pic as a driver is interesting, a home built bench variable frequency generator with inbuilt input frequency meter is interesting, to name a few that never made it to print...well at least without the designers name to it.

Sorry for the rant but i feel strongly about a great mag going down the toilet like EA and many others have.
 
L

Lorian

New Member
I wrote a little datalogger program for the PC. It just stores the data being recieved from the PICAXE and adds a date and time stamp so I don't need a RTC in the circuit.
 
W

westaust55

Moderator
SAborn said:
Westy,

You have me interested with F9, and could you post a example line of code of how the data needs to be presented to the pc.
Click to expand...

Hi SABorn,

as requested,
first for use with any "noprmal PICAXE output pin:
Code: 
symbol COM = 44         ' comma
symbol RET = 13         ' carriage return
symbol LFEED = 10       ' line feed
;
;
serout 7,N4800, ("Time & Date",COM,"'--",COM,"'--",COM,"Year",COM,"Month",COM,"Day",COM,"Hour",COM,"Min",COM,"Sec",COM," ",RET,LFEED)
pause 10
serout 7,N4800, (#address,COM,data0,COM,data1,COM,"20",#data2,COM,#data7,COM,#temp_byte,COM,#hours,COM,#mins,COM,#secs,COM," ",RET,LFEED)
;
;
serout 7,N4800, ("Address",COM,"Humidity",COM,"Future 1",COM,"Future 2",COM,"Temperature",COM,"Day",COM,"Hours",COM,"Mins",COM,"Secs"," ",RET,LFEED)
pause 10
serout 7,N4800, (#address,COM,#data0,COM,#data1,COM,#data2,COM,#data7,COM,#temp_byte,COM,#hours,COM,#mins,COM,#secs,COM,"  ",RET,LFEED)
and then end the entire data trasfer with
'finished so send NULL
serout 7,N4800,(0)
Click to expand...

If you are using the PICAXE programming output pin then change
serout 7,N4800,
Click to expand...
to
SERTXD
Click to expand...
by adding the comma's, line feeds and carriage returns into the PICAXE output, the F9 terminal window will save the data in comma delimited format which EXCEL can read straight in and have it nicely formated for you ready to do maths upon or use excel graphing functions etc.
 
J

John West

Senior Member
westaust55 said:
It is all very easy. :) But a little published fact.

[snip]

The one caveate - if there is a pause of any appreciable time, not sure exact dutration but something like 10 or 20 seconds between each record sent by the PICAXE, the datalogger window takes this as the end of the data transfer and stops receiving.
Click to expand...
This question comes immediately to mind: Can the data-logger time-out be changed?
 
W

westaust55

Moderator
SAborn said:
Thanks Westy,

What does the #address and "address" refere to?
Click to expand...
the Address part is a left over from copy and paste out of a program for adding date and time stamping to a dattalogger program.
they are in that code the title and data record numbers for each record as saved and subsequently sent to the PC.
 
G

Goeytex

Senior Member
You could send the Data to Hyperterminal with the "capture file" turned on.
Then run a relatively simple VB Script to convert the data in the capture file
into CSV Format. Then load it into Excel.

Use Windows Task Schelduler to open and close the apps at the appropriate times.
 
W

westaust55

Moderator
Yes you could use other software such as hyperterminal.

If the PICAXE sends the data correctly with commas etc then there sould be no need to do anything like "run a relatively simple VB Script to convert the data in the capture file".
Certainly don't need to with the PE F9 datalogger terminal.

As Jeremy Leach has also pointed out, for those who are experienced with VBA in Excel, you could have the data transferred go directly into Excel.

For schools and similar instutions there are often difficulties seeking permission/approval to install new software onto PC''s in those environments so the all-in-one approach using the Rev Ed PE is a good option.
 
G

Goeytex

Senior Member
westaust55 said:
Yes you could use other software such as hyperterminal.

If the PICAXE sends the data correctly with commas etc then there sould be no need to do anything like "run a relatively simple VB Script to convert the data in the capture file".
Certainly don't need to with the PE F9 datalogger terminal.

As Jeremy Leach has also pointed out, for those who are experienced with VBA in Excel, you could have the data transferred go directly into Excel.

For schools and similar instutions there are often difficulties seeking permission/approval to install new software onto PC''s in those environments so the all-in-one approach using the Rev Ed PE is a good option.
Click to expand...
You will probably need some kind of simple script to at least filter out any garbage in the file. But maybe not. Wouldn't need to be but a few lines anyway.

I don't know of a Windows Installation that doesn't come with Hyperterminal so that is not real issue with my suggested approach.

There would be no people "experienced" In Visual Basic or anything else if we keep discouraging folks because they are not "experienced". That how the experience comes, by getting in there and doing it.

I would suggest Python over VB anyway.

Goey
 
W

westaust55

Moderator
Goeytex said:
There would be no people "experienced" In Visual Basic or anything else if we keep discouraging folks because they are not "experienced". That how the experience comes, by getting in there and doing it.

I would suggest Python over VB anyway.

Goey
Click to expand...
It is not a case of discouraging. It is more a case of trying to provide the simplest solution which may be closest to the enquirers capabilities.

I read, test and experiment for myself to learn something new.
That philosophy comes from the days before the internet was available to glean and ask others.
For all my posts here over the 2 years since I "found" PICAXE chips, rarely have I asked a question on this forum to bring my own projects to completion.

My personal view is that for real experience research one should endeavour to research and read for one's self - asking others is akin to a last resort not a first line.
But I accept that these days, there is at least an element who want to do something and never read or research, they just ask how outright and up front with no or little research of their own.

For me, the PICAXE is purely a hobby and through this Rev Ed forum presents a way to share my hobby with others.

I am also mindful of the fact that there are many members here who are teachers and many students come through this forum.

Experience can be gained by personal application.
I recently did some work in my spare time and now PEBBLE as a breadboard layout program is where it is. For me it was a challenge and related to my hobbies.
Sure I had played around with HTML something like 10 years ago for a website but then not touched it for many years.
I had not touched Javascript before but a quick look and I figured out how it worked (still do not class myself as an expert).
Then I set about writing code, testing it adding to it as others here recommended features/changes.
Now I have experience in Javascript but I did not ask questions here or on other forums on what to do to bring it to its current state.

But we are here digressing from agoodevans post somewhat so I will desist.
 
K

kd5crs

Senior Member
If you are doing this on a Mac and receiving the serial data through something Applescriptable like SerialBridge ($20 from Perceptive Automation), then Applescript makes it easy to put the data into a text file which can be read into Excel as already stated. Once the script is running there would be no required user interaction, which is a benefit above some of the previously listed methods.

Just throwing that out there, as Macs can sometimes be found in educational settings.

Brian
 
B

BCJKiwi

Senior Member
Hyperterminal for WIN 7

Hyperterminal from XP works fine in Windows 7

Copy (at least) hypertrm.exe, and hypertrm.dll from a working Windows XP system into the same folder in Windows 7 (note they are in different folders in XP).

Create a shortcut to hypertrm.exe and all is good.
Suggest that the .hlp and .chm files be copied over as well.
 
T

tjetson

Senior Member
BCJKiwi said:
Hyperterminal from XP works fine in Windows 7

Copy (at least) hypertrm.exe, and hypertrm.dll from a working Windows XP system into the same folder in Windows 7 (note they are in different folders in XP).

Create a shortcut to hypertrm.exe and all is good.
Suggest that the .hlp and .chm files be copied over as well.
Click to expand...
That's a nice idea. I wonder if this would work for me as I have 32 bit XP machines and I run 64 bit Windows 7.
 
O

obroni

Member
I have been looking at knocking up something in Python to collect the scores from the guns for my LibreTag laser tag system. Not having done any Python before means I have got to learn it all from scratch, but it hopefully shouldn't take me too long. If you have any examples though it would be handy.
 
You must log in or register to reply here.
Top