To Import data from CSV file "Comma Delimited" for example inventory journal transactions use the below code assuming the csv file include data as the following table:
| Item ID |
Site |
Warehouse |
Qty. |
Unit Price |
| 10000001 |
Main |
RM |
15 |
120 |
static void ImportfromCSV(Args _args)
{
Dialog dialog = new Dialog();
DialogField dialogField;
AsciiIo importFile;
str filePath,fileNameOnly;
filetype type;
container record;
str Delimiter = ",";
int totalRecords;
InventDim InventDim;
InventJournalTable journalTable;
InventJournalTrans journalTrans;
InventJournalTableData journalTableData;
InventJournalTransData journalTransData;
dialogField=dialog.addField(extendedTypeStr(FilenameOpen),"Select File","Select file to import");
dialog.caption("File Picker");
dialog.filenameLookupFilter(['csv','*.csv']);
if(!dialog.run())
return;
[filePath, fileNameOnly, type] = fileNameSplit(dialogField.value());
importFile = new AsciiIo(dialogField.value(), 'R');
if((!importFile) || (importFile.status() != IO_Status::Ok))
{
warning("Error in opening import file");
throw(Exception::Error);
}
importFile.inFieldDelimiter(Delimiter);
if((!importFile) || (importFile.status() != IO_Status::Ok))
{
warning("Error in opening log file");
throw(Exception::Error);
}
try
{
ttsbegin;
journalTableData = JournalTableData::newTable(journalTable);
journalTransData = journalTableData.journalStatic().newJournalTransData(journalTrans,journalTableData);
journalTable.clear();
journalTable.JournalId = journalTableData.nextJournalId();
journalTable.JournalType = InventJournalType::Movement;
journalTable.JournalNameId = journalTableData.journalStatic().standardJournalNameId(journalTable.JournalType);
journalTableData.initFromJournalName(journalTableData.journalStatic().findJournalName(journalTable.JournalNameId));
journalTable.insert();
record = importFile.read();
while(importFile.status() == IO_Status::Ok)
{
record = importFile.read();
if(!record)
break;
totalRecords = totalRecords + 1;
journalTrans.clear();
journalTransData.initFromJournalTable();
journalTrans.TransDate = today();
journalTrans.ItemId = conPeek(record,1);
journalTrans.Qty = conPeek(record,4);
journalTrans.CostPrice = conPeek(record,5);
journalTrans.CostAmount = JournalTrans.Qty * JournalTrans.CostPrice;
// Dimension details
inventDim.clear();
inventDim.InventLocationId = conPeek(record,2);
inventDim.InventSiteId = conPeek(record,3);
journalTrans.InventDimId = InventDim::findOrCreate(inventDim).inventDimId;
journalTransData.create();
}
ttscommit;
}
catch(Exception::Error)
{
Throw(Exception::Error);
}
info(strFmt("Total Read Records = %1",totalRecords));
}
