Create Budget from Flat File

GL's Create Budget from Flat File (GLBUCF) utility uploads budgets from a flat file. The file may hold multiple lines of budgets for various accounts. Load one account or 5,000 accounts; however, GLBUCF typically loads one budget per account per file, where all the accounts are either key-object format or FQA. In addition, the file may load an annual, quarterly or monthly budget that is created in an Excel spreadsheet (space delimited or comma separated value file format).

For each valid record in the input file, the utility will find the glba_budact_mstr record for that account in the fiscal year being uploaded (it will create a glba_budact_mstr records for the account if it does not already have a glba_budact_mstr record in that fiscal year), and it will update a glb_budgetXX field (where XX depends on which budget version is being loaded or updated) for each account from the input file.

If the Budget tab on GLUPGN is set to "Enable Logging," the budget version has a checkmark in column to "Log Budg," and the GLBUCF question "Do you wish to log budget changes" says "Yes" (the default), then the budget change will create a glc_budg_dtl record for each account being modified or inserted. Note that if the question "Do you wish to log budget changes" is updated to "No," undoing the change will require a system restore. Otherwise, undo a budget change job with GLUTSUUN.

GLBUCF will load a budget into GLBUUP for defined keys and objects. Use GLUTRIRLKY and GLUTRIRLOB to create new keys or objects from a flat file, as needed.

Flat File Format

The basic flat-file format should reflect setup on GLUPGN's Presentation tab, Input Format field.

Keep in mind that the format of the key and object column could be something else, like FQA that displays the various key parts in a customize format per state standards. An example of the FQA display format could be " P1-P2-P3-O-P4-P5-P6-P7-P8." The actual format will depend on the Presentation tab of your ledger code in GLUPGN. The Input Format defined here must be the format used in the Key-Obj column of the flat file.

Typically, the .prn format loads both K-O and FQA formatted account strings well. An alternative is to format with a .csv file.

Be sure to use the Report Only feature to preview the budget file before committing to change.

Excel Output

Note that the Excel Output option on GLBUUP can help create a template for uploading budgets--both K-O (key-object) format or FQA. Select the desired accounts to update, check the "Account" box in the Output dialog window, and run the report. In Excel, add the desired budget amount to the right of the account. Update the data to TEXT format. Delete any blank lines and headings.

Save as .prn (or .csv in combination with common code GLBU/DELIM). Run in test mode and check for errors. Be sure the account is parsing as expected and amounts are correct – especially decimal placement.

Key-Object

Format the flat-file columns to enter 1 yearly amount for each account:

Or

The basic format of the flat file to enter monthly amounts for each account is (1 amt for each month):

To enter quarterly amounts for each account (1 amount in each quarter), the basic format is:

For quarterly budgets, amt1 will go into periods 1, 2, and 3 (in glm_mo_mstr), amt2 will go into periods 4, 5, and 6, amt3 will go into periods 7, 8, and 9, and amt4 will go into periods 10, 11, and 12. It is advised to use only alphanumeric characters in org. keys and object codes. However, common code GLBU/CFCHAR can be set up to direct which non-alphanumeric characters GLBUCF should accept for keys and objects. Save file as .prn (fixed length).

FQA (Fully Qualified Account) Format

It is possible to use the input format of the account instead of using the "key object" format. To do this, create common code GLBU/DELIM, and put a delimiter in the first character of the short description. GLBUCF uses the delimiter to separate the account and the first budget amount, so the delimiter should not be something that could appear in the account or amount.

The data must be entered in the spreadsheet in text format. Do not put a comma between the key and the object in the input file. Save file as .csv (comma separated value). An example format for the flat file with the GLBU/DELIM common code to enter one yearly amount for each account is: 12-23-58-3244-02; 4,500.50 Where 12-23-58-3244-02 is the account, ";" is the delimiter, and 4,500.50 is the amount. 

COORD Parameter

Optionally, another parameter may be added to the GLBUCF  input file. If "COORD" follows the amount fields, in either a delimited or a non-delimited file, the following field will be used as the Budget Coordinator. A maximum length of 12-characters may be used. 

Not Using a Delimiter
Key Obj Amount Indicator Value ABC123 6399 1234.56 COORD COORNAME

Using a Delimiter (As Specified in GLBU/DELIM Common Code)
Key-Obj,Amount,Indicator,Value ABC123-6399,1234.56,COORD,COORDNAME

Budget Upload via .CSV Common Code

NU Common Codes with a Code Category/Value of GLBU/DELIM assist the uploading of a .csv formatted budget file via GLBUCF. If this common code is in use, it means that the budget upload system is looking for two entries per line that are comma delimited (or two separate columns in an Excel spreadsheet).

The CSV file should be formatted like this in Excel:

Everything needs to be formatted as text, and the file needs to be saved using "Save As" and select CSV as the format. Then use the GLBUCF to upload the CSV document.

If this common code is not in place, then the older three-column upload format is used: