Hey There,
Theres a few ways to do this, but before I launch into it I just wanted to make sure you were aware that excel can actually import and open CSV files itself, so no need for much coding. Just open the CSV file with excel, do your data manipulation, and save it as a .xlsx native Excel file. Baring that won’t work for you or you just really want to learn how to do this in Python, here you go:
Step 1) Install a Python library that can work with excel files. I’m using openpyxl in this example
Shell:
$ pip install openpyxl
Python:
Method 1) - Easiest method, but might produce a memory error on certain systems if the Excel document to be written is on the large side. As a note, CSV files are read in as Python Lists. Each line of the CSV file will correspond to a list, each item separated by a comma in the line will become a list item.
Ex: Line1: “Line, url, Ip, lmt, mime, itag, dur, clen, rbuf”
will look like this in Python: [“Line”, “url”, “Ip”, “lmt”, “mime”, “itag”, “dur”, “clen”, “rbuf”]
We will be enumerating over these lists
Make sure you place the CSV document in the working directory of your Python environment so that you can find it easily, or more advanced filepath manipulation will be required.
import csv # For CSV stuff, part of the standard Python library
from openpyxl import Workbook # For Excel stuff
csv_file_path = 'examplecsv.csv' # The filename of the CSV file
excel_filename = 'exampleCSVtoXLSX.xlsx' # The filename we'll title the excel doc
with open(csv_file_path, 'r') as file:
csv_doc = csv.reader(file) # This method opens the file and will handle closing it when we're done
wb = Workbook() # Declare a new openpyxl Excel workbook
ws1 = wb.active # Make this the active worksheet in the Excel doc
for r, row in enumerate(csv_doc): # Loop through each row (CSV line) one-by-one
for c, col in enumerate(row): # Loop through each col (comma seperated item) in each row
ws1.cell(row=r+1, column=c+1, value = col) # Append a cell to the worksheet at cell position (r+1,c+1), because they write non-zero indexed...oddly, with value of the item
wb.save(excel_filename) # Save the Excel document
Method 2) - Preferred for writing large Excel documents. This uses far less memory than method 1 but isn’t quite as straightforward for a beginner.
import csv # For CSV stuff, part of the standard Python library
from openpyxl import Workbook # For Excel Stuff
from openpyxl.worksheet.write_only import WriteOnlyCell
csv_file_path = 'examplecsv.csv' # The filename of the CSV file
excel_filename = 'exampleCSVtoXLSX.xlsx' # The filename we'll title the excel doc
with open(csv_file_path, 'r') as file:
csv_doc = csv.reader(file) # This method opens the file and will handle closing it when we're done
wb = Workbook(write_only=True) # Declare a new openpyxl Excel Workbook in Write Only Mode
ws1 = wb.create_sheet() # Create a new worksheet within that workbook, one isn't auto created like the last method
for r, row in enumerate(csv_doc): # Enumerate over the CSV, row by row
row_to_append = []
for c, col in enumerate(row): # In every row, enumerate over each column
cell = WriteOnlyCell(ws1, value=col) # Declare a new Cell with the value associated with (row, col)
row_to_append.append(cell) # Add each new cell to a temporary list
ws1.append(row_to_append) # Append that list as a new row of cells in the worksheet
wb.save(excel_filename) # Save the workbook to the current working directory
Hopefully that helps. Good luck!
EDIT: Spelling