When you need to extract data from a system you can bet that someone will claim they have to have it in a Microsoft Excel format rather than the simple CSV format you had in mind. I’m unlikely to need to tell you this – as you’re already here – but that there are huge and annoying differences between these two formats. However, I’m not going to list them here, instead I want to introduce you to a nice python library: openpyxl ( https://pypi.python.org/pypi/openpyxl ).
This beautiful Excel library is a great example of how a good library should be written – it takes a complex problem and hides it away behind a nicely designed interface, and having written an Excel parser myself I can assure you that was unlikely to be an easy task! Basically, this library lets you read and write Excel files and requires next to no knowledge of the Excel format.
My little piece of code takes a list of CSV files with a name and inserts them all into a new excel file, you’ll get one sheet per CSV. It also improves the display by bolding the first line (assuming these are column titles) and adjusts the width of each column depending on the size of the strings within the column.
You can find the full code here: https://bitbucket.org/akademy/csvtoexcel/src/e92654d86ba35e221332c4365c78662b48f274c0/excel_writer.py?at=master&fileviewer=file-view-default
And you can use it like this:
ew = ExcelWriter()
ew.convert( {
# Specify the csv files, whether it has column titles (default: yes) and the names
"sheets" : [
{
"filelocation" : "test/work.csv",
"sheetname" : "Works"
},
{
"filelocation" : "test/person.csv",
"sheetname" : "People",
"has_titles" : False
}
],
# Specify the output name
"outputname" : "test/test_outout_file.xlsx"
}
openpyxl can do much more – pretty much anything you can do in Excel – check out the docs for more info: https://openpyxl.readthedocs.io
So install the library and run the code!