[FIXED] How to copy multiple .xlsx files into a respective .csv file?

Issue

I have 24 excel files, I'm aiming to copy the .xslx data and to their respective 24 .csv files. I have copied the data over however its creating 10 copies in the .csv files, I believe it has something to do with the for loops. Ive tried to use writerow() rather than writerows() yet that does help. I'm trying to understand openpyxl and its writer and reader objects.

import openpyxl, os, csv
from pathlib import Path

for excelFile in os.listdir('./excelspreadsheets'):
    if excelFile.endswith('.xlsx'): # Skip non xlsx files, load the workbook object
        wb = openpyxl.load_workbook('./excelspreadsheets/' + excelFile)
        for sheetName in wb.sheetnames:

            # Loop through every sheet in the workbook
            sheet = wb[sheetName]
            sheetTitle = sheet.title

            # Create the CSV filename from the Excel filename and sheet title
            p = Path(excelFile)
            excelFileStemName = p.stem
            CsvFilename = excelFileStemName + '_' + sheetTitle + '.csv'

            # Create the csv.writer object for this CSV file
            print(f'Creating filename {CsvFilename}...')
            outputFile = open(CsvFilename, 'w', newline='')
            outputWriter = csv.writer(outputFile)

            # Create reader object for each excel sheet
            fileObj = open('./excelspreadsheets/' + excelFile)
            fileReaderObj = csv.reader(fileObj)

            # Loop through every row in the excel sheet
            for rowNum in range(1, sheet.max_row + 1):
                rowData = [] # append each cell to this list
                
                # Loop through each cell in the row
                for colNum in range(1, sheet.max_column + 1):
                    rowData.append(sheet.values)

            # write the rowData list to the CSV file. 
            for row in rowData:
                outputWriter.writerows(row)

            outputFile.close()

So, each of the newly created .csv files writes the correct data but does it 10 times, rather than once.

Appreciate any feedback thanks.

Solution

You can use read_excel and to_csv, which come as part of pandas to read excel file and write the data to csv file. It is just simpler from coding perspective, as the read and write will be done in one line. It also uses Openpyxl underneath. The updated code is below.

import openpyxl, os, csv
from pathlib import Path
import pandas as pd
for excelFile in os.listdir('./excelspreadsheets'):
    if excelFile.endswith('.xlsx'): # Skip non xlsx files, load the workbook object
        xls = pd.ExcelFile('./excelspreadsheets/' + excelFile)
        for sheetname in xls.sheet_names:
            #Read each sheet into df
            df = pd.read_excel('./excelspreadsheets/' + excelFile, sheetname)
            #Remove .xlsx from filename and create CSV name
            CsvFilename = excelFile.rstrip('.xlsx') + '_' + sheetname + '.csv'
            print(f'Creating filename {CsvFilename}...')
            #Write df as CSV to file
            df.to_csv(CsvFilename, index=False)

Let me know if you see any errors...

Answered By - Redox

Answer Checked By - Marilyn (PHPFixing Volunteer)

Read More

[FIXED] How to read comma separated string in one cell using Python

Issue

I have a project wherein you need to read data from an excel file. I use openpyxl to read the said file. I tried reading the data as string first before converting it to an integer; however, error is occurring because of, I think, numbers in one cell separated by comma. I am trying to do a nested list but I still new in Python.

My code looks like this:

# storing S
S_follow = []
for row in range(2, max_row+1):
  if (sheet.cell(row,3).value is not None):
  S_follow.append(sheet.cell(row, 3).value);
# to convert the list from string to int, nested list
for i in range(0, len(S_follow)):
S_follow[i] = int(S_follow[i])
print(S_follow)

The data I a trying to read is: ['2,3', 4, '5,6', 8, 7, 9, 8, 9, 3, 11, 0]

hoping for your help

Solution

When you're about to convert the values to integers in the loop on the second-last line of your script, you can check if each value is an integer or string and if it is a string, just split it, convert the split values to integers and push them to a temporary list called say, strVal and then append that temp list to a new list called, say S_follow_int. But if the value is not a string, then just append them to S_follow_int without doing anything.

data= ['2,3', 4, '5,6', 8, 7, 9, 8, 9, 3, 11, 0]
S_follow = []
S_follow_int = []

