I use a python script found on stackoverflow to read in multiple csv files and create an excel workbook with separate sheets corresponding to each csv file.
Here is the code.
Code:
#!/usr/bin/python
import csv, xlwt, os
data_folder_path = '/home/path/to/my/cvs/files'
def input_from_user(prompt):
return raw_input(prompt).strip()
def make_an_excel_file_from_all_the_txtfiles_in_the_following_directory(directory):
wb = xlwt.Workbook()
for filename in os.listdir(data_folder_path):
#if filename.endswith(".csv") or filename.endswith(".txt"):
if filename.endswith(".csv"):
ws = wb.add_sheet(os.path.splitext(filename)[0])
with open('{}/{}'.format(data_folder_path,filename),'rb') as csvfile:
reader = csv.reader(csvfile, delimiter=',')
for rowx, row in enumerate(reader):
for colx, value in enumerate(row):
ws.write(rowx, colx, value)
return wb
if __name__ == '__main__':
path_to_data = input_from_user("Where is the data stored?: ")
xls = make_an_excel_file_from_all_the_txtfiles_in_the_following_directory(path_to_data)
xls_name = input_from_user('What do you want to name the excel file?: ')
xls.save('{}/{}{}'.format(data_folder_path,xls_name,'.xls'))
print "Your file has been saved in the data folder."
This does work however, it formats the cells in away that breaks cell references. For example, suppose I have a sheet called 'Teams' and cell B2 of Teams has the text value "Tigers". Further suppose that in one of my csv files I have '=Teams.B2' as one of the comma separated values. The spreadsheet gets created and the sheet in question has the value '=Teams.B2' in place but "=Teams.B2" is displayed instead of "Tigers".
I've done the normal checks on the format of the cell and that is not the problem. I can set the cell to any number of types and it continues to display "=Teams.B2" (without the double quotes btw).
If I retype part of the cell contents it de-references properly and displays "Tigers". For example, I can go into the cell, backspace over the 2, re-type 2, then hit enter and I see "Tigers" displayed.
So far I have only tried this with libreoffice calc so this may not be an issue with other spreadsheets.
If anyone else has created excel workbooks in a similar manner with cell references and got it to work I would love to know your secret. Am also open to other comments or suggestions.
Cheers,
