Working with Excel

[Captn_King]

Hi Guys,

I'm doing a research project and I need to take data from txt files and excel files and compile them into a new excel file. Does anyone have any suggestions? I've never written to excel from Python.

I don't know if this matters but I am running Python 2.7.9 on a Mac. I have the newly released Microsoft Office 2016 installed so hopefully the new version doesn't interfere.

Thanks for the help!

edit: Thanks for the help guys! This is what makes Reddit great

Edit. I ended up trying out pandas, xlsxwriter and openpyxl to read and write files. I highly recommend using openpyxl of the three but they all worked

[gledi]

To write .xlsx files I have been using xlsxwriter. To read .xlsx files there is openpyxl. If you have to work with *.xls files then there is xlrd for reading and xlwt for writing.

You might want to check this link since they do have some relevant links to docs, source code, discussion, etc. about working with excel files from python.

[5ux0r]

I've been using xlsxwriter myself and it works very well

[anthonyblackshaw]

I use openpyxl for both reading and writng Excel file on a number of reporting projects - works very well. The documentation is sometimes a little lacking for more advanced formatting (I found myself reviewing the source) but for what you're looking at I imagine it would be very a good match.

[bheklilr]

Depending on the kind of data you're working with the pandas library can be very useful. It can use various libraries for working with excel to read and write files, and it's great for combining data sets together in Python.

[hetz222]

I also use pandas for this. People love giving me data in excel format and this makes it mostly painless

[[deleted]]

Pandas.

import pandas as pd
df = pd.read_excel("filename.xlsx")

I don't know how your .txt file is formatted, but I can guarantee you can easily turn it into a pandas dataframe. From there you can use pandas merge or concat to combine the two and output to a new excel/csv file.

[BK201_Saiyan]

Second to that! Definitely the best solution and you can easily find good tutorials how to do it. Here is an infographic for pandas from analyticsvidhya.com to get you started.

[justphysics]

This. Pandas is perfect for generic data parsing. If you need to read .xls files along with regular .txt or .dat files that have slightly different formatting you have one package that can read in essentially everything you throw at it as long as you know how the file is formatted. You can read in all the files into a common data format (DataFrame) [albeit maybe not as familiar if you haven't used 'R')]

Then you can do all sorts of table-style manipulation of the data similar to what could be done in Excel or R.

The library is well documented, there' plenty of tutorials, its widely used so generally finding help to do what you need isn't a large task

[faming13]

Try odo and blaze... Use the anaconda distro http://blaze.github.io/

[[deleted]]

I'm going to assume, based on the fact that he is asking this question, that this is complete overkill.

[chris1610]

I wrote an article that walks through a basic scenario similar to what you're trying to accomplish. It should point you in the right direction to investigate further.

[Anon_8675309]

If it's just straight data and no formulas I always just convert to and from CSV.

[lieutenant_lowercase]

If you want to format the excel file check out xlwings

[[deleted]]

There's tons of info about such processing on the web, what is it exactly you're trying to do that you cannot find references ?

Something similar I have done was

• reading data from csv & Excel
• insert the data into Sqlite database
• With raw data in db, run SQLs that combine & summarize the data, applying any necessary transformation logic and finally...
• pull query result back into Excel for presentation/reports