User avatar
Trigo
Posts: 106
Joined: Fri Jun 17, 2016 7:52 pm

Excel and python

Mon Mar 09, 2020 8:09 pm

In case you miss me... ;) I'm trying to write a python script that pulls data from other python script and insert it on a Excel sheet! I'm not getting it! I have a script from someone ( very nerd :) ).It pulls data from a car ECU (Mine only gets throttle position, engine coolant temperature, RPM, and Air Flow)! I want to pull that values on a excel sheet!

This code is from someone

Code: Select all

# PID010C Get the current Engine RPM from the ECU.
    def PID010C(self, FreezeIndex = -1):
        Result = STRING_NO_DATA

        if '010C' in self.ValidPIDs:
            if FreezeIndex == -1:
                Response = self.GetResponse(b'010C\r')
                Response = self.PruneData(Response, 2)
            else:
                Response = self.GetResponse(bytearray("020C" + "{:02d}".format(FreezeIndex) + "\r", 'UTF-8'))
                Response = self.PruneData(Response, 3)
            Result = (256 * int(Response[:2], 16) + int(Response[2:4], 16)) / 4
i don't know how to make it appear on a excel sheet! I've read several blogs and several articles, no one wants to say! If you could assist me i will be grateful!

Thanks.... Trigo!
Beat psychologically with a stick :?:

User avatar
B.Goode
Posts: 10549
Joined: Mon Sep 01, 2014 4:03 pm
Location: UK

Re: Excel and python

Mon Mar 09, 2020 9:15 pm

Trigo wrote:
Mon Mar 09, 2020 8:09 pm
In case you miss me... ;) I'm trying to write a python script that pulls data from other python script and insert it on a Excel sheet! I'm not getting it! I have a script from someone ( very nerd :) ).It pulls data from a car ECU (Mine only gets throttle position, engine coolant temperature, RPM, and Air Flow)! I want to pull that values on a excel sheet!

This code is from someone

Code: Select all

# PID010C Get the current Engine RPM from the ECU.
    def PID010C(self, FreezeIndex = -1):
        Result = STRING_NO_DATA

        if '010C' in self.ValidPIDs:
            if FreezeIndex == -1:
                Response = self.GetResponse(b'010C\r')
                Response = self.PruneData(Response, 2)
            else:
                Response = self.GetResponse(bytearray("020C" + "{:02d}".format(FreezeIndex) + "\r", 'UTF-8'))
                Response = self.PruneData(Response, 3)
            Result = (256 * int(Response[:2], 16) + int(Response[2:4], 16)) / 4
i don't know how to make it appear on a excel sheet! I've read several blogs and several articles, no one wants to say! If you could assist me i will be grateful!

Thanks.... Trigo!



XlsxWriter is a Python module that can be used to write text, numbers, formulas and hyperlinks to multiple worksheets in an Excel 2007+ XLSX file.
Ref: https://xlsxwriter.readthedocs.io/


However, maybe a simple CSV file would suffice?
https://docs.python.org/3/library/csv.html

User avatar
paddyg
Posts: 2566
Joined: Sat Jan 28, 2012 11:57 am
Location: UK

Re: Excel and python

Mon Mar 09, 2020 11:12 pm

And I once used https://openpyxl.readthedocs.io/en/stable/ which seemed quite good and still seems to be quite actively developed.
also https://groups.google.com/forum/?hl=en-GB&fromgroups=#!forum/pi3d

User avatar
Trigo
Posts: 106
Joined: Fri Jun 17, 2016 7:52 pm

Re: Excel and python

Wed Mar 11, 2020 9:16 pm

Well this not getting me anywhere! If someone wants to clear some things, i would give a rate of 5! A very nice rate! :evil: This is what i have...

Code: Select all

