Writing Python Output to excel file-CodePudding

I am new to programming. What I am doing probably the probably very simple for an experienced coder. My Python program successfully does the following:

it reads single line of Error and Success message from .log file
these .log file are stored in my local system

And I want to write these output of my code to excel or CSV file. Any suggestion or help any suggestion or help How to do that ? I am able to write it to text file but I want to write it to excel or CSV file.

import glob

file_list = glob.glob('C:\\Users\\Rahul\\AppData\\Roaming\\JetBrains\\PyCharmCE2021.3\\*.log')

for file in file_list:
    file_in = open(file, "r").readlines()
    print(file)
    for line in file_in: # Loop every line
        if 'ERROR' in line: # Search for ERROR in line
            print(line) # Print line
        elif 'SUCCESS' in line: # Search for SUCCESS in line
            print(line) # Print line
        else:
            pass

Any suggestion or help ?

CodePudding user response：

Your question is quite vague. Let me try. CSV is "Comma Separated Values". i.e., the data in the file are separated by a comma (usually). But the delimiter can by anything such as ";", tab etc. This delimiter separates the columns and each line signifies a row. So, a CSV file in raw text format would look like

1,2,3
4,5,6
7,8,9

When you open the file in spreadsheet format (excel, google sheet etc.) each number will be in a separate cell.

If you want to write data to a CSV file, you can just write it as raw text from python. All you need is to create the file with .csv extension. Let's say you add an error counter to your loop. You can output it like this,

for file in file_list:
    file_in = open(file, "r").readlines()
    file_out = open(file.strip(".log")   ".csv", "w")    #creating a csv file
    count = 0
    for line in file_in: # Loop every line
        if 'ERROR' in line: # Search for ERROR in line
            count  = 1
            file_out.write(str(count)   ","   line   "\n")    #writing csv file
        elif 'SUCCESS' in line: # Search for SUCCESS in line
            print(line) # Print line

This will create a CSV file with two columns, index (count) and error message. You can simplify it using pandas https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_csv.html

For excel (.xlsx) format, there's a method in pandas https://pandas.pydata.org/docs/reference/api/pandas.ExcelWriter.html Another popular library is Openpyxl, https://openpyxl.readthedocs.io/en/stable/ I've never used it before, so, don't know how it works. If you explain your problem in more details, may be I can help more.

CodePudding user response：

Try pandas. It is ideal for data engineering. Also, it has a built-in function to create CSV files.

Below you can see a small example. First I do some set up variables to create an empty data frame to work with. I also make a dummy file to work with according to your specifications. After that, I read the file, and strip unneeded spaces en sorts. Then I create a Series object to add to the data frame. These series get merged with the correct data. After this I save the data frame to a CSV file.

Please note, this is a simple demo. You might want to change it to your needs.

import pandas as pd

columns = ['LOG_STATUS', 'MESSAGE']
df = pd.DataFrame(columns=columns, )

text = "ERROR LOG: \nSUCCESS LOG: "

with open('file.txt', 'w ') as file:
    file.write('ERROR: log messaging error\n')
    file.write('SUCCESS: log messaging success\n')

with open('file.txt', 'r') as file:
    for line in file.readlines():
        line = line.strip()
        if 'ERROR' in line:
            df2 = pd.Series({'LOG_STATUS': 'ERROR', 'MESSAGE': line})
        elif 'SUCCESS' in line:
            df2 = pd.Series({'LOG_STATUS': 'SUCCESS', 'MESSAGE': line})
        df = pd.DataFrame(df.append(df2,
                                    ignore_index=True), columns=columns)
print(df)

df.to_csv('file_name.csv', encoding='utf-8')

https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_csv.html https://www.geeksforgeeks.org/python-pandas-dataframe-append/ https://towardsdatascience.com/how-to-export-pandas-dataframe-to-csv-2038e43d9c03