Creating a KFI file to import a GL Journal from Excel does not work if more than one line is used in Excel for a journal transaction.
The GL Journal screen is designed to be one continuous list of fields for KFI purposes. Further, a KFI will cycle through multiple lines automatically rather than requiring the use of F9 as is needed with other entry screens. To make KFI work for the GL Journal entry screen and similar GL screens, special KFI codes need to be used.
The situation is complicated when the file is set up in Excel because Excel will make all the lines the same length. Further, Excel will remove the commas from the end of a line which the KFI needs.
Summary Steps
A summary of the steps to set up a GL Journal KFI using Excel follows:
Initially create the data in Excel with line types in the following order:
Tag line (one per KFI file).
Transaction1.
Header1 (one per transaction).
Allocation1 (at least one debit and one credit line).
Repeat the Transaction setup for each transaction up to Transaction x.
Refer to the first screenshot below for an example.
Save the file in csv format.
Open the file in a text editor and adjust to a proper kfi format (refer to Detailed Steps below).
Save the csv file and convert it to a KFI file.
Detailed Steps
Add as many rows to Excel as you need as follows. In Excel your worksheet should look like the following:
At A1 (the Tag line) in Excel add the following KFI tag code: <IgnoreCR> (Refer to the screenshot below).
The Header row details are added per transaction. If you only want one transaction in this batch, then you will only have one of these rows. The Excel Header columns are (refer screenshot above):
Date.
📌Note: The date year will need to have either 2 or 4 digits according to the Attaché User's date entry setting in File, User Preferences...
Reference.
Details: The KFI needs a comma after the last column in a row, therefore enter a * immediately after the last value on each line.
📌Note: The date year will need to have either 2 or 4 digits according to the Attaché User's date entry setting in File, User Preferences...
A 'filler' that will be removed later.
(Only added for versions 3.x and higher) A 'filler' that will be removed later
e.g.: a 1.x header line: entered in Excel as (columns indicated by commas): 01012017,Reference1,Details1*,##.
e.g.: a 3.x and up header line: entered in Excel as (columns indicated by commas): 01012017,Reference1,Details1*,##,##.
The Allocation rows for each transaction can now be added.
Given the following values:
Account code is 11100.
Amount is $100.
Cost Centre of ADMIN001.
Add this data to Excel in the columns as specified below (refer above screenshot).
GL Code.
Debit, OR
Credit.
Details [add a * to the end of the value in the field if the version is 1.x].
Cost Centre [add a * if version is 3.x and above].
Refer to the above screenshot, that separate debit and credit lines are added. You can have as many debit and credit lines as are needed. The value of debit lines should equal the value of credit lines so that the transaction is balanced
The KFI needs a comma after the last column in a row, therefore enter a * immediately after the last value on each debit and credit line, the last value will vary by version, e.g.:
A 1.x debit line is entered in Excel as (columns indicated by commas): 11100,100,,Alloc Details*.
📌Note: Column E is not used
A 3.x and above debit line with Cost Centres used is entered in Excel as (columns indicated by commas): 11100,100,,Alloc Details,ADMIN01*.
If cost centres are NOT being used in 3.x and above then enter "*" only in column E as the blank field is still required, eg:
11100,100,,Alloc Details,*
Note this is different to 1.x
A 1.x credit line is entered in Excel as: 11100,,100,Alloc Details*
A 3.x and higher credit line is entered in Excel as: 11100,,100,Alloc Details,ADMIN001*
If you wish to create a KFI with multiple transactions, use the following row in Excel to move to a new transaction:
Insert <F9> in the date field of a header line to move to the new transaction
A 3.x header line for a new transaction in the GL Batch is entered in Excel as (columns indicated by commas:<F9>13112017,Reference2,Details2*,##,##
Enter related debit and credit lines for the new header as per Step 4 (as many as are needed, so long as debits equal credits).
Repeat this step for each transaction that needs to be entered for the batch
Save the Excel file and then save it again as a csv file, close Excel.
Open the csv using the right click menu item Open With and then select Notepad or similar (not MS Word). Your csv file should look something like the following, depending on the text editor you are using. The screenshot below is before the next step.
Use Find and Replace in Notepad as follows to amend the csv file:
Find: ,## and Replace with no value / blank / empty replace value (Replace is set up as per above screenshot, results are displayed in the screenshot below)
Find: * and Replace with, (Replace setup as per above screenshot, results in the screenshot below).
Remove all commas that appear after <IgnoreCR> (Final file displayed above)
After the last comma you can add additional KFI tags such as "<F9>" to save the last transaction and "<ESC>" to finish data entry in the General Journal.
Save the file as a csv file.
Copy the txt file and paste it to the same location.
Change the extension of the copied file to be ".kfi" and adjust the name as required.
Optional: Move the kfi file to the Resources\[Company]\KFIData folder to make Step 11 easier.
In Attaché, archive your data if possible using File, Archive Current Company.
Go to General Ledger, Transactions, Transactions, Journals.
At Batch No enter a batch number or accept the default next batch number.
Enter/Adjust a Description for the batch and select Accept F9 to go to the first transaction screen.
Select File, Keystroke File Import to open the KFI screen.
Select the KFI file using the File F2 button. The default location that Attaché will look in for a KFI file is the Resources\[Company]\KFIData folder.
Start the KFI using Start F5 when other settings are set as you like.