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

[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")