for row in range(0, len(data)):
  if (sheet.cell(row,3).value is not None):
      S_follow.append(sheet.cell(row, 3).value);
      
# to convert the list from string to int, nested list
for i in range(0, len(S_follow)):
    
    #if the current value is a string, split it, convert the values to integers, put them on a temp list called strVal and then append it to S_follow_int
    if type(S_follow[i]) is str:
        x = S_follow[i].split(',')
        strVal = []
        for y in x:
            strVal.append(int(y))
        S_follow_int.append(strVal)
        
    #else if it is already an integer, just append it to S_follow_int without doing anything

    else:
        S_follow_int.append(S_follow[i])
    
print(S_follow_int)

However, I would recommend that you check the datatype(str/int) of each value in the initial loop that you used to retrieved data from the excel file itself rather than pushing all values to S_follow and then convert the type afterwards like this:

#simplified representation of the logic you can use for your script

data = ['2,3', 4, '5,6', 8, 7, 9, 8, 9, 3, 11, 0]
x = []

for dat in data:
    if dat is not None:
        if type(dat) is str:
            y = dat.split(',')
            strVal = []
            for z in y:
                strVal.append(int(z))
            x.append(strVal)
        else:
            x.append(dat)
print(x)

Answered By - AndrewL64

Answer Checked By - Cary Denson (PHPFixing Admin)

Read More

[FIXED] How can I convert Cell of Openpyxl from Text to Number format?

Issue

I wrote a code to convert a text file into excel file using Openpyxl extension of Python.

Although the value are setting properly into the column but they are showing as a text instead of number. Although I tried to convert, seems like it is not working.

Can anyone please correct the code?

import csv
import openpyxl
import openpyxl as oxl

input_file = r'C:\Python\Test.txt'
output_file = r'C:\Python\Test.xlsx'

wb = oxl.Workbook()
ws = wb.active
ws.number_format = 'General'
ws.title = "Waveform"
#ws = wb.create_sheet(title='Waveform')


with open(input_file, 'r') as data:
    reader = csv.reader(data, delimiter='\t')
    for row in reader:
        ws.append(row)


for row in range(2, ws.max_row+1):
    ws["{}{}".format("A", row)].number_format = 'General'
    ws["{}{}".format("B", row)].number_format = 'General'

wb.save(output_file)

Here is the output excel file

Solution

