convertUpload.py

# Code snippet to convert all .csv files in the current directory to .xlsx files and upload to smartsheet

import os
import glob
import pandas as pd
import numpy as np
import smartsheet # Package smartsheek-python-sdk
import openpyxl

# Smartsheet Token for use with API
TOKEN = "<YOUR TOKEN HERE>"
xSheet = smartsheet.Smartsheet(TOKEN)

extension = 'csv'
result = glob.glob('*.{}'.format(extension))

# Loop every csv file in current dir
for file_ in result:
    file_full = os.path.join(os.getcwd(), file_)
    outname, file_ext = os.path.splitext(file_)
    
    # Reading the csv file into dataframe
    print("Reading: "+file_)
    df_new = pd.read_csv(file_full)

    # Convert dataframe to excel and save xlsx file
    GFG = pd.ExcelWriter(outname+".xlsx", engine='openpyxl')
    df_new.to_excel(GFG, index = False)
    GFG.save()

		# Rename sheet in workbook to original filename
    print("Renaming sheet in file: "+outname+".xlsx")
    ss=openpyxl.load_workbook(outname+".xlsx")
    ss_sheet = ss['Sheet1']
    ss_sheet.title = outname
    ss.save(outname+".xlsx")
 
	  # Get full file path
    file_up = os.path.join(os.getcwd(), outname+".xlsx")
		
		# Upload to Smartsheet
		print("Uploading file to SS")
    imported_sheet = xSheet.Sheets.import_xlsx_sheet(
        file_up, # Workbook
        outname, #sheet_name
        header_row_index=0
    )