Saving data in excel by cell rather than semi-colon with python-CodePudding

***Edit: I am originally saving the code to a .csv file but then later converting it to a .xlsx file.

I am trying to change my code to save the values in separate cells verses in the same cell separated by a semicolon. I currently have code that saves the values (8 values per row) in a single cell separated by a semicolon and then have a separate set of code that I run to do text-to-columns. I am not well versed in Python but I think I should just be able to edit the chunk of code that saves the values to the csv file to do this.

Here is the chunk of code that saves the data to the excel file.

# Stores data in Excel File
with open(output, "w") as fp:
     for line in data:
         fp.write("; ".join(str(x) for x in line))
         fp.write("\n")

Just in case, here is the code that gathers the data and how its stored

def recordData(memory_service):
    """ Record the data from ALMemory.
        Returns a matrix of values
    """
    print "Recording data ..."
    data = list()
    startTime = time.time()
    s = 9.06

    while True:
        currentTime = time.time()
        elapsedTime = currentTime - startTime
        line = list()

        for key in ALMEMORY_KEY_NAMES:
            value = memory_service.getData(key)
            line.append(value)
            print value
        data.append(line)

        if elapsedTime > s:
            return data

Lastly here is a picture of how the code saves: Excel Data

I am using Python 2.7.18 and PyCharm 2022.1.2.

***Edit: Code that converts .csv to .xlsx

# Reads in the .csv file and converts it to .xlsx
    readFile = pd.read_csv(r'C:\Users\PycharmProjects\FsrValues5.csv')
    readFile.to_excel(r'C:\Users\PycharmProjects\Excel Data\New Trials\FsrValues5.xlsx', index=None,
                      header=True)

CodePudding user response：

Creation of the CSV file is not problematic. The issue is this:

readFile = pd.read_csv(r'C:\Users\PycharmProjects\FsrValues5.csv')

as was noted in the comments:

You just have to make sure the system importing/reading the CSV file knows what symbol to use for the delimiter.

Specify the sep since you're not using the default separator ,.

readFile = pd.read_csv(r'C:\Users\PycharmProjects\FsrValues5.csv', sep=';')

CodePudding user response：

I'm not sure if this is possible without an excel module. I use Openpyxl. I believe without using modules, python can't properly use .xlsx files.

In openpyxl, you can assign to specific cells:

import openpyxl as op
wb = op.load_workbook("Path/to/workbook")
ws = wb["Name of worksheet to be used"]
ws['A1'] = "x"
ws['B2'] = "y"

You could then use split() method on your data to get it without the semicolons. Then use a loop to loop through the cells and paste data where you want it