# PID0110 Get the MAF air flow rate from the ECU.
    def PID0110(self, FreezeIndex = -1):
        Result = STRING_NO_DATA

        if '0110' in self.ValidPIDs:
            if FreezeIndex == -1:
                Response = self.GetResponse(b'0110\r')
                Response = self.PruneData(Response, 2)
            else:
                Response = self.GetResponse(bytearray("0210" + "{:02d}".format(FreezeIndex) + "\r", 'UTF-8'))
                Response = self.PruneData(Response, 3)
            Result =   (256 * int(Response[:2], 16) + int(Response[2:4], 16))/100
            
        return Result
    PidFunctions["0110"] = PID0110
    PidFunctions["0210"] = PID0110
    
    File = open("SAVE/CO2.csv", 'a')
    print string("PID0110")
    time.sleep(2)
    File.write = string ("PID0110"+"\n")
    File.close()
It only prints PID0110 at the terminal or at a excel sheet! Can you say something. I want it to write data every 2seconds! I'm not a expert with this... this is... lei(sure) time...
Beat psychologically with a stick :?:

User avatar
B.Goode
Posts: 10549
Joined: Mon Sep 01, 2014 4:03 pm
Location: UK

Re: Excel and python

Wed Mar 11, 2020 9:37 pm

Trigo wrote:
Wed Mar 11, 2020 9:16 pm
Well this not getting me anywhere! If someone wants to clear some things, i would give a rate of 5! A very nice rate! :evil: This is what i have...

Code: Select all

# PID0110 Get the MAF air flow rate from the ECU.
    def PID0110(self, FreezeIndex = -1):
        Result = STRING_NO_DATA

        if '0110' in self.ValidPIDs:
            if FreezeIndex == -1:
                Response = self.GetResponse(b'0110\r')
                Response = self.PruneData(Response, 2)
            else:
                Response = self.GetResponse(bytearray("0210" + "{:02d}".format(FreezeIndex) + "\r", 'UTF-8'))
                Response = self.PruneData(Response, 3)
            Result =   (256 * int(Response[:2], 16) + int(Response[2:4], 16))/100
            
        return Result
    PidFunctions["0110"] = PID0110
    PidFunctions["0210"] = PID0110
    
    File = open("SAVE/CO2.csv", 'a')
    print string("PID0110")
    time.sleep(2)
    File.write = string ("PID0110"+"\n")
    File.close()
It only prints PID0110 at the terminal or at a excel sheet! Can you say something. I want it to write data every 2seconds! I'm not a expert with this... this is... lei(sure) time...


This is 'leisure' time for me too. Let's see if we can move that 5 towards a 10...

You appear to define a function, PID0110() that you subsequently make no use of.

As you say, the script as it stands will simply wait for 2 seconds and then append the string 'PID0110' to the file SAVE/CO2.csv



So as not to waste time, I will be straightforward - it seems your Python programming knowledge/experience is not currently a match for the task you are trying to complete. I suggest you need to step back and learn some basic Python skills that will enable you to better understand how to complete the task.

User avatar
Trigo
Posts: 106
Joined: Fri Jun 17, 2016 7:52 pm

Re: Excel and python

Wed Mar 11, 2020 9:50 pm

You know the answer and you hide it. OK. Is like when you want to go to a place 5km away and you have to do 500... The environment pays the price... anyway... How do i print a result of a function on the terminal. Thank you!

A vídeo to wake you up!

https://youtu.be/dv3T2VOVJc8


[moderator thinks this attitude warrants strikethroughs, and a short ban]
Beat psychologically with a stick :?:

pcmanbob
Posts: 9884
Joined: Fri May 31, 2013 9:28 pm
Location: Mansfield UK

Re: Excel and python

Thu Mar 12, 2020 1:45 pm

First that sort of attitude will get you a ban, people here give their time to help others they don't have to help you.

Secondly your understanding of python and how to call a function is clearly incorrect which is why it was suggested you go learn the basics before continuing with your project.

So I have tried to correct your code but I an not guaranteeing it will work as I only have you posted code to work from and it does not look complete.

Code: Select all

