Openpyxl Convert CSV to Excel

openpyxl convert CSV to EXCEL

import csv
from openpyxl import Workbook
from openpyxl.cell import get_column_letter

f = open(r'C:\Users\Asus\Desktop\herp.csv')

csv.register_dialect('colons', delimiter=':')

reader = csv.reader(f, dialect='colons')

wb = Workbook()
dest_filename = r"C:\Users\Asus\Desktop\herp.xlsx"

ws = wb.worksheets[0]
ws.title = "A Snazzy Title"

for row_index, row in enumerate(reader):
    for column_index, cell in enumerate(row):
        column_letter = get_column_letter((column_index + 1))
        ws.cell('%s%s'%(column_letter, (row_index + 1))).value = cell

wb.save(filename = dest_filename)

Python CSV to Excel sheet by sheet with openpyxl [NOT OPTIMIZED]

To create an Excel spreadsheet with 8 different sheets (one per CSV file) you could use the following approach:

import openpyxl
import csv
import os

wb = openpyxl.Workbook()
del wb[wb.sheetnames[0]]        # Remove the default 'Sheet1'

for filename in ['file1.csv', 'file2.csv', 'file3.csv']:
    with open(filename, newline='') as f_input:
        ws = wb.create_sheet(title=os.path.basename(filename))
        
        for row in csv.reader(f_input, delimiter=';'):
            ws.append(row)
        
wb.save('output.xlsx')

1. First create an Excel workbook to store you CSV data in (and remove the default sheet).

2. For each file create a new worksheet. Give the sheet the same name as the filename (assumes the filenames are not too long).

3. For each row in the CSV file, use ws.append() to write the row into the worksheet.

4. When all files are processed, write the whole Excel spreadsheet out.

---

If you have an existing Excel spreadsheet to update, the following approach could be used:

import openpyxl
import csv
import os

wb = openpyxl.load_workbook('data.xlsx')

sheets = [
    ('file1.csv', 'Sheet A'),
    ('file2.csv', 'Sheet B'),
    ('file3.csv', 'Sheet C')
]

for filename, sheet in sheets:
    with open(filename, newline='') as f_input:
        ws = wb[sheet]
        
        for rowy, row in enumerate(csv.reader(f_input, delimiter=';'), start=1):    # make start first row number
            for colx, value in enumerate(row, start=1):                             # make start first col number    
                ws.cell(column=colx, row=rowy, value=value)
        
wb.save('data.xlsx')

This first reads the existing template file in an overwrites the existing entries (starting top left at 'A1')

Python convert csv to xlsx

Here's an example using xlsxwriter:

import os
import glob
import csv
from xlsxwriter.workbook import Workbook

for csvfile in glob.glob(os.path.join('.', '*.csv')):
    workbook = Workbook(csvfile[:-4] + '.xlsx')
    worksheet = workbook.add_worksheet()
    with open(csvfile, 'rt', encoding='utf8') as f:
        reader = csv.reader(f)
        for r, row in enumerate(reader):
            for c, col in enumerate(row):
                worksheet.write(r, c, col)
    workbook.close()

FYI, there is also a package called openpyxl, that can read/write Excel 2007 xlsx/xlsm files.

Hope that helps.

Openpyxl/Pandas - Convert CSV to XLSX

I found the solution on this one.

I just added the encoding param as utf-8 and it worked fine.

with open(pathcsv, 'r+', encoding="utf-8") as f:

Thanks everyone for your help. It was a nice first post :)

openpyxl python - writing csv to excel gives 'number formatted as text'

You need to convert the value from the CSV file to what you need. All values in CSV files are strings.
ws.cell('%s%s'%(column_letter, (row_index + 1))).value = int(cell) ought to do it.

BTW. you might want to look at the ws.append() method.

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

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

---

Related Topics