Pipeline 3


 

SECTION 4.5.8.1 – SHOP/FUEL BUDGETS ASCII INPUT

 

USAGE: To import a file containing, all or a subset, changes to the Site Budgets for either the shop or fuel budgets. These can also be changed one at a time using MODIFY OPERATING PARAMETERS (see SECTION 4.1.2.5), however, if you need to setup new parameters for all sites, entering them this way is usually more convenient.

 

DISCUSSION: Make sure that you have a good backup of your data and that no-one is using Pipeline during the update. Pipeline may not update the files as you expected and the only completely safe way to correct the mistake is to restore your data from backup.

 

The import file required must be called stnbudmf.csv, although case is not important in Windows. This file must reside in your data directory, eg \PIP3DATA\COMP1. Also, the file must not be in use. This is important because if you have created the file from a spreadsheet and have saved it but not closed it, the file will still be locked by the spreadsheet program and Pipeline will not be able to open it.

 

The input file must be in CSV format. This is a comma-delimited file (without quotes) with one record per line. Exporting from Excel, choosing the CVS format, will produce the correct format automatically. Numeric fields should not have any dollar signs or commas. Do not use commas in any alpha fields. These will be treated as a field break and Pipeline will not import the row correctly. A blank cell will be treated as a valid entry, thus a blank alpha cell will clear the corresponding field in the master-file and a blank numeric cell will reset the corresponding field in the master-file to zero. Thus if you do not wish to change a field, either delete the row.

 

Unlike most imports, this program expects a very strict field layout. Specifically, each line must contain a SITE NUMBER and 12 numbers corresponding to the budgets for each period. The first column represents the budget for January and so on.

 

The very first line must be the header line and must be exactly:

 

SITE_CODE,SHOP_BUDGETS_P1,SHOP_BUDGETS_P2,SHOP_BUDGET_P3,SHOP_BUDGET_P4,SHOP_BUDGET_P5,SHOP_BUDGET_P6,SHOP_BUDGET_P7,SHOP_BUDGET_P8,SHOP_BUDGET_P9,SHOP_BUDGET_P10,SHOP_BUDGET_P11SHOP_BUDGET_P12

 

OR

 

SITE_CODE,FUEL,FUEL_BUDGET_P1,FUEL_BUDGET_P2,FUEL_BUDGET_P3,FUEL_BUDGET_P4,FUEL_BUDGET_P5,FUEL_BUDGET_P6,FUEL_BUDGET_P7,FUEL_BUDGET_P8,FUEL_BUDGET_P9,FUEL_BUDGET_P10,FUEL_BUDGET_P11,FUEL_BUDGET_P12

 

If the first line is not exactly one of the options, as shown above, the program will reject the file. The program will know, from the 1st line, whether you are updating shop or fuels.

 

An example of a complete shop file is as follows:

 

SITE_CODE,BUDGETS_P1,BUDGETS_P2,BUDGETS_P3,BUDGETS_P4,BUDGETS_P5,BUDGETS_P6,BUDGETS_P7,BUDGETS_P8,BUDGETS_P9,BUDGETS_P10,BUDGETS_P11,BUDGETS_P12

8201,64000.00,62000.00,69000.00,61700.00,62000.00,53400.00,55100.00,60100.00,60600.00,62500.00,61000.00,69200.00

8203,70200.00,51700.00,58500.00,58500.00,49100.00,49700.00,52900.00,49700.00,60800.00,56600.00,50700.00,70900.00

8204,34800.00,34800.00,34800.00,34800.00,34800.00,34800.00,34800.00,34800.00,34800.00,34800.00,34800.00,34800.00

8205,82100.00,70300.00,87500.00,81200.00,76500.00,77600.00,77300.00,78600.00,76400.00,81300.00,77000.00,96900.00

8206,42600.00,26700.00,36600.00,34800.00,33300.00,33000.00,38800.00,39800.00,38400.00,40800.00,43200.00,59400.00

8207,312600.00,244000.00,294200.00,274900.00,238100.00,227800.00,240100.00,233000.00,246700.00,255800.00,256400.00,351300.00

8209,34900.00,33800.00,37200.00,38300.00,38400.00,31800.00,35800.00,35800.00,33300.00,36000.00,35100.00,37300.00

8211,69000.00,64600.00,72300.00,60300.00,60200.00,54700.00,59600.00,59700.00,63100.00,64700.00,67600.00,72000.00

8212,60000.00,55000.00,59800.00,53900.00,55500.00,51200.00,52300.00,51600.00,51700.00,54100.00,55900.00,58700.00

 

An example of a complete fuels file is as follows:

 

 

The fields and headers are identical to those exported using SHOP BUDGETS ASCII EXPORT (see SECTION 1.5.3.1). In fact the easiest way to create the initial file is to export it first using this program with the headings option turned on. Note that if you use this method, only export the SITE CODE and BUDGET fields. If you export other data, you will need to delete the superfluous columns before the file can be imported.

 

Notes:

1)                 The order of the columns is not important but they must not appear more than once.

2)                 CODE must appear, as it is used to link the row to the Site Code as it currently exists on the Site master-file.

3)                 Apart from CODE, you must have at least one more column.

4)                 If you try to import the raw file form the ASCII EXPORT program, it will not read in successfully unless you choose to export with comma delimiters between fields and without quotes around fields (numeric and alphanumeric). This can be set in the options part of the export program. Normally this is not the way this function is used, the purpose of this function being to manipulate the file in a spreadsheet program and importing the result.

5)                 Most fields are not verified on import. You must enter valid information. For the field that are validated, invalid data will be ignored.

 

When the program starts, it will firstly check whether the import file exists and is accessible. Having established this, Pipeline will read the first line of the file and check for its correctness. If no obvious errors are found, you will be asked to confirm that you have made a backup and then you will be asked to confirm that you wish to proceed.

 

Pipeline will then read import lines one at a time confirming firstly that the SITE field corresponds to a Site code and then replacing the fields. If an invalid or blank row is encountered, it will be skipped. Pipeline will continue processing until it reaches the end of the import program. Finally the import file will be renamed to stnbudmf.old (to stop you from accidentally rerunning the program with used data) before returning you the MAIN MENU.

Return to Table of Contents  Return to Table of Contents