Tuesday, 29 March 2016

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!

1 comment:

sriram said...

The great service in this blog and the nice technology is visible in this blog. I am really very happy for the nice approach is visible in this blog and thank you very much for using the nice technology in this blog
Oracle course in chennai