Hello,

In our budgeting process this year I basically began by copying my data directory to a new "budget" directory and then exported by department, to excel all our revenue and expense codes, including buget and prior year period results.

I then used excel to create my budgets and then imported back into my "budget" adagio data files. While in excel, I did create new line items as required.

To this point everything worked very well. However, when I completed the budget and exported all my revenue and expense codes to a single excel spreadsheet in order to bring into my the new year data directory, I ran into problems.

The data imported without any errors, but the actuals and hisotry seemed to be screwed up. For example, when I clicked on "display" the accounts that had been imported showed a balance of zero. But if I clicked on a given account, there were actual entries in the acount. (Note: in the Gl for the 2003/04 fiscal year there are already transactions posted, but our final budgets have not yet been loaded.) Basically, all I want to do is:

1. Add the new codes created in excel to the existing 2003/04 gl.
2. Add the budget figures for all codes.
3 Not impact on either the prior year or current year actual results.

Seems simple, but I am doing something wrong.

For the final budget load into the new year gl I am:

1. Exporting from my working budget adagio files:

Account code
dept code
description
period 1 budget
period 2 budget
period 3 budget
period 4 budget
period 5 budget
period 6 budget
period 7 budget
period 8 budget
period 9 budget
period 10 budget
period 11 budget
period 12 budget
Account Type
Consolidation Option

Do you see something I am missing to cause this problem?

Thanks,

Ken

Hello Ken,

Your import should just have acct, dept, description and the 12 budget amounts. Tick the option "Overwrite account information" to update existing accounts. Untick the "add accounts" option (since I don't think you're trying to add any new accounts). This should work properly.

Hi Ken,

2 questions:

1) Is the intention to update your existing Ledger with new budgets, or to create a new Ledger database with new accounts and new budgets? (I assume you are updating)

2) Are you intending to import new accounts, or only update existing accounts? (I assume you are adding new accounts, too)

If you are only updating your existing accounts with new budget figures, then the only fields you need in Excel are the Acct, Dept, and the budget fields. You would select the 'Overwrite existing accounts' and not 'Add new records'. In addition, you will want to 'Replace' amounts rather than 'Accumulate'.

If you want to add new accounts, then you will need more fields than what you listed. (Account Status and possibly Current Balance). You would also have to make sure the 'Add new Records' option is selected.

We would need more info about what is wrong. Are you 'displaying' accounts that previously existed prior to importing? That would imply that the budget updating did not proceed as expected. I would suggest looking at the options you chose during the import for how balances are handled.

If this is for new accounts, then you may need to include more fields in the import...

Thanks to both of you for your responses.

I am indeed trying to import budget figures andadd new accounts (and their budget) to the ledger database. During the budgeting process some existing account names have been changed and require updating, and some new accounts have been created in the excel spreadsheet and thus the budget database that must now be added to the ledger database.

I want to add new accounts, update budget information for all accounts (new and old) and update any name changes to existing account. (Nothing like coplicating a problem eh!)

Is there any hope?

Thanks,

Ken

Hello,

I have still not been able to add the budget numbers. Is there any other information you require to help me out?

Thanks,

Ken

Hi Ken,

ZIP up the Ledger and Excel spreadsheet you are trying to import and I'll take a look.

OK?

Hello.

Here in as much detail as possible is everything I am doing to try an import my budget files.

I have two data directories involved. One contains my budget information with some new accounts as well. The other contains my acutal GL data for this year and last. I want to import the new accounts and the budget into the current year gl.

Here are the the structures of my import and export files:

Export of Budget Infomation and New Accounts

File Formate: Excel
File Name: V:\my documents\ken\budget\03_04\band budget.xls

OPTIONS:
Header Included? Yes
Overwrite Existing File? Yes

Separator? None

Field Selections:
Account Code
Department Code
Description
Period 1 - Budget
Period 2 - Budget
Period 3 - Budget
Period 4 - Budget
Period 5 - Budget
Period 6 - Budget
Period 7 - Budget
Period 8 - Budget
Period 9 - Budget
Period 10 - Budget
Period 11 - Budget
Period 12 - Budget
Account Type
Consolidation Option
Account Status

Import of Budget Infomation and New Accounts

File Format: Excel (Version 2.1-5.0)
File Name: V:\my documents\ken\budget\03_04\band budget.xls

OPTIONS
Header Include? Yes
Overwrite Records? Yes
Add New Records? Yes
Validate? Yes
Separator? None
Use 4 Diget Years? No
Use Extended Character set?

Import Field Selection
Account Code
Department Code
Description
Period 1 - Budget
Period 2 - Budget
Period 3 - Budget
Period 4 - Budget
Period 5 - Budget
Period 6 - Budget
Period 7 - Budget
Period 8 - Budget
Period 9 - Budget
Period 10 - Budget
Period 11 - Budget
Period 12 - Budget
Account Type
Consolidation Option
Account Status

Field Defaults:
None Selected

Import Figures with NET CHANGES
REPLACE figures with imported values

I then get an error message that reads:

"The file layout for V:\my documents\ken\budget\03_04\band budget.xls is incorrect. Import is Aborted"

I have also attempted the import using the CSV delimited file type for the export and import processes. I receive different error messages when doing this as follows:

"Corrupt data has been found in the first line of the file. If you have a header line please delete it before importing."

I recieve this message whether or not I export/import with a header option selected.

When I click OK after the above error message I get another error messages that says:

"Invalid data for field called '0'of record Account Type"

"Imports cancelled. No records were imported."

I know this process works as i had no problem importing back into my budget database after adjusting the budgets in excel, But the import as above does not work in either the new gl database or the budget database.

HELP!!!!!

Ken

Hi Ken,

Sorry, we need the data. Imports are too fiddley to try and sort out without the data. Oh yes, if you're well backed up, turn off the Validate choice and try again.

And the import file....

FYI: Account Type must be any of 'B', 'I' or 'R'. The process is finding 0 for that field, meaning you either have an extra field or the import file is laid out incorrectly.

Hi Ken A.,

I'm glad we've got that sorted out. To close this thread, it was the "Validate" option causing the problem (only with P&L accounts). We will fix this in a subsequent release of Ledger.

Thanks for bringing it to our attention