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.
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!
3 Replies to “Read Excel File In Acumatica”
How to Read data from external csv file and use in test case for Acumatica Automation test SDK.
Hi KPL, compairng to Acumatica, there is no build in excel file integration from test SDK, as far as I know.
But you can use one of these 3 ways.
– Use CSV file instead of excel and then you can read it with simple File IO operations
– Use something like this https://dotnetcoretutorials.com/2019/12/09/reading-excel-files-in-net-core/
– Refer the Acumatica PX.Data from your test project and use the same code as here.
How to Read data from external csv file for Acumatica Automation test SDK.