Hello,
I would like to share my code to get Acumatica exports into python. I am a fan of python because it opens to door to AI analytics, and the opportunity to create robust data pipelines. Am wondering if anyone has ran into the same problem.
I originally got an error when importing the xlsx export from the Acumatica GUI, so I had to use the spire library to convert it into a csv file that could go into pandas.
Here is my code, please review it and let me know what are the best practices. Ideally I would like to run exports through the API, but this is a start.
# Imports ## Data Loading from spire import xls from typing import Union, List, Dict import pandas as pd import numpy as numpy # core lib imports import logging from pathlib import Path import re ## Variables ## # setup # logging level set to INFO logging.basicConfig(format='%(message)s', level=logging.INFO) LOG = logging.getLogger(__name__) # Global Variables ## File paths DATA_DIR = Path('./data/') acumatica_export_source_path = DATA_DIR/'your_export_xlsx_here.xlxs' # a tmp dir as a workaround tmp_csv = DATA_DIR/'tmp'/'tmp_so_booked_details.csv' ## Function ## def acumatica_xlsx_to_python_csv_adapter(source_acumatica_xlsx_path:Path,target_csv_path:Path,text_encoding='UTF-8'): """ A helper function to deal with the formatting errors that occur When Acumatica exports are put directly pandas. This will convert the export without it crashing in excel to do the conversion Parameters: source_xlsx_path: takes a path object to the source directory target_csv_path: returns a path object to the target dir text_encoding: left as utf 8 by default, designed this way to allow for multilingual inputs in future and to track encoding errors output: A file path of the csv file of the acumatica export """ if text_encoding == 'UTF-8': utf_8_encoding = xls.Encoding.get_UTF8 else: LOG.exception(f'---encoding {text_encoding} not supported, please convert to UTF-8') try: LOG.info('---starting to load file----') so_workbook = xls.Workbook() so_workbook.LoadFromFile(str(source_acumatica_xlsx_path)) # needs to change to string for compatibility # Pulling the top worksheet in acumatica sheet = so_workbook.Worksheets[0] sheet.SaveToFile(str(target_csv_path),',', xls.Encoding.get_UTF8()) #LOG.info('--- temp_csv_file_saved_sucessufly ---') df_acumatica_export = pd.read_csv(target_csv_path) # cleaning up temp file target_csv_path.unlink() LOG.info('---file imported ') except Exception as e: LOG.exception('error reading file: ',e) return df_acumatica_export ## Begin Code df_acumatica_export = acumatica_xlsx_to_python_csv_adapter(acumatica_export_source_path,tmp_csv)
This code will create a dataframe in jupyter notebook. Next step is to make it a .py file that can run automatically.