Read Excel File In Acumatica

Hi All,

Today want to share with you the easiest way to read Excel or CSV file in Acumatica.
Acumatica core (PX.Data.dll) contains 2 classes that may help you with reading some files:

• PX.Data.XLSXReader – can read Excel file row by row,
• PX.Data.CSVReader – can read any CSV file row by row.

In my example today we will read transactions from excel file and import it to some table.

Code snippet:

Just notice here several important things

• Do not forget to close reader by calling Dispose method
• Excel or CSV files may contains first row with headers – you need to skip it manually.
• You need to read data from reader by indexes, if you want to read it by names, create a dictionary with mapping index and name by first row. Reader can do it automatically if you call Reset() method.
• MoveNext() method will move cursor to next row.
• Sometimes you have external data representation in the file and internal in DAC (Like with Accounts – CD in UI and ID in Database). You can use Cache.SetValueExt(…) method to convert external representation to internal.
• Note that all Acumatica fields are nullable, so “” is not equals to null. Make sure that you reading empty columns correctly. You can use String.IsNullOrEmpty(…) method to validate for empty sting.

Have a nice development!