Excel

Dynamic

Import Transactions from CSV

Import from CSV

This option is listed in the Processing menu.

Import from CSV
Import from CSV

 

Typical applications of this function include the following

 

Financial Year End entries are easily generated from Income and Expense reports and can conveniently update all affected accounts through a single import. Use Journal type “BJA”

 

Bad Debt reversals can easily be managed with type “BDX”. Debit the Bad Debt account and credit the relevant client account.

 

Off line users can maintain spreadsheets with information, such as Fees which can then be conveniently imported into the system. For Fees use type “BFA”

 

Recoverable Disbursements may be captured manually or loaded from monitoring software, such as telephone or copier solutions. Use Journal type “BJA” or Petty Cash “BPC”

 

The various transaction types are all ledger based and it is not possible to update a Cash Book using this import.

 

The Processing: Import from CSV menu item allows the direct processing of transactions from a pre-formatted, prepared CSV file.

The file must follow a layout similar to this:
BookType, Matter1, Business2, Description1, Description2, Date, Amount, VAT, Voucher

CSV template in Excel
CSV template in Excel


BookType Users familiar with import of batch transactions in any suite of legal software should be comfortable with this type of file and layout.

Contains a valid transaction type. Only Ledger type transactions are support. Bank transactions such as receipts and cheques cannot be processed here.
Valid options are
BJA – Business Journals
BFA – Business Fees
BFX – Business Fee Reversals
BDX – Business Credit Notes
BPC – Business Petty Cash
BBF – Business Balance Brought Forward
TJA – Trust journals

Matter1
An existing ledger account number. The account to be debited.

Business2
An existing ledger account number. The account to be credited.

Description1
Transaction narrative for the account to be debited.

Description2
Transaction narrative for the account to be credited.

Date
A valid date in yyyy-mm-dd format.

Amount
A valid transaction amount. 0.00 transactions are not processed.

VAT
Contains a valid VAT type.
Valid options are
N*0 – No VAT
I*10.00 – Input VAT at 10%
O*17.5 – Output VAT at 17.5%

Voucher
Optional. If blank will be replaced with the word “Import”

 

Business Balance Brought Forward BBF types are suitable for bringing forward balances from third party accounting records.

 

Dynamic

carl

How to fix Excel CSV file imports

Sometimes, Excel will not recognize a CSV file for what it is.  Instead of breaking the delimited fields up into separate cells, it will consider each line as  a line of text and list the entire line in a single cell.

The problem is typically caused by a mismatch between Windows and Excel Regional Settings.

To fix this permanently, follow these steps:

Windows 8+
Windows Regional Settings
List “,”
Decimal “.”

Excel
File > Options
Advanced
Decimal “.”
Thousand Seperator “,”

Dynamic is preconfigured to use a CSV “,”.  Please see the config menu.

If you are in a rush and just need a once-off quick fix, try this:

Once off

Click the “A” Column header. Click “Data”. Click “Text to Columns”. Select Delimited, select delimiter.

Enjoy your spreadsheet.

-Dynamic

carl

How to convert CSV reports to spreadsheet

Export CSV File

CSV used to be an abbreviation for Comma Separated Value files, a simple text file format used to import and export data between databases.

At the time of writing this, Microsoft had started using the name Character Separated Value files. This system allows both traditional Comma Separated Value and Character Separated Value files.

Example of traditional CSV (comma): "text 1","text 2","text 3" using a comma: , Example of Microsoft CSV (character): "text 1"|"text 2"|"text 3" using a pipe: |

 

The CSV export format used by Dynamic has been tested a wide range of systems and should work by default.

Spreadsheets such as Microsoft Excel and OpenOffice Calc should recognize the data format. It may be necessary to use a Paste Special option, to select the delimiter and separator characters.
Note, sometimes numbers will appear as text and it will not be possible to do calculations. To fix this, follow these steps:

  • Edit > Find & Replace…
  • [More Options]
    tick Regular Expressions
  • Search: .+ (a dot and a plus)
  • Replace: & (the ampersand character)
  • [Replace All]

This is a very clever trick leveraging the power of Regular Expressions built in to spreadsheet software.

 

Dynamic CSV reports export and will automatically open in Microsoft Excel and OpenOffice Calc.  This has been tested on Windows 7, Windows 8 and Windows 10.

-Dynamic