Excel_to_csv.py

Go to the documentation of this file.

import os
from dotenv import load_dotenv
from pandas import read_excel
from MongoConnection import mongo_connection
 
load_dotenv()
 
def upload_info_mongo(event, path, file_id, filename):
    """Upload file information from xls file to new CSV file 
 
    Args:
        event (dict): 
        path (str): s3 path where file is storage
        file_id (str): current/new file_id assigned for mongodb
        filename (str): current file
    Returns: Nothing
    """
    mongo_conn = mongo_connection()
    # Mongo collection to upload snapshots
    collection = mongo_conn.mongo_conn_snapshots()
    new_file = {
    "file_db_id": file_id,
    "status": "processing",
    "client_id": event["client_id"],
    "name": filename,
    "s3_path": path+"/"+filename
    }
    if collection.count_documents({"file_db_id": file_id,"name": filename}) >= 1:
        collection.delete_many({"file_db_id": file_id, "name": filename})
        collection.insert_one(new_file)
    else:
        collection.insert_one(new_file)
 
def pass_excel_to_csv(event, files, xls, s3):
    """Transform xsl file into csv file(s) according sheets number 
 
    Args:
        event (dict): is a dictionary with all client and sales information
        files (dict): is a dictionary with file information
        xls (ExcelFile obj): file loaded as Excel file
        s3 (boto3 client): AWS client connection
    Returns: event (dict)
    """
    bucket = event["bucket"][0]
    path = event["path"][0]
    tmp_name = "/tmp/file.csv"
    file_id = files["file_id"]
    filename = files["file"]
    filename = filename.split(".xls")[0]
    count=1
    for sheet in xls.sheet_names:
        df = read_excel(xls, sheet_name=sheet, engine='openpyxl')
        if len(df) > 0:
            new_filename = filename+"_"+sheet.replace(" ", "_")+".csv"
            print("Creating new csv file: {}".format(new_filename))
            df.to_csv(tmp_name, index=False)
            with open(tmp_name, 'rb') as f:
                object_data = f.read()
                s3.put_object(Body=object_data, Bucket=bucket, Key=path+"/"+new_filename)
            upload_info_mongo(event, path, file_id+"-"+str(count), new_filename)
            event["files"].append({"file_id":file_id+"-"+str(count), "file": new_filename, "root_id":files["file_id"]})
            count += 1
        else:
            print("Sheet '{}' not created cause it is empty".format(sheet))
    
    return event