Acumatica and Python Data Analytics: Acumatica xlsx export to pandas dataframe workaround

​

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.

​