Trying to Get Arduino sensor data over to excel using Python.

[eh5713]

I have most of the code written. I want to add the first two columns of the csv file to be date and time. And, have DATE and TIME in the headers. When I comment out the date and time, it exports everything uniformly, just under the wrong columns.

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Created on Wed Nov  2 23:17:11 2022

@author: eh5713
"""
#script to communicate with arduino and load serial monitor data
#Your sketch/code should be already loaded/flashed to the board prior to running this script
#However communication port should not be occupied
#I usually close Arduino software after flashing the code to be on the safe side

#we need serial module to communicate with the arduino
#csv module is needed because we will be logging sensor data to a csv file

import serial
import csv
from datetime import date,datetime


arduino_port="COM3" #serial port of arduino
#Port that arduino uses - for my mac it is usbmodem.., on windows it will be COM3-4
baud = 9600
#defining the file we will use for data loading

#creates a file named by the user's input
filename = input("Input the Filename: ")
f_extns = filename.split(".csv")
print ("The extension of the file is : " + repr(f_extns[-1]))


#filename = "data_loaded.csv"

# open serial port
ser = serial.Serial(arduino_port, baud)

#print out the active port
print("Connected to Arduino port:" + arduino_port)
#we create 'data_loaded.csv file'
file = open(filename,'w')
print("File created!")

#creating empty lists for data to be taken



#this is a loop that performs 20 consecutive readings
samples = 20
print_labels = False
line = 0
sensor_data = []
#dates = []
#instance = []

while line <= samples:
    getData=ser.readline()
    dataString = getData.decode('utf-8')
    data = dataString[0:][:-2]
    print(data)

    # datetime object containing current date and time
    #data_taken = datetime.now()
    #data_taken_date=data_taken.strftime('%m/%d/%Y')
    #dates.append(data_taken_date)
    #data_taken_time=data_taken.strftime('%H:%M:%S')
    #instance.append(data_taken_time)

    readings = data.split(",")
    print(readings)
    sensor_data.append(readings)
    print(sensor_data)
    #overall=list(zip(dates,instance,sensor_data))

    line = line + 1
with open(filename, 'w',encoding = 'UTF8', newline= '') as f:
    writer = csv.writer(f)
    writer.writerows(date,sensor_data)
       
#closing the port
file.close()
ser.close()

Yoriz write Dec-01-2022, 06:14 AM:
Please post all code, output and errors (in their entirety) between their respective tags. Refer to BBCode help topic on how to post. Use the "Preview Post" button to make sure the code is presented as you expect before hitting the "Post Reply/Thread" button.

[deanhystad]

The reason this didn't work:

overall=list(zip(dates,instance,sensor_data))

Is that sensor_data is a list of lists so you get date, time, [sensor, data, as, a, list]. You need to flatten the sensor data. This is most easily done while the data is collected.

import csv
from datetime import datetime

class Serial:
    """My fake serial port listening to my imaginary Arduino"""
    def __init__(self, *args, **kwargs):
        print("opening", args, kwargs)

    def close(self):
        print("closing")

    def __enter__(self, *args, **kwargs):
        return self

    def __exit__(self, *args, **kwargs):
        self.close()

    def readline(self):
        return b"1,2,3,4,5\r\n"

labels = ["date", "time", "s1", "s2", "s3", "s4", "s5"]
samples = 20
rows = []

with Serial("COM3", 9600) as ser:
    for _ in range(samples):
        data = ser.readline()[:-2].decode('utf-8').split(",")
        now = datetime.now()
        rows.append([
            now.strftime('%m/%d/%Y'),
            now.strftime('%H:%M:%S'),
            *data
        ])

with open("test.csv", "w", encoding = 'UTF8', newline= '') as csv_file:
    writer = csv.writer(csv_file)
    writer.writerow(labels)
    writer.writerows(rows)

You don't need to use the csv library to write a csv file. Since the data is already comma delimited, you are just adding work.

labels = "date,time,s1,s2,s3,s4,s5\n"
samples = 20
rows = []

with Serial("COM3", 9600) as ser:
    for _ in range(samples):
        data = ser.readline()[:-2].decode('utf-8')
        now = datetime.now()
        rows.append("{},{},{}\n".format(
            now.strftime('%m/%d/%Y'),
            now.strftime('%H:%M:%S'),
            data))

with open("test.csv", "w", encoding = 'UTF8') as csv_file:
    csv_file.write(labels)
    csv_file.writelines(rows)

I don't think it makes sense to store all the data and then write it to the CSV file. Write the data as it is read. I would write your code like this:

labels = "date,time,s1,s2,s3,s4,s5\n"
samples = 20
with Serial("COM3", 9600) as ser:
    with open("test.csv", "w", encoding = 'UTF8', newline= '') as csv_file:
        csv_file.write(labels)
        for _ in range(samples):
            data = ser.readline()[:-2].decode('utf-8')
            now = datetime.now()
            row = "{},{},{}\n".format(
                now.strftime('%m/%d/%Y'),
                now.strftime('%H:%M:%S'),
                data)
            csv_file.write(row)

Why are you making separate columns for date and time. This makes date and time difficult to work with. Your date format is also a poor choice. I know month/day/year is a common convention, but it is worthless for sorting or comparison. I would have a single datetime column instead and use the default format.

labels = "time,s1,s2,s3,s4,s5\n"
samples = 20
with Serial("COM3", 9600) as ser:
    with open("test.csv", "w", encoding = 'UTF8', newline= '') as csv_file:
        csv_file.write(labels)
        for _ in range(samples):
            data = ser.readline()[:-2].decode('utf-8')
            csv_file.write(f"{datetime.now()},{data}\n")