Importing Budgets from Excel into Dynamics SL
Posted by Tiffany Bennett, Senior Consultant on August 18, 2016
Entering budgets directly into Dynamics SL can be a timely and tedious task, as you have to enter budgets one account or subaccount at a time. To save time (and your sanity) it’s easiest to enter the budget information into Microsoft Excel, in the proper Dynamics SL format, and then import it.
First, format your Excel file in the format below. The first column should always be “Budget.” The Subaccount should not contain dashes. The Ledger ID is your Budget Ledger ID. The Import Date column should be left blank.
Once ready, save first as an xls or xlsx file. Then remove the header row and save as a csv (comma delimited) file. Don't forget where you saved it.
(Click on the Excel image to enlarge it).
Log into Dynamics SL as a user with rights to the Administration module. Under Administration, go to Control Macro Generator.
Open the screen, and enter the values as shown below, changing the Control File Name Path to one you can access. Remember that path for later:
Now go to Administration, Transaction Import. Fill in all the fields, where the Data File Name is the file path and file name saved in Step 1.
The Control File Name is the path saved in Step 2. Match all other fields as shown below:
Click the Options button and match the fields as shown, changing the Data Written to Log File field to “Only Data in Error.”
Click OK to save the changes:
Now click the “Begin Processing” button to start importing your budget. You will see the budget loading into the Budget Maintenance screen.
Once the import has finished, click the Edit Errors button. Scroll down, to the bottom. It should read “The Number of Errors Detected was 0.” If it reports errors, scroll back up and review them. It is normally an account or subaccount that does not exist, or you forgot to remove the header row. Then open the XLSX file, not the CSV file, to make changes. In Excel, delete the column headings and resave as a CSV file. Then go back to Transaction Import and import again.
If you need assistance or have further questions, just let us know. Email our team of experts at Support@Crestwood.com