Through following code we can import data in Data base and can make log file for details !!!!
static void CsvFileReadAndInsert(Args _args)
{
Dialog dialog = new Dialog();
DialogField dialogField;
AsciiIo importFile,logFile;
str filePath,fileNameOnly;
filetype type;
container record;
CustTable custTable;
CustAccount custAccount;
CustName custName;
CustGroupId custGroupId;
CustCurrencyCode custCurrencyCode;
DirPartyTable dirPartyTable;
int totalRecords, totalInserted,totalFail;
;
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(",");
logFile = new AsciiIo(filePath + "\\Customer Import Log File.txt", 'a+');
if((!importFile) || (importFile.status() != IO_Status::Ok))
{
warning("Error in opening log file");
throw(Exception::Error);
}
logFile.write(strfmt("Log Details"));
logFile.write(strfmt("Import Date: %1 Import Time: %2",date2str(today(),123,2,-1,2,-1,4),time2str(timenow(),1,1)));
try
{
ttsbegin;
record = importFile.read();
while(importFile.status() == IO_Status::Ok)
{
record = importFile.read();
if(!record)
break;
totalRecords = totalRecords + 1;
custAccount = "";
custAccount = conpeek(record,1);
custName=conpeek(record,2);
custCurrencyCode=conpeek(record,2);
if(custAccount == "")
{
logFile.write("Account Num is blank");
totalFail = totalFail + 1;
continue;
}
select custTable
where custTable.AccountNum == custAccount;
if(custTable.RecId)
{
logFile.write((strfmt("Customer %1 already exists.",custAccount)));
custTable.clear();
custTable = null;
totalFail = totalFail + 1;
continue;
}
custTable.AccountNum = custAccount;
custTable.CustGroup=custGroupId;
custCurrencyCode=custCurrencyCode;
custTable.insert();
totalInserted = totalInserted + 1;
}
logFile.write((strfmt("Import Log:")));
logFile.write((strfmt("Total records: %1,added/imported in to the database: %2 and rejected / NOT imported into the database: %3",totalRecords,totalInserted,totalFail)));
ttscommit;
}
catch(Exception::Error)
{
Throw(Exception::Error);
}
}
Happy Coding !!!!
static void CsvFileReadAndInsert(Args _args)
{
Dialog dialog = new Dialog();
DialogField dialogField;
AsciiIo importFile,logFile;
str filePath,fileNameOnly;
filetype type;
container record;
CustTable custTable;
CustAccount custAccount;
CustName custName;
CustGroupId custGroupId;
CustCurrencyCode custCurrencyCode;
DirPartyTable dirPartyTable;
int totalRecords, totalInserted,totalFail;
;
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(",");
logFile = new AsciiIo(filePath + "\\Customer Import Log File.txt", 'a+');
if((!importFile) || (importFile.status() != IO_Status::Ok))
{
warning("Error in opening log file");
throw(Exception::Error);
}
logFile.write(strfmt("Log Details"));
logFile.write(strfmt("Import Date: %1 Import Time: %2",date2str(today(),123,2,-1,2,-1,4),time2str(timenow(),1,1)));
try
{
ttsbegin;
record = importFile.read();
while(importFile.status() == IO_Status::Ok)
{
record = importFile.read();
if(!record)
break;
totalRecords = totalRecords + 1;
custAccount = "";
custAccount = conpeek(record,1);
custName=conpeek(record,2);
custCurrencyCode=conpeek(record,2);
if(custAccount == "")
{
logFile.write("Account Num is blank");
totalFail = totalFail + 1;
continue;
}
select custTable
where custTable.AccountNum == custAccount;
if(custTable.RecId)
{
logFile.write((strfmt("Customer %1 already exists.",custAccount)));
custTable.clear();
custTable = null;
totalFail = totalFail + 1;
continue;
}
custTable.AccountNum = custAccount;
custTable.CustGroup=custGroupId;
custCurrencyCode=custCurrencyCode;
custTable.insert();
totalInserted = totalInserted + 1;
}
logFile.write((strfmt("Import Log:")));
logFile.write((strfmt("Total records: %1,added/imported in to the database: %2 and rejected / NOT imported into the database: %3",totalRecords,totalInserted,totalFail)));
ttscommit;
}
catch(Exception::Error)
{
Throw(Exception::Error);
}
}
Happy Coding !!!!