the read data from txt file will be in string. So, as suggested by jezza, you need to convert list to float. You don't need the 'number_format` lines you have. Updated code is here. Note that the conversion map assumes all data can be converted to float (no text). The try/catch will basically skip the row if there is text on any row

import csv
#import openpyxl
import openpyxl as oxl

input_file = r'C:\Python\Test.txt'
output_file = r'C:\Python\Test.xlsx'

wb = oxl.Workbook()
ws = wb.active
#ws.number_format = 'General'
ws.title = "Waveform"
#ws = wb.create_sheet(title='Waveform')

with open(input_file, 'r') as data:
    reader = csv.reader(data, delimiter='\t')
    for row in reader:
        try:
            row = list(map(float, row))
            ws.append(row)
        except:
            print("Skipping row ", row)
            pass

#for row in range(2, ws.max_row+1):
#    ws["{}{}".format("A", row)].number_format = 'General'
#    ws["{}{}".format("B", row)].number_format = 'General'

wb.save(output_file)

Output

Answered By - Redox

Answer Checked By - Senaida (PHPFixing Volunteer)

Read More

[FIXED] How to split the combined graph into a single graph separated in openpyxl

Issue

Required to display the graph plotted by taking each row in X-Axis over a column set as Y-Axis. Here is my data imported from an Excel sheet and displayed into a separate Excel sheet.

from openpyxl import Workbook
from openpyxl.chart import (
    Reference,
    BarChart3D
)

wb = Workbook()
fileItemPath = os.path.join(os.path.dirname(__file__), "Data.xlsx")
print('Input_FilePath: {}'.format(fileItemPath))
wb = openpyxl.load_workbook(fileItemPath)
sheet = wb['TMO_Reliability_Stats_Combined']

# This part of the code assigning to data causing the whole trouble...
data = Reference(sheet, min_col=2, min_row=2, max_col=2, max_row=6)

titles = Reference(sheet, min_col=2, max_col=2, min_row=1, max_row=1)
chart = BarChart3D()
chart.title = "3D Bar Chart"
chart.add_data(data=data, titles_from_data=True, from_rows=True)
chart.set_categories(titles)
sheet.add_chart(chart, "A10")
wb.save("bar3d.xlsx")

In the above code, I am not able to assign the correct values data = Reference(sheet, min_col=2, min_row=2, max_col=2, max_row=6)

Actual Result: Displaying combined graph instead of single.

Expected Result: Single graph needs to be plotted separately. The combined needs to be eliminated as shown in the snapshot

Solution

I'm uncertain of exactly why you are getting this error, but I believe it has to do with you using the openpyxl bar chart in a way it wasn't quite intended. It could just as easily something to do with Excel. Deep diving into the openpyxl source code for this module hasn't given me an answer yet.

However, altering your data to include the first column does solve your issue. The only way I could get a seperate graph for 2021 was to move the data and use cases so they were a seperate pair. While this isn't an ideal solution if you have many years, it does work as you intended.

from openpyxl import Workbook
from openpyxl.chart import (
    Reference,
    BarChart3D
)
import os
import openpyxl

wb = Workbook()
fileItemPath = os.path.join(os.path.dirname(__file__), "Data.xlsx")
print('Input_FilePath: {}'.format(fileItemPath))
wb = openpyxl.load_workbook(fileItemPath)
sheet = wb['Sheet1']

# Copying/moving 2021 data
for row in sheet.iter_rows(min_col=1, max_col=5):
    # Copy data
    colA = row[0].value
    colC = row[2].value

    # Delete colC
    row[2].value = None

    # Paste data
    row[3].value = colA
    row[4].value = colC

# 2020 chart
data = Reference(sheet, min_col=1, min_row=2, max_col=2, max_row=6)
titles = Reference(sheet, min_col=2, min_row=1)
chart = BarChart3D()
chart.title = "3D Bar Chart"
chart.add_data(data=data, titles_from_data=True, from_rows=True)
chart.set_categories(titles)
sheet.add_chart(chart, "A10")

# 2021 chart
data = Reference(sheet, min_col=4, min_row=2, max_col=5, max_row=6)
titles = Reference(sheet, min_col=5, min_row=1)
chart = BarChart3D()
chart.title = "3D Bar Chart"
chart.add_data(data=data, titles_from_data=True, from_rows=True)
chart.set_categories(titles)
sheet.add_chart(chart, "J10")

# Saving
wb.save("bar3d.xlsx")

Outputted bar charts:

Answered By - jezza_99

Answer Checked By - Robin (PHPFixing Admin)

Read More

[FIXED] How to append columns instead of rows openpyxl?

Issue

Currently using Openpyxl. I understand that append will print the output in excel in rows. I am trying to figure out if there is a function that can print the output in columns .eg:

headings = ['Name','Fruits']
ws.append(headings)

Name = ['John','Ben','Lily','Sarah']
Fruits = ['Orange','Apple','Grape','Peach']

Output in excel:

    A         B
 1 Name     Fruits
 2 John     Orange
 3 Ben      Apple
 4 Lily     Grape
 5 Sarah    Peach

Solution

The information you are looking for is shown here. The example shows how to iterate over columns and rows, which just ends up being simple grid/matrix logic.

from itertools import cycle

c = cycle([1, 2])

for idx in range(len(Name)):
    ws.cell(column=next(c), row=idx, value=Name[idx])
    ws.cell(column=next(c), row=idx, value=Fruits[idx])

# Column 1, Row 0 John
# Column 2, Row 0 Orange
# Column 1, Row 1 Ben
# Column 2, Row 1 Apple
# etc...

Answered By - gold_cy

Answer Checked By - Senaida (PHPFixing Volunteer)

Read More

[FIXED] How to supress UserWarings in Openpyxl

Issue

I get the UserWarning 'C:\Users...\Anaconda3\lib\site-packages\openpyxl\reader\drawings.py:58: UserWarning: wmf image format is not supported so the image is being dropped warn(msg)' How can I suppress this warning message?

Solution

stovfl answered this question:

To add WMF read or write support to your application, use PIL.WmfImagePlugin.register_handler() to register a WMF handler. You have to patch openpyxl as well, dropping WMF is hardcoded, see OpenPyXL - find_images

answer

Answered By - ConSod

Answer Checked By - Katrina (PHPFixing Volunteer)

Read More