# PID0110 Get the MAF air flow rate from the ECU.
    def PID0110(self, FreezeIndex = -1):
        Result = STRING_NO_DATA

        if '0110' in self.ValidPIDs:
            if FreezeIndex == -1:
                Response = self.GetResponse(b'0110\r')
                Response = self.PruneData(Response, 2)
            else:
                Response = self.GetResponse(bytearray("0210" + "{:02d}".format(FreezeIndex) + "\r", 'UTF-8'))
                Response = self.PruneData(Response, 3)
            Result =   (256 * int(Response[:2], 16) + int(Response[2:4], 16))/100
            
        return Result
    
    with open("/home/pi/SAVE/CO2".csv" , "a") as file
    While True:
        try:
        
            result = PID0110(0110)
           
            print (result)
            result = str(result)
            file.write(result + "," + "\n")
            time.sleep(2)
            
        except KeyboardInterrupt:    
            
            file.close()
            print ("end program , close file")
            
Some points to be aware of
1. you should use full paths to files when opening then so you always know were they are going to end up.
2. you can only write strings to a CSV file but if the file is opened in an office application you well find the entered information will be read as numbers.
We want information… information… information........................no information no help
The use of crystal balls & mind reading are not supported

User avatar
paddyg
Posts: 2566
Joined: Sat Jan 28, 2012 11:57 am
Location: UK

Re: Excel and python

Thu Mar 12, 2020 2:41 pm

Trigo, that advice from B Goode is probably the best answer you've had on this forum to date! I know it's really hard to do, but if you take a day or two out from trying to make an actual thing and just do a 'learn python' course you will make your life so much easier. To follow your youtube analogy - it's like the driver checking the route before she set off compared with stopping for directions at every street corner.

More specifically: the reason we genuinely can't help you is because the code snippet is obviously part of something larger. We know this because of functions having self in the arguments showing they're class methods. We have hardly any idea what the other method calls in the code do or what kind of information they return. So you won't be able to try pcmanbob's code as is - it will need to take into account the missing code from your snippet, and the fact that PID0110 is a class method.

Code: Select all

class AThing():
   """ large chunk of unkown code here """
   ...
    def PID0110(self, FreezeIndex = -1):
        Result = STRING_NO_DATA

        if '0110' in self.ValidPIDs:
            if FreezeIndex == -1:
                Response = self.GetResponse(b'0110\r')
                Response = self.PruneData(Response, 2)
            else:
                Response = self.GetResponse(bytearray("0210" + "{:02d}".format(FreezeIndex) + "\r", 'UTF-8'))
                Response = self.PruneData(Response, 3)
            Result =   (256 * int(Response[:2], 16) + int(Response[2:4], 16))/100
            
        return Result
    ...
    thing = AThing()
    
    with open("/home/pi/SAVE/CO2".csv" , "a") as file: #NB indent all rest
        While True:
            try:
                result = thing.PID0110(0110)
           
                print (result)
                result = str(result)
                file.write(result + "," + "\n")
                time.sleep(2)
                
also https://groups.google.com/forum/?hl=en-GB&fromgroups=#!forum/pi3d

User avatar
B.Goode
Posts: 10549
Joined: Mon Sep 01, 2014 4:03 pm
Location: UK

Re: Excel and python

Thu Mar 12, 2020 4:04 pm

Trigo wrote:
Wed Mar 11, 2020 9:50 pm
How do i print a result of a function on the terminal. Thank you!



This is not the only or best way, just an example.

Code: Select all

def square(x):
    y = x ** 2
    return y

result = square(3)
print(result)
Ref: https://www.digitalocean.com/community/ ... n-python-3

User avatar
Trigo
Posts: 106
Joined: Fri Jun 17, 2016 7:52 pm

Re: Excel and python

Tue Mar 17, 2020 5:32 pm

Thanks for all the help you are providing! Here is the github page for the code that i'm using: https://github.com/BirchJD/PiOBDII/blob ... /ELM327.py ! If someone could clarify it for me, awesome... I don't have much time and i try to understand as much as i can... :oops: the line that i've posted is 969
paddyg wrote:
Thu Mar 12, 2020 2:41 pm
Trigo, that advice from B Goode is probably the best answer you've had on this forum to date! I know it's really hard to do, but if you take a day or two out from trying to make an actual thing and just do a 'learn python' course you will make your life so much easier. To follow your youtube analogy - it's like the driver checking the route before she set off compared with stopping for directions at every street corner.

More specifically: the reason we genuinely can't help you is because the code snippet is obviously part of something larger. We know this because of functions having self in the arguments showing they're class methods. We have hardly any idea what the other method calls in the code do or what kind of information they return. So you won't be able to try pcmanbob's code as is - it will need to take into account the missing code from your snippet, and the fact that PID0110 is a class method.

Code: Select all

class AThing():
   """ large chunk of unkown code here """
   ...
    def PID0110(self, FreezeIndex = -1):
        Result = STRING_NO_DATA

        if '0110' in self.ValidPIDs:
            if FreezeIndex == -1:
                Response = self.GetResponse(b'0110\r')
                Response = self.PruneData(Response, 2)
            else:
                Response = self.GetResponse(bytearray("0210" + "{:02d}".format(FreezeIndex) + "\r", 'UTF-8'))
                Response = self.PruneData(Response, 3)
            Result =   (256 * int(Response[:2], 16) + int(Response[2:4], 16))/100
            
        return Result
    ...
    thing = AThing()
    
    with open("/home/pi/SAVE/CO2".csv" , "a") as file: #NB indent all rest
        While True:
            try:
                result = thing.PID0110(0110)
           
                print (result)
                result = str(result)
                file.write(result + "," + "\n")
                time.sleep(2)
                
Beat psychologically with a stick :?:

User avatar
paddyg
Posts: 2566
Joined: Sat Jan 28, 2012 11:57 am
Location: UK

Re: Excel and python

Tue Mar 17, 2020 7:05 pm

Ah, that's much more useful. I think I would try to save the values in PiOBDII.py as that's where the main loop happens. Best not to mess around with ELM327.py. I will have a rummage through the code and let you know any other thoughts/specific suggestions.

Does the code work on your car as it is? i.e. can you generate all the pygame displays, pdf files etc? Are you hoping to output all the PID data available for your car on each line of a spreadsheet - with each line representing an instant of time?
also https://groups.google.com/forum/?hl=en-GB&fromgroups=#!forum/pi3d

User avatar
Trigo
Posts: 106
Joined: Fri Jun 17, 2016 7:52 pm

Re: Excel and python

Tue Mar 17, 2020 7:36 pm

Well i've thinked different. I want to display only the current data. Like MAF, Throttle position, Engine coolant temp, Oil temp, intake air temp and RPM... After that i want to add two sensors to the raspian, one for CO and other for CO2. A third One could be added, for smoke opacity... I'm kind of noob... Thank you... I want to know what's the best car... All this data through time. I've not connected it to my car. I have a ECU simulator from www.imsapp.com, it supports 4 protocols... :geek:
Beat psychologically with a stick :?:

User avatar
paddyg
Posts: 2566
Joined: Sat Jan 28, 2012 11:57 am
Location: UK

Re: Excel and python

Tue Mar 17, 2020 9:55 pm

Here's a quick example of a possible approach after a rather superficial look at the code. There are lots of clever things built into the app such as thread locks and the freeze signalling that I have ignored so you will have to mess around to see what works and what doesn't. If you get stuck ask on the forum but give lots of info, especially full error listings.

This is in a file in the same directory as ELM327.py stop it with Ctrl C, will output to data_log.txt

Code: Select all

import ELM327
import time

TIMER_PERIOD = 0.5 # reading every 0.5s
DATA_FILE = "data_log.txt"
PID_LIST = ["010B", #inlet manifold abs press
            "010C", #rpm
            "010D"] #speed

this_ELM327 = ELM327.ELM327()
valid_pids = this_ELM327.GetValidPIDs()
for pid in PID_LIST:
    if pid not in valid_pids:
        print("PID{} is missing".format(pid))

next_time = time.time()
try:
    with open(DATA_FILE, "w") as f: #starts a new file each time "a" to append
        while True:
            now = time.time()
            if now > next_time:
                results = [this_ELM327.DoPID(pid) for pid in PID_LIST]
                f.write("{},{},{}\n".format(*results))
                next_time += TIMER_PERIOD
            time.sleep(0.01) #timing to 10ms
except KeyboardInterrupt:
    this_ELM327.Close()
PS I still *strongly* advise you to spend a while learning python properly!
Last edited by paddyg on Tue Mar 17, 2020 10:15 pm, edited 1 time in total.
also https://groups.google.com/forum/?hl=en-GB&fromgroups=#!forum/pi3d

User avatar
Trigo
Posts: 106
Joined: Fri Jun 17, 2016 7:52 pm

Re: Excel and python

Tue Mar 17, 2020 10:02 pm

I Will try to test it... What should i be reading for the learning? What about a educacional One... Made of paper... For kids... To make it easy... Thank you
Beat psychologically with a stick :?:

User avatar
Trigo
Posts: 106
Joined: Fri Jun 17, 2016 7:52 pm

Re: Excel and python

Thu Mar 19, 2020 10:51 pm

Hey... I now have two more sensors One for co and other for CO2... I don't know how to connect them.... 👌 I don't know if i have to buy another board... The two sensors are mg 811 and mq 9... Any pointers?
Beat psychologically with a stick :?:

pcmanbob
Posts: 9884
Joined: Fri May 31, 2013 9:28 pm
Location: Mansfield UK

Re: Excel and python

Thu Mar 19, 2020 11:03 pm

Trigo wrote:
Thu Mar 19, 2020 10:51 pm
Hey... I now have two more sensors One for co and other for CO2... I don't know how to connect them.... 👌 I don't know if i have to buy another board... The two sensors are mg 811 and mq 9... Any pointers?
Without knowing exactly which version of these sensor you have ( there are digital and analogue output versions ) no one can give you any useful advice.

These sensors also seem to be designed for indoor use to measure air quality.
We want information… information… information........................no information no help
The use of crystal balls & mind reading are not supported

User avatar
Trigo
Posts: 106
Joined: Fri Jun 17, 2016 7:52 pm

Re: Excel and python

Sun Mar 22, 2020 12:36 am

I've told you that it would work with two more sensors... I bought them but i don't know how they should connect... If i buy a groove base hat for the raspian they Will work together? The sensors are these two

https://ae01.alicdn.com/kf/HTB1GQAhXIfr ... ective.jpg
Beat psychologically with a stick :?:

pcmanbob
Posts: 9884
Joined: Fri May 31, 2013 9:28 pm
Location: Mansfield UK

Re: Excel and python

Sun Mar 22, 2020 10:40 am

Trigo wrote:
Sun Mar 22, 2020 12:36 am
I've told you that it would work with two more sensors...
If you telling us it will work then I guess you know what you doing and don't need our help any longer

good luck with your project.
We want information… information… information........................no information no help
The use of crystal balls & mind reading are not supported

User avatar
Trigo
Posts: 106
Joined: Fri Jun 17, 2016 7:52 pm

Re: Excel and python

Sun Mar 22, 2020 10:55 am

pcmanbob wrote:
Sun Mar 22, 2020 10:40 am
Trigo wrote:
Sun Mar 22, 2020 12:36 am
I've told you that it would work with two more sensors...
If you telling us it will work then I guess you know what you doing and don't need our help any longer

good luck with your project.
:lol: :lol: :lol: :lol: :lol: It was a joke? I hope it will work someday! Until there i hope you could breath clean air, or swim better... ;)
Beat psychologically with a stick :?:

User avatar
Trigo
Posts: 106
Joined: Fri Jun 17, 2016 7:52 pm

Re: Excel and python

Sun Mar 22, 2020 1:24 pm

Is it possible to connect 2 sensors one that needs 6v and the other needs 5v to the pi? If so how? Will you help me?
Beat psychologically with a stick :?:

Return to “Python”