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

  • 13 February 2024
  • 2 replies
  • 59 views

Userlevel 1

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.


2 replies

Userlevel 6
Badge +1

Have you considered pulling from Acumatica OData instead?

Userlevel 1

 

 

solved the issue:

 

use pandas.read_csv(target_csv, encoding=’cp1252’) 

 

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved