It can be a lengthy topic. Sometimes its easier to show you via Zoom, given the limited time I have to type.
Overview
-
You Clone StatementImportCSV_Example and name it StatementImportCSV_BankOfAmerica_XYZ
1.1 Then you change the files inside as well, and all classes that say StatementImportCSV_Example to StatementImportCSV_BankOfAmerica_XYZ
-
Now in Epicor you create a new Electronic Interface via Electronic Interface Maintenance
-
Now you will be able to assign it in Bank Account Maintenance under the Bank Reconcilation Tab
-
Now when you run Bank Statement Import it will use this .cs
Modifying the Program
Basically when Epicor runs Bank Statement Import it requires the EI to have this stub
/// <summary>
/// This is the MAIN Method that is required and invoked by Epicor
/// </summary>
/// <param name="ImportFile">
/// Epicor passes in the full file path of the file on the Server
/// Example: C:\EpicorData\KineticDevelopment\Users\hkeric\Import\export_20230403 - Copy (2).csv
/// </param>
/// <param name="BankAcctID">
/// Epicor passes in the BankAcctID if you are using the Bank Statement Entry Screen
/// </param>
/// <param name="opStatementHeaderList">
/// We must return this to Epicor
/// </param>
public void Run(string ImportFile, string BankAcctID, out List<StatementHeader> opStatementHeaderList)
{
// Everything here is pretty much up to your discretion
try
{
// Set the Import File Global so we can use it in the validateStatementHeader method
this.fileName = ImportFile;
// Initialize the header rules
initHeaderRules();
HeaderList = new List<StatementHeader>();
opStatementHeaderList = HeaderList;
// The core Method that does the work
this.buildInfo(ImportFile, BankAcctID);
}
catch (BLException)
{
throw;
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
}
All the other methods are up to your discretion. However you should be able to just modify them, and whatever opStatementHeaderList gets assigned to is what Epicor will feed the UI (Client).
The StatementHeader is an Epicor Custom Class that it expects in return, it holds the Header and StatementLines that the Client expects to read, whatever you set in that object in the EI, it will read. The more you specify the better the matching. Once you get auto-complete set you will be able to step into and see the values, otherwise use reflection its in Erp.Internal.Lib.StatementImport
<Reference Include="Erp.Internal.Lib.StatementImport">
<HintPath>..\..\..\..\..\Deployment\Server\Assemblies\Erp.Internal.Lib.StatementImport.dll</HintPath>
<Private>false</Private>
</Reference>
For example our Bank File .csv does not provide opening and closing account balances, so I dont supply them to Epicor, its a flat csv.
// My Example Statement Lines
private bool processStatementLine(StatementHeader statementHeader, string lineRaw)
{
bool result = false;
try
{
// Skip Statement Lines where Amount is Zero
decimal tranAmount = str2Amount(Convert.ToString(decimal.Parse(LineValues(5), System.Globalization.NumberStyles.Currency)));
if (tranAmount == 0)
return true; // Skip Zero Amounts
StatementLine statementLine = statementHeader.AddStatementLine();
statementLine.RawLineData = lineRaw;
statementLine.LineDate = getDate(LineValues(1));
statementLine.LineDescription = LineValues(2);
statementLine.TransactionCode = string.Empty; // This is if the Bank sends a IBAN Transaction Code
statementLine.TranAmount = str2Amount(Convert.ToString(decimal.Parse(LineValues(5), System.Globalization.NumberStyles.Currency)));
statementLine.BankAmount = str2Amount(Convert.ToString(decimal.Parse(LineValues(5), System.Globalization.NumberStyles.Currency)));
statementLine.TransactionCurCode = "USD";//LineValues(7);
statementLine.ExchangeRate = str2Amount("1", ".");
statementLine.Partner = string.Empty; //LineValues(9);
statementLine.PartnerBankAccount = string.Empty;
statementLine.PartnerBankCode = string.Empty;
statementLine.ReferenceNumber = LineValues(4);
statementLine.RemitData = string.Empty; //LineValues(14);
/*The next three items represent ways of getting the Bank Amount:
Update these and the following amount columns line to suit the columns provided in your statement
If provided as separate debit / credit amounts, then use LineValues(0) for the "if" statement and assignment of bank amount
If provided as a single movement amount with sign, similarly use 0 as the column number for the next pair of lines
if (LineValues(3).Trim().Length > 0)
statementLine.BankAmount = str2Amount("");//str2Amount(LineValues(3));
if (LineValues(4).Trim().Length > 0)
statementLine.DebitAmount = str2Amount("");//str2Amount(LineValues(4));
if (LineValues(5).Trim().Length > 0)
statementLine.CreditAmount = str2Amount("");//str2Amount(LineValues(5));
*/
// If the TranAmount is negative then set TransactionCode to "Reverse"
// This is only if Bank Statement is not already reversed and they are not providing a separate reversal column
//statementLine.Reverse(ReverseStamp, LineValues(15));
//statementLine.EndingBalance = str2Amount(LineValues(1));
//statementLine.TotalChargesAmount = str2Amount(LineValues(?));
result = true;
}
catch (Exception ex)
{
addError(ex.Message, CurrentLineNum);
}
return result;
}
Lastly if you get a bit dizzy looking at buildInfo one section is for multi-lines. I would just go through it and comment it all nicely, before you make changes. One portion is for CSV File has Headers, CSV doesnt have headers what not. You can most of the time strip all of that out. I have it a little bit documented.
/// <summary>
/// Main procedure for importing data
/// Executed from Run method
/// </summary>
private List<StatementHeader> buildInfo(string ImportFileName, string bankAcctID)
{
StatementHeader statementHeader = null;
StatementHeader nextStatementHeader = null;
// Epicor Wraps this in a TransactionScope I assume the StatementHeader internally Closes it
// Since trans isnt closed anywhere
using (TransactionScope trans = ErpContext.CreateDefaultTransactionScope())
{
// TextFieldParserWrapper is Epicors Line Reader from some old Visual Basic Library
// It doesnt do much besides read 1 line at a time
// You could easily not use it and replace it with CsvHelper or something else
// But leaving it here because it is what Epicor uses
using (TextFieldParserWrapper parser = new TextFieldParserWrapper(ImportFileName))
{
parser.TextFieldType = FieldType.Delimited;
parser.SetDelimiters(propFieldSeparator);
parser.HasFieldsEnclosedInQuotes = true;
//parser.CommentTokens = New String() {"'"} // Defines comment tokens. A comment token is a string which is placed at the beginning of a line. It indicates that the line is a comment and must be ignored by the parser.
// Not sure why Epicor does another one of these
// But it is what they do
// I assume its a way to Read Certain Lines of special format first
// Then Read the rest of the file
// But in our flat file we dont have any special lines
using (TextFieldParserWrapper parserRaw = new TextFieldParserWrapper(ImportFileName))
{
// Here we set the settings from the Parent Parser
parserRaw.TextFieldType = parser.TextFieldType;
parserRaw.SetDelimiters(parser.Delimiters);
parserRaw.HasFieldsEnclosedInQuotes = parser.HasFieldsEnclosedInQuotes;
bool noHeader = propHeaderLinesCount == 0;
long statementLastLine = 0;
int headerLineNum = 1;
string curBankAccount = string.Empty;
string lineError = string.Empty;
bool breakImport = false;
// Read the file until the end of the file is reached
while (!parser.EndOfData)
{
CurrentLineNum = parser.LineNumber;
fieldValues = parser.ReadFields();
string lineRaw = parserRaw.ReadLine();
// Here you can check if Header is missing something or not and continue to next line
#region MultiLineStatement
headerLineNum = Convert.ToInt32(CurrentLineNum - statementLastLine);
// We dont use this but Epicor has support to read lines without a Header
if (noHeader) //Statement without header
{
if (!HeaderProcessed)
{
statementHeader = new StatementHeader(this.Db);
if (validateStatementHeader(statementHeader, bankAcctID))
{
HeaderList.Add(statementHeader);
HeaderProcessed = true;
}
else
break;
}
processStatementLine(statementHeader, lineRaw);
}
else // Statement with header
{
if (nextStatementHeader == null)
{
nextStatementHeader = new StatementHeader(this.Db);
}
bool parsed = false;
try
{
parsed = headerRules.Parse(headerLineNum, fieldValues, ref nextStatementHeader, out lineError);
}
catch (Exception ex)
{
addError(ex.Message, CurrentLineNum);
statementLastLine = CurrentLineNum;
statementHeader = null;
nextStatementHeader = null;
continue;
}
if (parsed)
{
if (headerLineNum >= propHeaderLinesCount)
{
if (validateStatementHeader(nextStatementHeader, bankAcctID))
HeaderList.Add(nextStatementHeader);
HeaderProcessed = true;
statementHeader = nextStatementHeader;
statementLastLine = CurrentLineNum;
nextStatementHeader = null;
}
}
else
{
statementLastLine = CurrentLineNum;
if (!string.IsNullOrEmpty(lineError) && !HeaderProcessed)
{
addError(lineError, CurrentLineNum);
foreach (StatementHeader header in HeaderList.Where(hdr => hdr.BankAccountNumber.KeyEquals(nextStatementHeader.BankAccountNumber)).ToList())
{
HeaderList.Remove(header);
}
statementHeader = null;
nextStatementHeader = null;
if (string.IsNullOrEmpty(bankAcctID))
continue;
else
breakImport = true;
}
if (statementHeader != null)
{
for (int i = 1; i <= headerLineNum; i++)
{
fieldValues = headerRules.GetHeaderArray(i);
if (!processStatementLine(statementHeader, lineRaw))
{
foreach (StatementHeader header in HeaderList.Where(hdr => hdr.BankAccountNumber.KeyEquals(statementHeader.BankAccountNumber)).ToList())
{
HeaderList.Remove(header);
}
nextStatementHeader = null;
statementHeader = null;
if (!string.IsNullOrEmpty(bankAcctID))
{
breakImport = true;
}
break;
}
}
}
}
}
#endregion MultiLineStatement
if (breakImport)
break;
} //while (!parser.EndOfData)
if (!HeaderProcessed && HeaderList.Count == 0 && CurrentLineNum > 1)
{
throw new BLException(String.Concat(GlobalStrings.TheFileBeingImporIsNotInTheSelecFormat, Environment.NewLine, StatementErrors));
}
parser.Close();
parserRaw.Close();
}
}
this.processErrors();
return HeaderList;
}
}
You create your own Helpers to parse descriptions, if Bank of America isnt giving you a good CSV. Lets say they send over CheckNum-00000004431 you might need to trim CheckNum- and remove leading 0s for Epicor.
public string GetNumbersFromString(string input)
{
string numbersOnly = Regex.Replace(input, @"[^\d]", "");
return numbersOnly;
}
public string TryToGetCheckNumFromString(string input)
{
string numbersOnly = Regex.Replace(input, @"[^\d]", "");
return (!string.IsNullOrEmpty(numbersOnly) ? numbersOnly : string.Empty);
}
/// <summary>
/// Helper to Extract the Reference for various types
/// </summary>
/// <param name="input"></param>
/// <returns>Epicor Limit is x(50) for Reference</returns>
public string GetReferenceFromTransaction(string input, string description)
{
string reference = string.Empty;
if (input.Contains("Check"))
{
reference = this.TryToGetCheckNumFromString(input);
}
else if (description.Contains("WCI-Inv"))
{
reference = string.Empty;
// For now lets not get any InvoiceNum to not confuse with CheckNum
//reference = this.TryToGetInvoiceNumFromString(description);
}
else {
reference = string.Empty;
// For now lets not get any TranNum to not confuse with InvoiceNum or CheckNum
//reference = this.TryToGetTranFromString(description);
}
if (!string.IsNullOrEmpty(reference))
{
reference = reference.Trim();
if (reference.Length > 50)
reference = reference.Substring(0, 50);
return this.RemoveWisconsinConverting(reference);
}
// For now return Empty String since we already have a Description Column
// aka Notes in Epicor.
return string.Empty;
// If we failed to extract meaninguful reference, then use the description
// Just remove our Compant Name to preserve x(50) limit
//return this.RemoveWisconsinConverting(description).Trim();
}
public string RemoveWisconsinConverting(string input)
{
string pattern = @"\b(WIS(CO|CONS|CON|CONV|CONSIN CONVERTING)?|WISC)\b";
string pattern2 = @"\bWIS(CONV(ERTING)?)?\b";
string pattern3 = @"\bWIS(CONV(ERTING)?|CONS(IN CONVERTING|CONVER|CONV|CON|C)?)\b";
string output = Regex.Replace(input, pattern, "", RegexOptions.IgnoreCase);
output = Regex.Replace(output, pattern2, "", RegexOptions.IgnoreCase);
output = Regex.Replace(output, pattern3, "", RegexOptions.IgnoreCase);
return output.Replace("WISCONSIN", "").Replace("WISONSIN C", "");
}
public string TryToGetTranFromString(string input)
{
Match match = Regex.Match(input, @"\d{9}");
if (match.Success)
{
string transactionNumber = match.Value;
return transactionNumber;
}
return string.Empty;
}
public string TryToGetInvoiceNumFromString(string input)
{
string invoiceNumber = "";
// Define the regular expression pattern
//Regex pattern = new Regex(@"WCI-Inv#\s*(\d+)");
Regex pattern = new Regex(@"WCI-Inv#\s*(\d+)\s*(\d*)");
// Check if the input string contains the pattern
Match match = pattern.Match(input);
if (match.Success)
{
// Extract the invoice number from the matched group
invoiceNumber = match.Groups[1].Value;
// Extract any additional digits after the invoice number
string additionalDigits = match.Groups[2].Value;
// Append the additional digits to the invoice number
if (!string.IsNullOrEmpty(additionalDigits))
{
// Lets just overwrite the invoiceNumber since its usually a 1
invoiceNumber = additionalDigits;
}
return "Invoice: " + invoiceNumber;
}
return string.Empty;
}
Epicor has some helpers, you can modify the .csproj and .sln to point to your Servers Assemblies, and then when you run the folder in Visual Studio or VS Code, you will get auto-complete.
Debugging
You can create a LogWrite method to simply use basic C# FileIO and write to some file ex C:\EpicorData\testing.txt
/// <summary>
/// A Custom Method from WCI if you want write some message to a log file
/// Used during Debugging
/// </summary>
/// <param name="fileName"></param>
/// <param name="message"></param>
public void WriteToLogFile(string fileName, string message)
{
using (System.IO.StreamWriter sw = new System.IO.StreamWriter(fileName, true))
{
sw.WriteLine(message);
}
}
Deploying
To deploy you will have to move files from one ENV to another, make sure you add them to App and Print Servers, if you are loadbalanced include all of them.
| Name |
Description |
| Erp\EI\StatementImportCSV_OurBank_WCI |
Electronic Interface Server Side Files |
| DMT_ElectronicInterfaces.xlsx |
DMT Install Template |
Then you can DMT in the rest.
DMT File Details
| EI Name |
EI Program |
EI Type |
| Our Bank Import |
Erp\EI\StatementImportCSV_OurBank_WCI\StatementImportCSV_OurBank_WCI.cs |
Bank Statement |
Deployment Steps
Installation
- Copy the
Erp Folder to your Environments Server Folder (Both AppServer and Print AppServer)
IMPORTANT: Sometimes Epicor runs Processes Async, it is best to keep App and Print Synced
Example: \\kineticdev\c$\inetpub\wwwroot\KineticDev
Or you can Paste the Contents inside Erp\EI to Servers Erp\EI
- Launch DMT and Import
DMT_ElectronicInterfaces.xlsx file with Add / Delete options checked
Configuration
- Launch
Bank Account Maintenance and Navigate to the Bank Reconciliation Tab
- Configure the
Electronic Interface for XYZ
- Validate that it works by importing an example CSV
